CODEWITHSUNDEEP
excelvbapivot-table
sean2020
sean2020

Reputation: 17

How to display Distinct Count in pivot table as a Percentage with VBA

Basically, I am trying to render a pivot table where the distinct count of a value is displayed as a percentage.

I am using VBA to create the pivot table. I can manually change the "Distinct Count" to the "% of Grand Total" which works fine, but with VBA an error is constantly being thrown. I even resorted to a macro recording to try and fix the issue, but have been unable to do so thus far.

Here is the code that is causing the issue right now:

With ActiveSheet.PivotTables("Shipping_Dest").PivotFields( _
    "[Measures].[customer_num]")
    .Caption = "Distinct Count of customer_num"
    .Function = xlDistinctCount
    .Calculation = xlPercentOfTotal
    .NumberFormat = "0.00%"
End With

So far, it appears that the "xlPercentOfTotal" is causing the error, and I cannot figure out a way to solve it. Any help is greatly appreciated.

Upvotes: 0

Views: 432

Answers (1)

Rory
Rory

Reputation: 34075

Do it as two steps:

With ActiveSheet.PivotTables("Shipping_Dest").PivotFields( _
    "[Measures].[customer_num]")
    .Caption = "Distinct Count of customer_num"
    .Function = xlDistinctCount
End With
With ActiveSheet.PivotTables("Shipping_Dest").PivotFields( _
    "[Measures].[Distinct Count of customer_num]")
    .Calculation = xlPercentOfTotal
    .NumberFormat = "0.00%"
End With

and note the measure name change for the second part

Upvotes: 1

Related Questions