如何用VBA在数据透视表中显示去重计数的百分比

你遇到的问题根源在于：你操作的是Power Pivot度量值（[Measures].[customer_num]），这类DAX驱动的字段无法通过原生的.Calculation = xlPercentOfTotal属性设置百分比计算——这个属性仅支持Excel普通数据透视表字段，不适用Power Pivot度量值。手动操作能成功是因为Excel后台自动生成了基于原去重计数的DAX百分比逻辑，但VBA需要你显式实现这个逻辑。

下面是两种可行的解决方法：

方法1：修改现有度量值的DAX公式 #

直接更新原度量值的公式，把去重计数转为占总计的百分比：

With ActiveSheet.PivotTables("Shipping_Dest").PivotFields("[Measures].[customer_num]")
    .Caption = "Distinct Count % of Total"
    ' 替换下面的'你的表名'为实际数据表名称
    .Formula = "DIVIDE(DISTINCTCOUNT('你的表名'[customer_num]), CALCULATE(DISTINCTCOUNT('你的表名'[customer_num]), ALL('你的表名')))"
    .NumberFormat = "0.00%"
End With

方法2：新增百分比专用度量值 #

如果不想改动原度量值，可以新建一个专门的百分比度量值并添加到透视表：

' 创建新的百分比度量值
ActiveWorkbook.Model.ModelMeasures.Add _
    Name:="customer_num_Percent", _
    ' 替换'你的表名'为实际数据表名称
    Formula:="DIVIDE(DISTINCTCOUNT('你的表名'[customer_num]), CALCULATE(DISTINCTCOUNT('你的表名'[customer_num]), ALL('你的表名')))", _
    FormatInformation:=xlNumberFormatPercentage

' 将新度量值添加到数据透视表
ActiveSheet.PivotTables("Shipping_Dest").AddDataField _
    ActiveSheet.PivotTables("Shipping_Dest").PivotFields("[Measures].[customer_num_Percent]"), _
    "Distinct Count % of Total"

补充说明 #

原代码报错是因为Power Pivot度量值的.Calculation属性是只读的，无法通过VBA赋值。手动操作时Excel偷偷帮你做了额外工作：基于原度量值创建了新的百分比计算逻辑，但这个过程在VBA里需要你手动实现。

内容的提问来源于stack exchange，提问作者sean2020