Microsoft Office Tutorials and References
In Depth Information
Creating a Calculated Field or Calculated Item
4. 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)
5. Click OK to close the dialog box.
Figure 18-35: The Insert Calculated Item dialog box.
A calculated item, unlike a calculated field, does not appear in the PivotTable Field List.
Only fields appear in the field list.
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 item, so the commission amounts are included with the sales
amounts. To turn off Grand Totals, choose PivotTable Tools➜Design➜Layout➜Grand
After you create the calculated items, they appear in the pivot table. Figure 18-36 shows the
pivot table after adding the four calculated items. Notice that the calculated items are added to
the end of the Month items. You can rearrange the items by selecting the cell and dragging its
border. Another option is to create two groups: One for the sales numbers and one for the
commission calculations. Figure 18-37 shows the pivot table after creating the two groups and adding