Chapter 35: Analyzing Data with Pivot Tables
729
- Enter a name for the new item in the Name field and specify the formula in the
Formula field (see Figure 35.17). The formula can use items in other fields, but it can’t
use worksheet functions. For this example, the new item is named Qtr1 Commission,
and the formula appears as follows:
=10%*(Jan+Feb+Mar) - Click Add.
- Repeat Steps 2 and 3 to create three additional calculated items:
Qtr2 Commission: = 11%*(Apr+May+Jun)
Qtr3 Commission: = 12%*(Jul+Aug+Sep)
Qtr4 Commission: = 12.5%*(Oct+Nov+Dec)- Click OK to close the dialog box.
FIGURE 35.17
The Insert Calculated Item dialog box.Note
A calculated item, unlike a calculated field, does not appear in the PivotTable Field List. Only fields appear in
the field list. nCaution
If you use a calculated item in your pivot table, you may need to turn off the Grand Total display for columns
to avoid double counting. In this example, the Grand Total includes the calculated items, so the commission
amounts are included with the sales amounts. To turn off Grand Totals, choose PivotTable Tools ➪ Design ➪
Layout ➪ Grand Totals. n