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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors