Yesterday i had a excellent question and mail conversation with Jan at my ask a question page concerning strange measure behaviors. It appeared he had used a calculated column where he had to use a calculated measure to get the result he wanted. In this blog post i try to explain where you should create your measure and why you should use one or the other.
In PowerPivot we have two ways to create new information using DAX formula’s. Using the definition from the MS PowerPivot blog post about measures:
DAX formulas can be used to define calculated columns in a table and they can also be used to define measures. Calculated columns are just like in Excel – you enter a formula, and that formula is evaluated for each row in the table, effectively filling in the entire column with values.
A measure is different. When you define a measure, you provide a DAX formula and a name. This measure can be placed onto an Excel PivotTable, and it will be evaluated many times, once for each cell in the values area of that PivotTable.
Ok so what does that mean and why two types ? Let’s consider the following example, i have this data:
First i want to create a measure that divides ms2 by ms1. I start by creating a calculated column:
.
What we can see here is that we have a new column inside our data, the calculation is done for each row in the data and is only calculated when created or after data refresh of the table.
What happens when we put this inside our PivotTable ?
The first thing we can notice here is that our new “CalcColDivide” looks identical to any other column inside the Table1. And this is exactly how PowerPivot handles the data inside this column, since the data is already calculated PowerPivot has no notion of our formula, it will just do a sum on the Grand total row.
When we add the same calculation as a measure inside the PivotTable we immediately get the error “The value for column ‘ms1’ in table ‘Table1’ cannot be determined in the current context.”. That is because the values here are aggregated, the formula has to be:
MsDivide = sum(Table1[ms2])/sum(Table1[ms1])
This results in this pivottable:
The values are calculated for each row in the pivottable, for each row it will do a divide of ms2 by ms1 (most of the time this will be an aggregation). The values are calculated when your pivottable changes or you do a refresh. In the Grand total row the value is also calculated using the sum of the two values.
One thing to keep in mind here is that although you create this measure in Excel instead of the PowerPivot window both measures will be stored inside the PowerPivot model. One will be stored as a column in the dimension and one as a measure.
Now we look at another example, I want to use a special factor at the division that multiplies the ms2 with -1 when the gender is “u”. This means we need to know which gender is in context.
We create a calculated column to do this:
And this is where calculated column is meant for, when we want to do this using a calculated measure we need to build a very complex DAX statement to achieve this. As we have seen the calculated measure
Conclusion:
- Use Calculated column to enrich data in your table with a new column based on values in other columns of the same row, think Month of a date
- Use Calculated measure to create measures that will be used in your pivottable and doesn’t need data on the row level, for example when you would like to subtract two measures