Microsoft Office Tutorials and References
In Depth Information
Figure 18-36: The Insert Calculated Item dialog box.
3. Click the Add button.
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.
A calculated item, unlike a calculated field, does not appear in the PivotTable Field task
pane. 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 Totals.
After you create the calculated items, they appear in the pivot table. Figure 18-37 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-38 shows the pivot table after creating
the two groups and adding subtotals.