Excel如何添加公式驱动的可折叠字段且总计基于源数据而非列聚合？

我们先来看一组用于演示需求的示例数据，样式如下：

基于上述数据我生成了如下所示的数据透视表：

尽管单行列的计算结果无误，但Cost per Sale这类自定义计算指标的总计结果不符合预期：比如总Cost per Sale应为40/70=0.57，但透视表默认返回的0.54是列值的平均值，未考虑各行列的量级。其他自定义计算指标也存在同类问题。我尝试在透视表外部添加公式计算得到了正确值，但无法跟随透视表同步展开/折叠，只要操作透视表的折叠展开功能就会出现引用错误，如下图所示：

因此我想咨询：Excel中是否有方法可以添加基于公式的可展开/折叠字段，且字段总计基于主字段的合计值计算，而非列聚合结果？

表格形式的可复现数据源 #

解决方案 #

普通透视表自带的「计算字段」功能默认对所有层级采用相同的聚合规则，所以会出现行级计算正确、总计为列值平均值的错误，使用Power Pivot的DAX度量值可以完美解决该需求，完全适配透视表的展开折叠逻辑，所有层级计算规则统一，操作步骤如下：

• 第一步：将数据源导入Power Pivot数据模型
  选中数据源任意单元格，点击「数据」选项卡 → 「从表格/范围」，确认勾选「我的表格有标题」后确定，数据会自动加载到Power Pivot编辑器中，保存后关闭编辑器即可。
• 第二步：创建自定义DAX度量值
  选中透视表任意单元格，点击「Power Pivot」选项卡 → 「度量值」→ 「新建度量值」，在弹出的编辑框中输入如下公式：

  Cost per Sale = DIVIDE( SUM('表名'[Cost]), SUM('表名'[Sales]) )
  

  将公式中的表名替换为实际导入的数据源表名，同时可根据需求设置度量值的格式为百分比或保留2位小数的数值。
• 第三步：将度量值添加到透视表值区域
  替换原有自定义计算字段即可。新的度量值会自动适配透视表的行列上下文：行/列展开时计算当前分组的Cost总和除以Sales总和，总计行直接取全局Cost总和除以全局Sales总和，计算逻辑完全符合预期。

如果不想使用Power Pivot，也可以开启透视表的「GETPIVOTDATA」函数自动引用功能，在透视表外写公式时通过GETPIVOTDATA获取对应层级的Cost和Sales汇总值再做除法，这类引用不会因为透视表展开折叠发生偏移，但需要提前对应所有层级的行标题，维护成本高于DAX度量值。

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