Pivot Table- Add Percent of the Grand Total

March 28, 2022

I want to add a calculated filed of percent participation in my Pivot Table using vba. So I want something like this:

Items	Sum of Points	Percent
First	4	40%
Second	3	30%
Third	3	30%
Total	10	100 %

I thought there is a build in formula and tried to something like this:

 With ptt
    .CalculatedFields.Add name:="value", Formula:=xlPercentOf
    .PivotFields("value").Orientation = xlDataField
    .PivotFields("Sum of value").name = "Percent"
    .PivotFields("Prercent").NumberFormat = "0.00%"
 End With

But i got the same percent value in every row, so its not what I wanted. I will be greatful for any help!

>Solution :

Trying recording macro while creating % with pivot:

and it returns its script that can hint you some things:

   ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Points"), "Sum of Points2", xlSum    
   With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of Points2")
        .Caption = "Percent"
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0.00%"
    End With