PowerBI基于双类别组合计算新列占比的技术问题

需求规则 #

每行Col5的计算逻辑：

• 先计算当前行的差值：Col4 - Col3
• 再计算当前行所属Col1+Col2组合的总差值：SUM(Col4) - SUM(Col3)（同一Col1+Col2下所有行的Col4总和减Col3总和）
• Col5 = 行差值 ÷ 组合总差值，最终以百分比呈现，且同一Col1+Col2组合的Col5总和需为100%

原始数据示例 #

Col1  Col2   Col3  Col4  
 A    Type1   2     4
 B    Type2   5     9
 A    Type3   9     12
 B    Type1   4     2
 A    Type3   1     2
 B    Type2   9     8
 A    Type2   7     3

预期计算结果 #

Col1  Col2   Col3  Col4  Col5
 A    Type1   2     4     66.6%
 B    Type2   5     9    133.3%
 A    Type3   9     12   -300%
 B    Type1   4     2     100%
 A    Type1   1     2    33.3%
 B    Type2   9     8   -33.3%
 A    Type2   7     3     400%

错误尝试及问题 #

1. 创建度量值：Total = SUM(Col4)-SUM(Col3)，再计算Col5=(Col4-Col3)/Total，得到错误结果：

Col1  Col2   Col3  Col4  Col5
 A    Type1   2     4     0%
 B    Type2   5     9    -100%
 A    Type3   9     12    0%
 B    Type1   4     2    -100%
 A    Type3   1     2     0%
 B    Type2   9     8    -100%
 A    Type2   7     3    -100%

问题原因：度量值的上下文是当前行，导致Total计算的是当前行的差值而非分组总差值。

2. 使用快速度量：仅支持单类别（Col1或Col2）分组，无法实现Col1+Col2双组合的总差值计算。

正确解决方案 #

创建计算列，使用DAX公式实现分组总差值的计算：

Col5 = 
VAR RowDiff = 'Table'[Col4] - 'Table'[Col3]
VAR GroupTotalDiff = CALCULATE(
    SUM('Table'[Col4]) - SUM('Table'[Col3]),
    ALLEXCEPT('Table', 'Table'[Col1], 'Table'[Col2])
)
RETURN DIVIDE(RowDiff, GroupTotalDiff, BLANK()) * 100

公式说明 #

• RowDiff：计算当前行的Col4与Col3差值
• GroupTotalDiff：通过ALLEXCEPT保留当前行的Col1和Col2筛选上下文，计算该组合下的总差值
• DIVIDE：安全处理除零情况（若组合总差值为0，返回BLANK()），最后乘100转换为百分比格式

设置列格式为百分比后，即可得到符合预期的结果。

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