Calculated Data Fields

Pivot tables offer two types of formulas. The most useful type defines a formula for a calculated field. This adds a new field to the pivot table. Calculations for calculated fields are always done at the summary level. If you define a calculated field for average price as Revenue divided by Units Sold, Excel first adds up the total revenue and the total quantity, and then it does the division of these totals to get the result. In many cases, this is exactly what you need. If your calculation does not follow the associative law of mathematics, it might not work as you expect.

To set up a calculated field, use the Add method with the CalculatedFields object. You have to specify a field name and a formula. Note that if you create a field called Average Price, the default pivot table produces a field called Sum of Average Price. This is misleading and downright silly. What you have is actually the average of the sums of prices. The solution is to use the Name property when defining the data field to replace Sum of Average Price with something such as Avg Price. Note that this name must be different from the name for the calculated field.

Listing 2 produces the report shown in Figure 13.

Listing 2. Code That Calculates an Average Price Field as a Second Data Field
```Sub TwoDataFields()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long

Set WSD = Worksheets("PivotTable")
Dim WSR As Worksheet
Dim WBO As Workbook
Dim WBN As Workbook
Set WBO = ActiveWorkbook

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")

' Turn off updating while building the table
PT.ManualUpdate = True

' Set up the row fields

' Define Calculated Fields

' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
End With

With PT.PivotFields("Units Sold")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
.NumberFormat = "#,##0"
End With

With PT.PivotFields("AveragePrice")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "#,##0.00"
.Name = "Avg Price"
End With

' Ensure that you get zeroes instead of blanks in the data area
PT.NullString = "0"

' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True

End Sub
```

13. The virtual "Data" dimension contains two fields from your dataset plus a calculation. It is shown along the column area of the report. 