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

Follow these steps to create a calculated field:

1. Start with a PivotTable created from a Power Pivot model.

2. Click the Power Pivot tab on the Ribbon and choose Calculated Fields ➜ New Calculated Field.

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

Figure 3-20: Creating a new calculated field.

3. Set the following inputs:

Table Name: Choose the table that will contain the calculated field when looking at the
PivotTable Fields list.

●

Calculated Field Name: Give your calculated field 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 field results.

●

In this example, we use the following DAX formula:

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

This formula uses the Calculate function to sum the Total Revenue column from the
InvoiceDetails table, where the Year column in the InvoiceHeader is equal to 2007. This is just
one of a limitless number of formulas you can use to define a calculated field.

4. Click the Check Formula button to ensure there are no syntax errors.

If your formula is well formed, you see the message No errors in formula . If there are
errors, you see a full description of the errors.