Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have created a Calculation Group using Tabular Editor in Power BI Desktop. The Calcuation Group itself has about 100 metrics with simple calculations such as SUM(metricname). I also have a separte simple Date Table that is available in a slicer on the report page.
The metrics have individual values. Some metrics are measured in percentage, others are measured in million values. My goal is it to create a trend chart visualization that shows me relative trend for a low and high volume selected metric from the calculation group regardless of the actual volumes, ideally (data is daily) it would calculate it with a 7 day rolling average.
The base point for the calcualtion for each of the metrics would be the value of the MIN Date of the selected date range. Means, each value for each day afte the MIN Date would be calculated as "DAY VALUE" / "MIN DATE VALUE" to show the relative trend for the metrics.
Does anybody have an idea how a metric using the calcualtion groups and the date reference table could look like?
Solved! Go to Solution.
I think I have solved this now. The calculation seems to be done in a Calcuation Group rather than in Dax directly.
This is the Dax I created now for normalized trend view of a selectedmeasure.
VAR mindate =CALCULATE (MIN ( 'Dates-Date_and_Periods'[Date] ), ALLSELECTED ( 'Dates-Date_and_Periods'[Date])) VAR firstrecord = CALCULATE (selectedmeasure(),FILTER (ALLSELECTED ( 'Dates-Date_and_Periods' ),'Dates-Date_and_Periods'[Date] = mindate)) Return IF(Max('Dates-Date_and_Periods'[Date]) = mindate,0, IF( ISFILTERED('Dates-Date_and_Periods'[Date]), VAR __LAST_DATE = LASTDATE('Dates-Date_and_Periods'[Date]) RETURN AVERAGEX( DATESBETWEEN( 'Dates-Date_and_Periods'[Date], DATEADD(__LAST_DATE, -7, DAY), __LAST_DATE ), (selectedmeasure()/firstrecord)-1 ) ))
This will allow me now to compare two or more different metrics in one view regardless of the volumes against a baseline (in this case the minimum date of the slicer). Below shows a metric that is in millions versus a metric that is one digit decimals.
I think I have solved this now. The calculation seems to be done in a Calcuation Group rather than in Dax directly.
This is the Dax I created now for normalized trend view of a selectedmeasure.
VAR mindate =CALCULATE (MIN ( 'Dates-Date_and_Periods'[Date] ), ALLSELECTED ( 'Dates-Date_and_Periods'[Date])) VAR firstrecord = CALCULATE (selectedmeasure(),FILTER (ALLSELECTED ( 'Dates-Date_and_Periods' ),'Dates-Date_and_Periods'[Date] = mindate)) Return IF(Max('Dates-Date_and_Periods'[Date]) = mindate,0, IF( ISFILTERED('Dates-Date_and_Periods'[Date]), VAR __LAST_DATE = LASTDATE('Dates-Date_and_Periods'[Date]) RETURN AVERAGEX( DATESBETWEEN( 'Dates-Date_and_Periods'[Date], DATEADD(__LAST_DATE, -7, DAY), __LAST_DATE ), (selectedmeasure()/firstrecord)-1 ) ))
This will allow me now to compare two or more different metrics in one view regardless of the volumes against a baseline (in this case the minimum date of the slicer). Below shows a metric that is in millions versus a metric that is one digit decimals.
@frankGB , I was able to use two calculation group and use them together. For variance you might need measure like
Rolling 7 Var = CALCULATE(selectedmeasure() , DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
- CALCULATE(selectedmeasure() , DATESINPERIOD('Date'[Date ],MAX('Date'[Date ])-7,-7,DAY))
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
Thank you Amit,
This doesn't work for me. I tested a very simple calculation in DAX just saying
measure = selectedmeasure() - selectedmeasure()
This should return a 0 value. But instead it just returns the full selectedmeasure value.
User | Count |
---|---|
90 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |