Can anyone offer help with a variance calculation between row s of a filtered matrix

[tbobolz]

Hi, thanks for looking. I have read through a few other post, yet I am still puzzled. Since my matrix is filtered to two dimensions, it is a little to complex for my skill level. 

Here is the smaple pbix link:

Sample pbix 

Ultimately, I'd like to look like this smaple:

Sample final 

Thanks again for looking.

Terry

[shawne · Accepted solution]

Solution 

Create Measures for each variation of Dollars like this: 

Actual =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Actual")

-------------------------------------------------------------------------------------------------- 

Budget =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Budgeted")

 -------------------------------------------------------------------------------------------------- 

Variance =

VAR Actual =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Actual"

)

VAR Budge =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Budgeted"

)

VAR Result = Actual - Budge

RETURN

Result

 -------------------------------------------------------------------------------------------------- 

Variance % = DIVIDE([Variance],[Budget])

-------------------------------------------------------------------------------------------------- 

Then remove time class from the Rows and add all measures into values.  Remove the "Dollars" field from values.  Go to the formatting of the visual, go to values section and set "Show Values On Rows".  Should Work as requested. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[tbobolz]

Thank you as well Ashish for taking the time to help me. As I mentioned to Shawne, this makes perfect sense now that i see it.

Terry

[Ashish_Mathur]

You are welcome.

[amitchandak]

@tbobolz , You need have all you four-measure Actual, Budget, Variance and Variance % and Show them in matrix with option - "Show on Row"

[shawne · Accepted solution]

Solution 

Create Measures for each variation of Dollars like this: 

Actual =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Actual")

-------------------------------------------------------------------------------------------------- 

Budget =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Budgeted")

 -------------------------------------------------------------------------------------------------- 

Variance =

VAR Actual =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Actual"

)

VAR Budge =

CALCULATE (

SUM ( 'Oncology DB Budget Gross Revenue - Extract'[Dollars] ),

'Oncology DB Budget Gross Revenue - Extract'[Time Class - TimeClass] = "Budgeted"

)

VAR Result = Actual - Budge

RETURN

Result

 -------------------------------------------------------------------------------------------------- 

Variance % = DIVIDE([Variance],[Budget])

-------------------------------------------------------------------------------------------------- 

Then remove time class from the Rows and add all measures into values.  Remove the "Dollars" field from values.  Go to the formatting of the visual, go to values section and set "Show Values On Rows".  Should Work as requested. 

[tbobolz]

Thank you Shawne,

This looked so painful obvious once i seen it. 🙄 I apprecaite you taking the time to assist!

[shawne]

No problem at all!  Glad to help.  In this table structure it is easier to calculate than when Budget and Actuals are in seperate tables.  But if you ever encounter that, here is a great reference.  Again, way more tricky when delaing with multiple finance tables, but this article is very helpful if you run into it as you get deeper and deeper into DAX.     https://www.daxpatterns.com/budget/