Forum Discussion

frankGB's avatar
frankGB
Frequent Visitor
3 years ago
Solved

Variance % against a baseline value for calculation groups

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...
Need Help
  • frankGB's avatar
    frankGB
    3 years ago

    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.  

     

     

     

amitchandak's avatar
amitchandak
Super User
3 years ago

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

frankGB's avatar
frankGB
Frequent Visitor
3 years ago

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.