Home > Software design >  Pivot Table- Add Percent of the Grand Total
Pivot Table- Add Percent of the Grand Total

Time:03-29

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!

CodePudding user response:

Trying recording macro while creating % with pivot:

enter image description here

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
  • Related