Excel数据透视表Calculated Field计算错误，不新增源列如何获取按国家产品正确汇总结果

问题背景 #

现有A-D列源数据需要生成按国家、产品层级汇总的透视表，且不能修改源数据插入新列，之前使用普通计算字段得到的结果不符合预期。

错误原因 #

普通Excel透视表的计算字段默认逻辑是先对涉及的所有字段分别做求和汇总，再执行你设置的运算规则。比如你要算「数量×单价」的总金额，普通计算字段会先把所有行的数量求和、所有行的单价求和，再把两个总和相乘，自然和你预期的「逐行计算数量乘单价后再汇总」的结果完全不符。

解决方法（全程不需要修改源数据） #

方法1：使用Power Pivot数据模型新建度量值（最简便） #

• 选中源数据任意单元格，点击「插入」选项卡下的「数据透视表」，勾选弹窗底部的「将此数据添加到数据模型」，点击确认创建透视表
• 点击顶部「Power Pivot」选项卡，选择「度量值」→「新建度量值」
• 按你的实际字段名设置度量值公式，示例公式如下：

  总金额 = SUMX('源数据表名', [数量] * [单价])
  

  公式里的源数据表名替换为你导入数据时的实际表名，[数量] [单价]替换为你对应列的字段名称即可
• 确认创建度量值后，将「国家」「产品」字段拖到透视表行区域，将新建的「总金额」度量值拖到值区域，得到的结果就和你手动制作的绿色预期区域完全一致

方法2：用Power Query添加计算列（不修改原始工作表数据） #

如果不习惯用DAX度量值，可以用Power Query预处理数据，全程不会改动你原工作表的源数据：

• 选中源数据任意单元格，点击「数据」选项卡→「从表格/区域」，将数据导入Power Query编辑器
• 在Power Query编辑器顶部点击「添加列」→「自定义列」，输入自定义列公式（按你实际字段调整）：

  = [数量] * [单价]
  

• 点击确定后，选择「关闭并上载至」，勾选「仅创建连接」和「将此数据添加到数据模型」
• 用这个连接创建透视表，把「国家」「产品」拖到行区域，新增的自定义列拖到值区域即可得到正确结果

内容的提问来源于stack exchange，提问作者Sonu Excel Powered