Using the CalculatedFields.Add Method to create a calculated field. Refer Image 1a which shows a PivotTable report named "PivotTable1" showing Sales using the summary function of Sum. Image 1b shows the Pivot report with a new calculated field added "Variance-%", using the below code. Note that the source range field names are "Sales" and "Budgeted Sales" on which calculations are based.
Code
Sub PivotTableCalculatedFields1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
'for empty cells in the data area of a PivotTable report, show a specified value, using the PivotTable.NullString Property:
PvtTbl.NullString = "0"
PvtTbl.DisplayNullString = True
PvtTbl.CalculatedFields.Add Name:="Variance", Formula:="=IF(OR(Sales=0,Budgeted Sales=0),0,(Sales - Budgeted Sales)/Budgeted Sales)"
With PvtTbl.PivotFields("Variance")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "0.00%"
.Caption = "Variance-%"
End With
End Sub
Admin
Display More