Part V: Understanding Power Pivot and Power Query
Imagine you wanted to show the difference in unit costs between the years 2007 and 2006
for each of your customers. Think about what technically has to be done to achieve this
calculation. You’d have to figure out the sum of unit costs for 2007, then you’d have to get
the sum of unit costs for 2006, and finally you’d have to subtract the sum of 2006 from the
sum of 2007. This is a calculation that simply can’t be done using calculated columns. Using
calculated measures is the only way to get the cost variance between 2007 and 2006.
Follow these steps to create a calculated measure:
- Start with a PivotTable created from a Power Pivot model.
- Click the Power Pivot tab in the Excel Ribbon, and select Measures ➪ New
Measure. This will open the Measure dialog box shown in Figure 37.18.
The sample file for this chapter contains a tab called Calculated Measures with a PivotTable already created.
FIGURE 37.18
Creating a new calculated measure
You will notice in Figure 37.18 that the DAX calculation is entered with carriage returns and spaces. This is purely for
readability purposes. The fact is that DAX ignores white space and is not case sensitive. So, it’s very forgiving on how
you structure your calculation. Given this fact, it’s always best to attempt to achieve some readability with the use of
carriage returns and spaces.
