Calculation Item giving incorrect results when 'variance' and 'variance %' measures are used

[PBIDaxter]

Hi, I have a query regarding a derived calculation item and results given when different measures are added.

Apologies if this has been raised previously as I couldn't find a proper solution. 

Highly appreciate all answers.

I have a calculation group called "Calc Group1" with below 3 calc items; (This is a simplified example similar to the data I have)

Revenue = CALCULATE(SELECTEDMEASURE(),Fact_Sales[Category] = 1)

Units = CALCULATE(SELECTEDMEASURE(),Fact_Sales[Category] = 2)

Revenue Per Unit =  Revenue/Units

Currently "Revenue Per Unit" is written as below;

DIVIDE(

    CALCULATE(SELECTEDMEASURE(),'Calc Group1'[Calculation group column] = "Revenue")

    ,

    CALCULATE(SELECTEDMEASURE(),'Calc Group1'[Calculation group column] = "Units")

    )

Now, I have 4 measures defined as below;

Actual Amount = CALCULATE(SUM(Fact_Sales[Actual]))

Budget Amount = CALCULATE(SUM(Fact_Sales[Budget]))

Budget Variance = [Actual Amount] - [Budget Amount]

Budget Variance Percentage = DIVIDE([Budget Variance],[Budget Amount])

My issue is that I get an incorrect value for "Budget Variance" and "Budget Variance Percentage" when I create below matrix in Power BI (Please refer to "Revenue Per Unit - Incorrect". I have also shown what the correct calc should be as "Revenue Per Unit - Correct")

Please find the link to the pbix - https://drive.google.com/file/d/1Rtw6UgAUFa3YcdUVtElqkERMsNfmrkvf/view?usp=sharing

I am not entirely sure why this is occurring, but it works when I update the calculation of "Revenue Per Unit" as follows - it's a lengthy code where I separately consider each measure such as 'Budget Variance, and 'Budget Variance Percentage' and do each calculation separately within the calculation item,

*****************************************

Revenue Per Unit - Correct =

var _Revenue = CALCULATE(SELECTEDMEASURE(),'Calc Group1'[Calculation group column] = "Revenue")

var _Units = CALCULATE(SELECTEDMEASURE(),'Calc Group1'[Calculation group column] = "Units")

//Calculations for Actual

var _Actual_Revenue = CALCULATE([Actual Amount],'Calc Group1'[Calculation group column] = "Revenue")

var _Actual_Units = CALCULATE([Actual Amount],'Calc Group1'[Calculation group column] = "Units")

var _Actual_RevPerUnit = DIVIDE(_Actual_Revenue,_Actual_Units)

//Calculations for Budget

var _Budget_Revenue = CALCULATE([Budget Amount],'Calc Group1'[Calculation group column] = "Revenue")

var _Budget_Units = CALCULATE([Budget Amount],'Calc Group1'[Calculation group column] = "Units")

var _Budget_RevPerUnit = DIVIDE(_Budget_Revenue ,_Budget_Units)

var _Variance = IF(OR(ISBLANK(_Actual_RevPerUnit),ISBLANK(_Budget_RevPerUnit))

                  ,BLANK()

                  ,_Actual_RevPerUnit - _Budget_RevPerUnit)

var _variancePercentage = IF(ISBLANK(_Variance), BLANK(), DIVIDE(_Variance,_Budget_RevPerUnit))

Return

Switch(True(),

       //Checking for each report measure

       ISSELECTEDMEASURE([Budget Variance]),_Variance

      ,ISSELECTEDMEASURE([Budget Variance Percentage]),_variancePercentage

      ,DIVIDE(_Revenue,_Units)

      )

***************************************

Although it seems to be working, this means that each time a new measure is added I have to test if these type of calculation items give correct values.

Also, my actual report contains multiple calculation groups which contain calculation items that refer to other calculation group items similar to above item. There is also a calculation group that handles time intelligence calculations such as 'Current Month", "Previous Month", "Fiscal YTD", Previous Fiscal YTD" etc.).

There are also other measures such as "Prior Month Actual Amount", "Prior Month Actual Amount Variance", "Prior Month Actual Amount Variance %" etc, that can be used along with above measures and the code should be extended to factor these measures.

This data model is created mainly for self service analytics, thus the measure creation will be controlled and kept to a minimum.

QUESTION:
1.Is there a better and an efficient way that the above issue can be fixed than the one I have used?
2. What is the reason for this behaviour in Calculation Groups?

Really appreciate your support! Thanks!

[OwenAuger]

Hi @PBIDaxter 

(My suggested PBIX attached)

1. I would suggest that you switch the functions of measures and calculation group.

• Create measure for Revenue, Units and Revenue per Unit
• Create calculation items for Actual, Budget, Budget Variance and Budget Variance Percentage

To make this easier to work with, I would also suggest restructuring Fact_Sales by "unpivoting" Budget/Actual columns into a Version column:

This means that any given calculation can be defined once, and you need only filter by Budget/Actual to apply it to either Budget/Actual (which is handled by calculation group below).

You would then write these measures (Tabular Editor DAX script shown):

-------------------
-- Measure: [Value]
-------------------
MEASURE _Measures[Value] = SUM ( Fact_Sales[Value] )
    , FormatString = "#,0"

---------------------
-- Measure: [Revenue]
---------------------
MEASURE _Measures[Revenue] = 
    CALCULATE (
        [Value],
        Fact_Sales[Category] = 1
    )
    , FormatString = "#,0"

-------------------
-- Measure: [Units]
-------------------
MEASURE _Measures[Units] = 
    CALCULATE (
        [Value],
        Fact_Sales[Category] = 2
    )
    , FormatString = "#,0"

------------------------------
-- Measure: [Revenue Per Unit]
------------------------------
MEASURE _Measures[Revenue Per Unit] = 
    DIVIDE (
        [Revenue],
        [Units]
    )
    , FormatString = "#,0.0"

Then your calculation group would look like this:

-----------------------------------
-- Calculation Group: 'Calc Group1'
-----------------------------------
CALCULATIONGROUP 'Calc Group1'[Calculation group column]    , Precedence = 10

    CALCULATIONITEM "Actual" = 
        CALCULATE (
            SELECTEDMEASURE (),
            Fact_Sales[Version] = "Actual"
        )
        , Ordinal = 5

    CALCULATIONITEM "Budget" = 
        CALCULATE (
            SELECTEDMEASURE (),
            Fact_Sales[Version] = "Budget"
        )
        , Ordinal = 6

    CALCULATIONITEM "Budget Variance" = 
        VAR ActualValue =
            CALCULATE (
                SELECTEDMEASURE ( ),
                Fact_Sales[Version] = "Actual"
            )
        VAR BudgetValue =
            CALCULATE (
                SELECTEDMEASURE ( ),
                Fact_Sales[Version] = "Budget"
            )
        VAR Result = ActualValue - BudgetValue
        RETURN
            Result
        , Ordinal = 6

    CALCULATIONITEM "Budget Variance Percentage" = 
        VAR ActualValue =
            CALCULATE (
                SELECTEDMEASURE ( ),
                Fact_Sales[Version] = "Actual"
            )
        VAR BudgetValue =
            CALCULATE (
                SELECTEDMEASURE ( ),
                Fact_Sales[Version] = "Budget"
            )
        VAR VarianceValue = ActualValue - BudgetValue
        VAR Result =
            DIVIDE (
                VarianceValue,
                BudgetValue
                
            )
        RETURN
            Result
        , FormatString = "0.0%"
        , Ordinal = 6

 

2. Explanation for original behaviour

Order of operations:

The Revenue Per Unit variance was being calculated as:

(Actual Revenue - Budget Revenue) / (Actual Units - Budget Units)

whereas you really want:

(Actual Revenue/Actual Units) - (Budget Revenue/Budget Units)

Notes:

• You could still map measure to calculation items in a separate calculation group if you wanted, but I think it complicates things a bit.
• You could also pivot the Category column into "Revenue" and "Units" columns for a more traditional fact table structure, and redefine measures accordingly. This may help with performance as well.

Well that's my 2 cents. Would something like this be workable for you?

Regards

[PBIDaxter]

Hi,

Thanks for your response.

Unfortunately, I can't swap the calculation items into measures - there are around 6 calculation groups currently in the report accounting for around 60 calculation items. There is also a calculation group which is meant for time intelligence (such as Current Month/Previous Month, Fiscal YTD, Previous Fiscal YTD etc.) which means I will have to create different variations of the above 60 calc items if I create them as measures. Also, given the data model is meant for self service analytics, it puts too much strain on the end users to have that many measures and utilize those, unlike in the current setup where each calculation group corresponds to metrics related to a specific business unit and it's easier to use them in tables and matrix visuals.

Is there any method to rectify the above issue without swapping calculation items and measures?

Thanks!

[OwenAuger · Accepted solution]

Hi again,

I must have missed your reply sorry!

I wouldn't suggest making any changes to the other calculation groups.

The only change would be:

• Create measures for Revenue, Units, Revenue per Unit (this relies on unpivoting Budget/Actual to a Version column)
• Create a calculation group for Actual, Budget, Budget Variance, Budget Variance Percentage

The other calculation groups should work fine with the above changes. Is this workable do you think?

[PBIDaxter]

Hi,

Apologies for the delay! Thanks for your solution. I tried it out and using the measures in a separate calculation group and setting a higher precendence did the trick. I have to do more testing as my model is a bit more complex than what I had provided in the sample dataset. But it works for now, which is great!