Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Hi,
Based on the table that you hae shared, please show the expected result.
@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)
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:
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]]]])),...
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
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!