'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:

Plot of incorrectly normalized data: series A, B & C are divided by the sum of values for all categories, yielding plots which do not start at 1.0

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:

Plot of correctly normalized data: series A, B & C start at 1.0 on Jan/01, Jan/01 and Jan/02 respectively when slicer starts on Jan/01; all series also start at 1.0 on Jan/02 when slicer starts on Jan/02

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