How to calculate percentage from Pivot Table

echow

New Member
Joined
Aug 25, 2011
Messages
2
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%

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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top