cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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)

@ 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!:
Mastering Power BI 2nd Edition

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]]]])),...


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors