Adding Calculated Detail Fields and
Let's round out our data presentation with
the addition of derived detail data via calculated fields and calculated
totals. These calculations can make our PivotTable, and ultimately our
PivotChart, far more useful to information consumers, as we will see by taking
the following steps:
- Click the Calculated Totals and Fields button
(shown in Illustration 12) on the PivotTable toolbar.
Illustration 12: The Calculated Totals and Fields Button
- Select Create Calculated Detail Field from the
dropdown options that appear.
The PivotTable Field List appears (unless already
present), with a new field, whose default name is Calculated, appearing
at its bottom. 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.
- On the Calculation tab, in the text area below the Name
text box, type the following:
NOTE: For comments and recommendations on the use of
the Insert Reference To button, see our last lesson, Create a PivotTable
View in Access.
- Type Total Net Sale into the Name
The Properties box, Calculation tab, appears
as shown in Illustration 13.
Illustration 13: The Properties Box, Calculation Tab
- Click the Format tab on the Properties
- In the Number format selector, select Currency.
- Click the Bold button in the Text Format
section of the Format tab.
The Properties box, Format tab, appears as
shown in Illustration 14.
Illustration 14: The Properties Box, Format Tab
- Return to the Calculation tab.
- Click the Change button at the bottom of the Calculation
- Close the Properties box and PivotTable Field
The new Total
calculated detail field appears within our PivotTable.
- Click and drag the label of the Total Net Sale
calculated field to the right of the Discount field, if necessary.
- With the Total Net Sale field still selected,
click AutoCalc on the PivotTable toolbar, and then click Sum.
The AutoCalc button, complete with its cascaded menu,
is shown in Illustration 15.
Illustration 15: The AutoCalc Button with Cascaded Menu
NOTE: For comments on the use of the AutoCalc
button, see our last lesson, Create a PivotTable View in Access.
Our PivotTable now appears as partially shown in Illustration
Illustration 16: Partial PivotTable View, with Totals
We have successfully created a PivotTable
view with calculated detail fields and calculated totals. Now let's build a PivotChart
view based upon our PivotTable data, and organize and format our data
presentation within the PivotChart to enhance its value to information