Calculation Group and Complex Measures

[WZorn]

Let me see if I can explain what I'm tryin to do.  I hope someone has a solution for where the calculations go wrong.

I'm creating an income statement where the majority of rows are a roll up of multiple accounts.  Some rows, on the other hand, are summarization rows.  The columns are measures and I'm using a calculation group to apply additional row level filtering on a per row basis.

Rows:

Columns:

Measure examples:

MTD Actual = SUM('Table'[MTD_Actual])

MTD Budget = SUM('Table'[MTD_Budget])

Var % = ([MTD Actual] - [MTD Budget])/[MTD Budget]

Calculation group, Calculation Item Example:

Line Item = 
	SWITCH(
		SELECTEDVALUE('Rollup_Table'[Row_Number]),
		// This is the "Gross Patient Revenue" row 
		// It filters to include records for BOTH Inpatient and Outpatient Revenue 
		3, CALCULATE(SELECTEDMEASURE(), FILTER(ALL('Rollup_Table'), 'Rollup_Table'[Row_Number] IN {1, 2})),	
		
		// This is the "Net Patient Revenue" row
		// It has to calculate [Gross Patient Revenue] - ([Revenue Deductions] + [Provision for Bad Debt - Patient])
		6, CALCULATE(SELECTEDMEASURE(), FILTER(ALL('Rollup_Table'), 'Rollup_Table'[Row_Number] IN {1, 2})) -
			CALCULATE(SELECTEDMEASURE(), FILTER(ALL('Rollup_Table'), 'Rollup_Table'[Row_Number] IN {4, 5})),	
			
		... more code here ...

		// Uses rows of table to filter to each row item
		SELECTEDMEASURE()     
	)

For simple measures like MTD Actual and MTD Budget everything WORKS.

The issue I have is with measures like Var % that have a more complex calculation.

Rows that default to SELECTEDMEASURE() or use the CALCULATE function like row 3 above still WORK great.

Rows where additional calculation has to be preformed like row 6 DO NOT WORK.

I'm pretty sure this is the reason  why:

LET:

A = CALCULATE(SELECTEDMEASURE(), FILTER(ALL('Rollup_Table'), 'Rollup_Table'[Row_Number] IN {1, 2})) 

B = CALCULATE(SELECTEDMEASURE(), FILTER(ALL('Rollup_Table'), 'Rollup_Table'[Row_Number] IN {4, 5}))

I think what gets calculated for row 6 is this:

Using A(([MTD Actual] - [MTD Budget])/[MTD Budget]) - Using B(([MTD Actual] - [MTD Budget])/[MTD Budget])

When what I want is this:

((A[MTD Actual]-B[MTD Actual]) - (A[MTD Budget]-B[MTD Budget]))/(A[MTD Budget]-B[MTD Budget])

I hope that makes sense. 

[v-saisrao-msft]

Hi @WZorn,

Have you been able to look over the solution shared by the @johnt75 @Hoangechip910? If the problem is still occurring, please send the sample pbix file so we can help resolve it.

Thank you.

[Hoangechip910]

hi, try below for 6, and data of B should be in negative

6, CALCULATE(SELECTEDMEASURE(), FILTER(ALL('Rollup_Table'), 'Rollup_Table'[Row_Number] IN {1, 2,4,5})

[johnt75 · Accepted solution]

You want to change the way the measures are calculated, so you need to define the logic in the calculation item. You can detect which measure is currently active using SELECTEDMEASURENAME and then define a separate calculation for each one if you need to.

[WZorn]

This is exactly what I had to do. 

I was hoping for a "magic" fix without having to add additional logic in the calculation item.  Ah well. 

Thank you!

[v-saisrao-msft]

Hi @WZorn ,

Please provide sample pbix file that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? 

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI