数据透视表计算字段：计算两个百分比的百分点差值问题

原始数据 #

Name    Name Avg    State Avg
A       10%         20%
A       10%         20%
A       5%          20%
B       50%         50%
B       50%         50%
B       50%         50%

需求 #

计算每个分组（Name）下，Name Avg的平均值与State Avg的平均值之间的百分点差值，例如Name A的预期结果为：(10%+10%+5%)/3 - 20% = -11.67%。

问题原因 #

数据透视表的计算字段逻辑是基于原始行数据运算，再对结果汇总，而非基于透视表的分组汇总值运算：

• 当使用计算字段='Name Avg' - 'State Avg'时，它会先计算每一行的差值，再对所有行的差值做Sum或Average汇总。选Sum时，得到的是SUM(Name Avg) - SUM(State Avg)，和需求不符；
• 计算字段不支持嵌套聚合函数（如SUM(AVERAGE('Name Avg'))），这类写法无法识别透视表的分组汇总值，必然返回错误结果。

若你设置Average汇总方式仍未得到正确结果，大概率是操作时未正确应用汇总设置，或是对预期结果的描述存在笔误。

解决方案 #

方案1：调整计算字段的汇总方式 #

直接使用计算字段='Name Avg' - 'State Avg'，将该字段的汇总方式设置为Average，即可得到预期的-11.67%（Name A）和0%（Name B）。

方案2：基于透视表汇总值手动计算 #

1. 在数据透视表中分别添加Name Avg和State Avg字段，均设置汇总方式为Average；
2. 在透视表右侧空白列输入公式，引用对应的汇总值相减（例如=D2-E2，假设D列是Name Avg的平均值，E列是State Avg的平均值），下拉填充即可。

方案3：源数据预处理 #

使用Power Query或Excel公式（如AVERAGEIF）先计算每个Name的Name Avg平均值和State Avg平均值，再用处理后的数据创建透视表，直接计算差值。

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