Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rgadbois
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
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@rgadbois Maybe:

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)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

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

rgadbois_0-1664665908283.png

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.