Forum Discussion

rgadbois's avatar
rgadbois
Frequent Visitor
3 years ago
Solved

Calculated Column based on measure

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:
NAMEDATERSIJUMP HEIGHT

Athlete 1

09/21/2022

0.530
Athlete 209/21/20220.6839.3
Athlete 109/22/20220.8532
Athlete 209/22/20220.732.4
Athlete 109/23/20220.4829
Athlete 209/23/20220.8941

 

 

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!

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    rgadbois So like this?

    _RSI_BASELINE =
  VAR __Athlete = [NAME]
RETURN
  MAXX(FILTER(CMJ, [NAME] = __Athlete && [Date]=DATE(2022,9,21)),[RSI])
    • rgadbois's avatar
      rgadbois
      Frequent Visitor
      3 years ago

      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.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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.