Calculated Fields

You can create new fields in a PivotTable by performing calculations on existing fields. For example, you might want to calculate the weighted average price of each product. You could create a new field called AveragePrice and define it to be Revenue divided by NumberSold, as in the following code:

Sub CalculateAveragePrice() Dim pvt As PivotTable

'Add new Worksheet and PivotTable Worksheets.Add

Set pvt = ActiveWorkbook.PivotCaches(1).CreatePivotTable( _

TableDestination:=ActiveCell, TableName:="AveragePrice")

With pvt

'Remove AveragePrice if it exists On Error Resume Next .PivotFields("AveragePrice").Delete On Error GoTo 0

'Create new AveragePrice

.CalculatedFields.Add Name:="AveragePrice", _ Formula:="=Revenue/NumberSold"

'Add Row and Column fields

.AddFields RowFields:="Customer", ColumnFields:="Product"

'Add AveragePrice as Data field With .PivotFields("AveragePrice") .Orientation = xlDataField .NumberFormat = "0.00" End With

'Remove grand totals .ColumnGrand = False .RowGrand = False

End With

End Sub

CalculateAveragePrice adds a new worksheet and uses the CreatePivotTable method of the previously created PivotCache to create a new PivotTable in the new worksheet. So you can run this code repeatedly, it deletes any existing PivotField objects called AveragePrice. The On Error statements ensure that the code keeps running if AveragePrice does not exist.

The CalculatedFields collection is accessed using the CalculatedFields method of the PivotTable. The Add method of the CalculatedFields collection is used to add the new field. Note that the new field is really added to the PivotCache, even though it appears to have been added to the PivotTable. It is now also available to your first PivotTable, and deleting the new PivotTable would not delete AveragePrice from the PivotCache. Once the new field exists, you treat it like any other member of the PivotFields collection. The final lines of code remove the grand totals that appear by default.

The Table in Figure 7-8 results from the changes outlined in the previous paragraph. Because the prices do not vary in the source data, it is not surprising that the weighted average prices for each product in the PivotTable do not vary either.

_ -


A 1 B





Sum of AveragePrice Column Labels ■


Row Labels - Apples


Oranges Pears



Kee 12.50





Pradesh 12.50





Roberts 12.50




6 ISmith 12.50

20 00






► H Sheeti Sheet! Safes -J

Figure 7-8

Take care when creating CalculatedFields. You need to appreciate that the calculations are performed after the source data has been summed. In this example, Revenue and NumberSold were summed and one sum was divided by the other sum. This works fine for calculating a weighted average price and is also suitable for simple addition or subtraction. Other calculations might not work as you expect.

For example, say you don't have Revenue in the source data, and you decide to calculate it by defining a CalculatedField equal to Price multiplied by NumberSold. This would not give the correct result. You can't get Revenue by multiplying the sum of Price by the sum of NumberSold, except in the special case where only one record from the source data is represented in each cell of the PivotTable.

0 0

Post a comment