Introduction to Power Pivot - Microsoft Business Intelligence Tools for Excel Analysts - page 63

5. Click OK to confirm your changes and close the Calculated Field dialog box.

You immediately see your newly created calculated field in the PivotTable.

6. Repeat Steps 2-5 for any other calculated field you need to create.

In this example, we created two additional calculated fields:

=CALCULATE(SUM(InvoiceDetails[Total Revenue]),InvoiceHeader[Year]=2006)
=[2007 Revenue]-[2006 Revenue]

Figure 3-21 shows the newly created calculated fields. The calculated fields are applied to each cus-
tomer, showing the variance between their 2007 and 2006 revenues. Note that once you create cal-
culated fields, they're available for selection in the PivotTable Fields list.

Figure 3-21: Calculated fields can be seen in the PivotTable Fields list.

There are over 140 different DAX functions. You can click the fx button in the Calculated
Field dialog box to see all the available DAX functions that can be used to implement a
new calculated field. A full overview of DAX is out of the scope of this topic. If after
reading this section, you have a desire to learn more about DAX, consider picking up
Microsoft Excel 2013: Building Data Models with PowerPivot, by Alberto Ferrari and Marco
Russo (Microsoft Press).

Note

You may find that you need to either edit or delete a calculated field. You can do so by following
these steps:

1. Click anywhere inside your PivotTable, then click the Power Pivot tab on the Ribbon and
choose Calculated Fields ➜ Manage Calculated Fields.

The Manage Calculated Field dialog box opens, as shown in Figure 3-22.

2. Select the target calculated field and click either the Edit or Delete button.

Clicking the Edit button opens the Calculated Field dialog box, where you can make changes
to the calculation setting. Clicking the Delete button opens a message box asking you to
confirm that you want to remove the calculated field.