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.  

     

     

     

frankGB's avatar
frankGB
Frequent Visitor
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.