Calculating Variance and Variance% in Dax

Question

In my PBIX File, I have measures that calculate Revenue, COGS, Gross Margin, etc.

Revenue = Sum(Amt)

More measures that calculate the value for Last year Revenue_LY, COGS_LY, and GM_LY.

Revenue_LY = CALCULATE (

  [Revenue],

  FILTER (

       ALL ( 'Date' ),

            'Date'[FinYear]= MAX ( 'Date'[FinYear] ) - 1 && 'Date'[FinPeriod] = max('Date'[FinPeriod])

    )

 )

Now I need variance and variance% measures for each which compare data against last year and budget. The amount of measures is just getting too many. 

 Revenue_CY-LY = CALCULATE([Revenue],KEEPFILTERS(Versions[VersionCode] = "Act")) - CALCULATE([Revenue_LY],KEEPFILTERS(Versions[VersionCode] = "Act"))

Revenue_CY-LY% = IF([Revenue_CY-LY] < 0, -1, 1) *

                IF(

                    ABS(DIVIDE([Revenue_CY-LY],[Revenue])) > 99.9, 

                        "n/a",

                    ABS(DIVIDE([Revenue_CY-LY],[Revenue])*100)

            )

Is there a way to summarize the measures used. I don't want to create individual measures of each variance. 

Answer 1

• Yes, You can create a dynamic measure.

• First create Revenue, COGS, Gross Margin, etc. measures

Revenue = SUM([Amt])

COGS = SUM([Cost])

Gross Margin = [Revenue] - [COGS]

...

• Then you create a table with one row for each of your measures:

My Measures = DATATABLE("My Measure", STRING, {{"Revenue"}, {"COGS"}, {"Gross Margin"}})

• The names don't need to align with your actual measures, but they will be displayed so make them presentable.

• Then you create a measure on that table which will dynamically be the same as the selected row in the table:

Selected Measure = SWITCH(SELECTEDVALUE('My Measures'[My Measure], BLANK()), "Revenue", [Revenue], "COGS", [COGS], "Gross Margin", [Gross Margin], BLANK())

• Next you go and create all the complicated time-intelligence measures using the [Selected Measure] as the base:

Dynamic_LY = CALCULATE (

  [Selected Measure],

  FILTER (

      ALL ( 'Date' ),

            'Date'[FinYear]= MAX ( 'Date'[FinYear] ) - 1 && 'Date'[FinPeriod] = max('Date'[FinPeriod])

    )

 )

• And then you can do [Dynamic_CY-LY] and [Dynamic_CY-LY %] in a similar manner to the ones in your question, replacing references to the [Revenue] measure with references to the dynamic measures.

• Now you can either use a slicer on the 'My Measures'[My Measure] column to dynamically change every instance of [Dynamic_CY-LY] and the other dynamic measures, or you can add a filter on each visualisation to filter 'My Measures'[My Measure].

• It might be that you'd also like to have a default value for [Selected Measure] instead of defaulting to  BLANK(); just put that in last position in the SWITCH() function.