DAX Calculated Item

[barjoman]

This question has been posted before, but the requirements have been slightly different and the proposed solutions don't work for me.

I have 2 data sets, Financial and Volume.

Simplified version of Financial looks like this:

Type, Category, Value
Actual, Revenue, 120
Budget, Revenue, 110
Target, Revenue, 130
Actual, Expense, 100
Budget, Expense, 90
Target, Expense, 110

Simplified version of Volume looks like this:

Type, Category, Value
Actual, Sales Item 1, 1200
Budget, Sales Item 1, 1100
Target, Sales Item 1, 1300
Actual, Sales Item 2, 1200
Budget, Sales Item 2, 1100
Target, Sales Item 2, 1300

Reality is that I have multiple other Categories and my values are actually split into multiple columns. The fields that I use in the Pivot Table are Measures which calculate the appropriate amounts for Revenue, Expenses, Profit, Profit %, etc.

The two tables are "linked" together through a Lookup Table which simply contains the types. I use the values from this table in my Pivot Table and Slicers to show the data from both tables in the same pivot table.

My Pivot Table currently has fields like this:

My columns display Actual, Budget, Target for each of the Measures, which are on the Rows. What I would like to add are Variances: (Budget - Actual), and (Target - Actual) to the columns. My end result should have Actual, Budget, (Budget - Actual), Target, (Target - Actual) as columns with each Measure calculated accordingly.

The current solution which I have implemented involves converting the Pivot Table to Cube Formulas, inserting columns, and calculating the variances on the Sheet. This is not desirable because it takes Excel a long time to calculate those formulas (there are a lot of them) and this is not acceptable to the business (Even though I understand the cause, it annoys the hell out of me when I have to wait for a sheet to recalculate, good luck explaining it to regular users). Calculations done in PowerPivot are much faster.

So, how can I do something like this in PowerPivot?

 

[Matt Allington]

Just hard code your measures for Actual, Budget and Target, then write the variance measures.
Actual Revenue =calculate([net revenue],lookuptable[type]="actual")
Budget Revenue =calculate([net revenue],lookuptable[type]="budget")
Actual vs budget =[actual revenue] - [budget revenue]

you don't need the first 2 if you don't want - you could write the third measure (fully extended to include the logic of the first 2) but the trouble then is they don't play well together in the pivot. You can manage it in Fields, Items and Sets, but I prefer to write individual measures.