MS Access for the Business Environment: Create a PivotChart View in Access - Page 5

August 4, 2003

Adding Calculated Detail Fields and Calculated Totals

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:

1. Click the Calculated Totals and Fields button (shown in Illustration 12) on the PivotTable toolbar.

2. 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.

3. On the Calculation tab, in the text area below the Name text box, type the following:

		Quantity*UnitPrice-((Quantity*UnitPrice)*Discount)

NOTE: For comments and recommendations on the use of the Insert Reference To button, see our last lesson, Create a PivotTable View in Access.

4. Type Total Net Sale into the Name text box.

The Properties box, Calculation tab, appears as shown in Illustration 13.

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 14.

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.

The new Total Net Sale calculated detail field appears within our PivotTable.

11. Click and drag the label of the Total Net Sale calculated field to the right of the Discount field, if necessary.
12. 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.

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 16 .

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 consumers.