cancel
Showing results for
Did you mean:
Frequent Visitor

## Create Baseline and Percentage Deviation from Baseline

Hi,

I have a large report with all Sports Science data for athletes. One table I am using has all relevant data for a particular piece of technology, in which the columns are sorted Date, Athlete, (Metric 1), (Metric 2).....(Metric 20).

I want to create a baseline for 4 specific metrics from a specific date (Sept. 1, 2022) and each day that is populated thereafter to be calculated as a % deviation from baseline. My data goes back to september 2021, but I need to use this year's September date as the starting point for this season. The data typically looks as follows:

Athlete 1 | 09/01/2022 | 0.6

Athlete 2 | 09/01/2022 | 0.37

Athlete 3 | 09/01/2022 | 0.68

Athlete 4 | 09/01/2022 | 0.43

Athlete 1 | 09/02/2022 | 0.79

Athlete 2 | 09/02/2022 | 0.56

Athlete 3 | 09/02/2022 | 0.6

Athlete 4 | 09/02/2022 | 0.6

etc. etc.

I have tried using various DAX formulae to create a measure for this, but cannot figure anything out. Any assistance would be greatly appreciated!

Note: I cannot upload the Excel file for .pbix for this as the data I am using is sensitive.

5 REPLIES 5
Super User

Hi,

Based on the table that you hae shared, please show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

``````Baseline Measure = AVERAGEX(SUMMARIZE(FILTER(ALL('Table'),[Date]=DATE(2022,9,1)),[Athlete],"__Metric",SUM('Table'[Metric])),[__Metric])

Percent Diff Measure =
VAR __Current = AVERAGEX(SUMMARIZE('Table',[Athlete],"__Metric",SUM('Table'[Metric])),[__Metric])
RETURN
DIVIDE(__Current - [Baseline Measure],[Baseline Measure],0)
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

I got this to work! I tested it out with a card with a page filter based on each individual player, yet the baseline is set for everyone, and not individually. Is there a way to fix that?

Frequent Visitor

Thank your for your help! I seem to be close, but I keep getting the following error:

my table name is "CMJ" and the metric is "RSI-modified [m/s]"

Super User

@rgadbois Guessing you need one extra ] on your SUM('CMJ'[RSI - modified[m/s]]]])),...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...