DAX question on calculating difference between two column values

[cr731]

I have a table showing a summary of values with a column for date, row type (Actuals or Budget), and the value.

I arrange this in (Power) pivot table where my Type and Date columns are in the columns, and then values are obviously in the values section. The result, for a given month, is that it shows me Actuals and Budget side-by-side.

I want to add a third column for variance between Actuals and Budget. So I tried using calculate,

=CALCULATE(SUM[Values],[Type] = "Actual")-CALCULATE(SUM[Values],[Type] = "Budget")

But the result of this is 4 columns because it repeats the variance calculation for each of the Actuals and Budget values which are in the column labels.

What's the proper way to create a calculated field to calculate the difference between two values within a single column?

It's almost like I want to mimic having row in my source table where the value in the Type column is Actuals vs. Budget, but PowerPivot, you cannot do this.

Thanks

 

[scottsen]

I think you will have to go ahead and make the 3 measures, [Total Actual], [Total Budget] and [Actual to Budget Delta], and not have Type on columns.

 

[Ozeroth]

Alternatively, you can play with Named Sets under PivotTable Options, Fields, Items & Sets, and Create Set based on Rows or Columns, where the Delta is not split by Type.
But I think Scott's solution is probably cleaner.

 

[SimonNU]

Another alternative would be to use the OLAP PivotTable extensions add-in to create a custom MDX query but, to echo Ozeroth, Scott's solution is standard practice and the best solution by a mile.