'How should I define a DAX measure to normalize dataset values by category in a time series visual?
I'm trying to obtain a plot of values by categories, normalized by the category value at the earliest date not sooner than a selected date from a time slicer.
I tried to the following DAX measure:
Normalized =
var mintime = CALCULATE(min(Data[Date]), ALLSELECTED(Data))
var initial = SUMX(FILTER(ALLSELECTED(Data), Data[Date]=mintime), Data[Value])
return DIVIDE(SUM(Data[Value]), initial)
But this fails to correctly compute the initial values per category, and correspondingly the lines do not start at 1 on the graph.
For example, using a sample dataset Data defined as:
Date Category Value
2022-01-01 A 100
2022-01-01 B 95
2022-01-02 A 60
2022-01-02 B 115
2022-01-02 C 95
2022-01-03 A 36
2022-01-03 B 34
2022-01-03 C 54
2022-01-04 A 22
2022-01-04 B 10
2022-01-04 C 27
I get the following incorrect plot:
How should I modify my measure to get the correct result?
For reference, I know I could separate the data in computed tables for each category:
TableA = FILTER(Data, Data[Category] == "A")
TableB = FILTER(Data, Data[Category] == "B")
TableC = FILTER(Data, Data[Category] == "C")
Then define separate DAX measures for each category:
A =
var mintime = CALCULATE(min(TableA[Date]), ALLSELECTED(TableA))
var initial = SUMX(FILTER(ALLSELECTED(TableA), TableA[Date]=mintime), TableA[Value])
return DIVIDE(SUM(TableA[Value]), initial)
B = ...
Along with relationships to keep all the TableA[Date], TableB[Date], ... to refer to a common time base.
Stacking each of those measures into a line plot does produce the expected result:
However this quickly become fairly impractical as the number of categories increases (and my production dataset does have much more categories). So I'm looking for an alternative approach involving a single measure, contextualized by the plot legend.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|


