Excel 365双表透视表如何创建行级加权求和度量值

你之前用SUMX无法读取权重值的核心原因：当前权重表是单行宽表结构，未与透视表数值来源的事实表建立关联，直接引用权重列时会被透视表当前行的筛选上下文截断，无法获取固定权重值。 #

方案1：无需调整现有表结构，直接编写度量值 #

直接用ALL()函数清除权重表上的所有筛选上下文，固定读取三个权重值，再逐行计算加权和即可，公式如下（注意替换成你自己的实际表名）：

Row Weighted Total = 
// 固定读取三个权重值，不受透视表筛选影响
VAR w1 = CALCULATE(MAX('权重配置表'[Weight1]), ALL('权重配置表'))
VAR w2 = CALCULATE(MAX('权重配置表'[Weight2]), ALL('权重配置表'))
VAR w3 = CALCULATE(MAX('权重配置表'[Weight3]), ALL('权重配置表'))
RETURN
// 逐行遍历事实表计算加权和
SUMX(
    '数值来源事实表',
    '数值来源事实表'[Header1] * w1 + '数值来源事实表'[Header2] * w2 + '数值来源事实表'[Header3] * w3
)

结果校验：第一行3*13 + 2*5 +5*8 = 89，第二行6*13 +7*5 +4*8=145，完全匹配预期输出。

方案2：调整表结构的可扩展方案（推荐，后续新增指标无需改公式） #

如果后续需要新增更多计算列，建议先把两张表都调整为纵向结构，降低维护成本：

1. 把原横向权重表转为纵向二维表：

   MetricName  WeightValue
   Header1     13
   Header2     5
   Header3     8
   

2. 对原事实表做逆透视操作，把Header1/Header2/Header3三列转为MetricName（存指标名）、MetricValue（存指标数值）两列的结构
3. 用MetricName字段在两张表之间建立一对多关系
4. 编写通用度量值：

   Row Weighted Total = 
   SUMX(
       VALUES('事实表'[MetricName]),
       CALCULATE(SUM('事实表'[MetricValue])) * CALCULATE(MAX('权重表'[WeightValue]))
   )
   

之前SUMX写法的问题 #

未对权重引用加ALL()清除筛选上下文时，透视表每行的行上下文会对权重表做筛选，权重表无匹配维度值时返回空，最终计算结果错误。

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