# DAX question on calculating difference between two column values



## cr731 (Nov 9, 2015)

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 (Nov 9, 2015)

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 (Nov 9, 2015)

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 (Nov 12, 2015)

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.


----------

