Calculation Item giving incorrect result when 'variance %' related report measure is used

[PBIDaxter]

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

Apologies if this has been raised previously as I couldnt find a proper solution. 

Highly appreciate all answers.

I have a calculation group called "Calc Group1" with below 3 calc items; (I have changed some namings for this post)

Revenue_1 = CALCULATE (SELECTEDMEASURE (), Category = 1)

Revenue_2=  CALCULATE (SELECTEDMEASURE (), Category = 2)

Revenue_3 = Revenue_1 + Revenue_2

Currently Revenue_3 is written as below;

Revenue_3 = 

CALCULATE(SELECTEDMEASURE(), 'Calc Group1 = "Revenue_1") +

CALCULATE(SELECTEDMEASURE(), 'Calc Group1 = "Revenue_2")

Now, I have 3 report level measures defined as below;

Actual Amount = CALCULATE(SUM(Transactions_Actual[Value]))

Budget Amount = CALCULATE(SUM(Transactions_Budget[Value]))

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 Percentage" when I create below matrix in Power BI,

Incorrect Budget variance % is adding the %s of Revenue_1 (50%) and Revenue_2 (14.29%) rather than considering the actual/budget values of "Net Revenue" metric itself (1050/2350 = 44.68%).

Based on research looks like this is ocurring due to the sideways recursion of calculation groups - but I am unclear.

Can I know why this is happening and what's the best way to fix it keeping the structure of the calc group and Report measures intact?

Thanks!

[v-nuoc-msft]

Hi @PBIDaxter 

Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided.

Regards,

Nono Chen

[PBIDaxter]

Hi,

Apologies for the delay.

I can't seem to attach any files to this post. But below is the sample data for the table;

Actual	Budget	Category
1000	1000	1
500	250	1
1500	750	1
50	50	2
150	75	2
200	225	2

You can create the calculation items via below;

Revenue_1 = CALCULATE(SELECTEDMEASURE(),FactRevenue[Category] = 1)

Revenue_2 = CALCULATE(SELECTEDMEASURE(),FactRevenue[Category] = 2)

Revenue_3 =

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

+

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

and measures as below;

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

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

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

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

Issue is that I get an incorrect value for 'Budget Variance Percentage'

Based on the values, correct answer is 1050/2350 = 44.6%