May I know how do I write the VB code to calculate the Percentage of sum/count using the same pivot field in pivot table calculation?
Note: SumGood_Apple and TotalCount_Apple are generated from the Pivot Table using the same pivot fields. And I assume the Percent of Apple can also be generated using the calculation options, but not sure how.
Field SumGood_Apple TotalCount_Apple Pct_of_Apple_from_Field
A______ 0_______________ 1_______________ 0%
B______ 2_______________ 3_______________ 67%
C______ 0_______________ 1_______________ 0%
D______ 3_______________ 4_______________ 75%
E______ 0_______________ 1_______________ 0%
F______ 1_______________ 2_______________ 50%
G______ 1_______________ 5_______________ 20%
PS: Using Excel 2010 version
Appreciate if anyone can help.
Note: SumGood_Apple and TotalCount_Apple are generated from the Pivot Table using the same pivot fields. And I assume the Percent of Apple can also be generated using the calculation options, but not sure how.
Field SumGood_Apple TotalCount_Apple Pct_of_Apple_from_Field
A______ 0_______________ 1_______________ 0%
B______ 2_______________ 3_______________ 67%
C______ 0_______________ 1_______________ 0%
D______ 3_______________ 4_______________ 75%
E______ 0_______________ 1_______________ 0%
F______ 1_______________ 2_______________ 50%
G______ 1_______________ 5_______________ 20%
Code:
' Set up the row & column fields
PT.AddFields RowFields:="Field", ColumnFields:="Data"
' Set up the data fields
With PT.PivotFields("Apple")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.Name = "Sum_Apple"
End With
With PT.PivotFields("Apple")
.Orientation = xlDataField
.Function = xlCount
.Position = 2
.Name = "Count_Apple"
End With
'Set up Pct of Apple
With PT.PivotFields("Apple")
.Orientation = xlDataField
.Function = xlSum
.Caption = "% Apple Field"
'.Calculation = ???
'.BaseField = "Date"
'.BaseItem = "(previous)"
.Position = 3
.NumberFormat = "#0.0%"
End With
PS: Using Excel 2010 version
Appreciate if anyone can help.