Adding Calculated Detail Fields and
Calculated Totals
While we can bring much of the data we
need into the PivotTable simply by selecting the appropriate data fields, as we
have seen above, we can also present derived detail data via calculated fields.
We can also present calculated totals. These calculations can make our
PivotTable far more useful to information consumers, as we will see by taking
the following steps:
1. Click
the Calculated Totals and Fields button (shown in Illustration 18)
on the PivotTable toolbar.
Illustration 18: The Calculated Totals and Fields Button
2. Select
Create Calculated Detail Field from the dropdown options that appear.
The PivotTable Field List appears, with a new field,
whose default name is Calculated, appearing at its bottom,
as shown in Illustration 19. In addition, the Properties dialog
for the new Calculated field appears, typically in front of the PivotTable
Field List, defaulted to show the Calculation tab.
Illustration 19: The PivotTable Field List, with New
Field, and the Associated Properties Box
3. On
the Calculation tab, in the text area below the Name text box,
type the following:
UnitPrice * Quantity - Discount
NOTE: We can use the Insert Reference To button, after selecting each of the fields in sequence (inserting the appropriate operators), as an option for inputting our calculation. This is perhaps cumbersome, but can often help us to ensure that the fields are represented correctly; an example here would be Unit Price, which appears in the PivotTable Field List as "Unit Price" (with a space between the words). If we simply type in "Unit Price," our calculation results in an error. However, if we build the calculation using the Insert Reference To button (a form of "expression builder" approach), the correct format of "UnitPrice" (no space between words) is input - saving us the time, ultimately, in clearing the error.
4. Type
"Net Sale" into the Name text box.
The Properties box, Calculation tab, appears
as shown in Illustration 20.
Illustration 20: The Properties Box, Calculation Tab
5. Click
the Format tab on the Properties dialog.
6. In
the Number format selector, select Currency.
7. Click
the Bold button in the Text Format section of the Format
tab.
The Properties box, Format tab, appears as
shown in Illustration 21.
Illustration 21: The Properties Box, Format Tab
8. Return
to the Calculation tab.
9. Click
the Change button at the bottom of the Calculation tab.
10. Close the Properties
box and PivotTable Field List.
» 
Comment and Contribute
