Calculated Column based on measure

[rgadbois]

Hi,

I recently created a measure that creates a baseline for athletes' Day 1 testing results for a specific metric. We repeat this test on a daily basis, and I am attempting to see each day's % deviation from the baseline value. ie, ((Today - Baseline) / Baseline) expressed as a percentage. The measure I created works, and the Dax is below:

_RSI_BASELINE =

AVERAGEX(SUMMARIZE(FILTER(CMJ,[Date]=DATE(2022,9,21)),CMJ[Name],"RSI-modified [m/s]",AVERAGE('CMJ'[RSI-modified [m/s]]])),[RSI-modified [m/s]]])

I am struggling to determine how to create this DAX formula for this calculated column, as each column has numerous individuals in it, which makes filtering more difficult. Any assistance would be greaty appreciated!

This is within the same Table that this data will populate on a daily basis. The table in question resembles below:

NAME	DATE	RSI	JUMP HEIGHT
Athlete 1	09/21/2022	0.5	30
Athlete 2	09/21/2022	0.68	39.3
Athlete 1	09/22/2022	0.85	32
Athlete 2	09/22/2022	0.7	32.4
Athlete 1	09/23/2022	0.48	29
Athlete 2	09/23/2022	0.89	41

I am struggling to determine how to create this DAX formula for this calculated column, as each column has numerous individuals in it, which makes filtering more difficult. Rather than create another measure for this on a daily basis, I need a Calculated Column so I can graph individuals' changes on a daily basis over time. Any assistance would be greaty appreciated!

[v-yadongf-msft · Accepted solution]

Hi @rgadbois ,

Please try following DAX:

RSI_BASELINE = CALCULATE(AVERAGE('CMJ'[RSI]),FILTER('CMJ','CMJ'[Date] = EARLIER('CMJ'[Date])))

Percentage = DIVIDE([RSI] - [RSI_BASELINE], [RSI_BASELINE])

The result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@rgadbois So like this?

_RSI_BASELINE =
  VAR __Athlete = [NAME]
RETURN
  MAXX(FILTER(CMJ, [NAME] = __Athlete && [Date]=DATE(2022,9,21)),[RSI])

[rgadbois]

That does not work as there are numerous names within the [Name] column. There are 72 total athletes, each with one row added to the table on a daily basis.