Chapter 37: Adding Formulas to Power Pivot
37
- In the Measure dialog box, input the following information:
Table name Choose the table that you want to contain the calculated measure when look-
ing at the PivotTable Fields list. Don’t sweat this decision too much. The table you select has
no bearing on how the calculation works. It’s just a preference on where you want to see the
new calculation within the PivotTable Fields list.
Measure name Give your calculated measure a descriptive name.
Formula Enter the DAX formula that will calculate the results of your new field.
Formatting Options Specify the formatting for the calculated measure results.
In this example, we are using the following DAX formula:
=CALCULATE(
SUM(InvoiceDetails[UnitCost]),
YEAR(InvoiceHeader[InvoiceDate])=2007
)This formula uses the CALCULATE function to sum the UnitCost column from the
InvoiceDetails table, where the Year column in the InvoiceHeader is equal to 2007.- Click the Check Formula button to ensure that there are no syntax errors. If
your formula is well formed, you will see the message “No errors in formula.” If
there are errors, you will see a full description of the errors. - Click the OK button to confirm your changes and close the Measure dialog box.
You will immediately see your newly created calculated measure in the PivotTable. - Repeat steps 2–5 for any other calculated measure that you need to create.
In this example, we need a measure to show the 2006 cost:
=CALCULATE(
SUM(InvoiceDetails[UnitCost]),
YEAR(InvoiceHeader[InvoiceDate])=2006
)We will also need a measure to calculate the variance:
=[2007 Revenue]-[2006 Revenue]Figure 37.19 illustrates the newly created calculated measures. The calculated measures are
applied to each customer, displaying the variance between their 2007 and 2006 costs. As
you can see, each calculated measure is available for selection in the PivotTable Fields list.