CODEWITHSUNDEEP
excelpowerbidax
Sth3
Sth3

Reputation: 69

Calculating Variance and Variance% in Dax

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

Revenue = Sum(Amt)

More measures that calculate 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.

Upvotes: 3

Views: 2493

Answers (1)

mendosi
mendosi

Reputation: 2051

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.

Upvotes: 1

Related Questions