Power BI嵌套度量值SummerizeConsumption总计计算错误求助

问题描述 #

编写的SummerizeConsumption度量值明细行计算正确，但总计结果错误，相关度量值代码如下：

有问题的度量值：SummerizeConsumption #

SummerizeConsumption = 
VAR _Table = SUMMARIZE('Chips Consuption Query','Chips Consuption Query'[Component.Component Level 01.Key])
RETURN SUMX(_Table,[Consumption])

关联的Consumption度量值及基础度量值 #

[Consumption]=
var Val1 = CALCULATE(SUMX(
    VALUES('Parent Consumption Query'[Component.Component Level 01.Key]), IF('Parent Consumption Query'[Component.Component Level 01.Key] IN { "P1","P2","P3","P4","P5" }, 'Parent Consumption Query'[Parent Issue Stock Qty.]) ))
    
var val2 = CALCULATE(SUMX(
    VALUES('Parent Production Query'[Material.Material Level 01.Key]), IF('Parent Production Query'[Material.Material Level 01.Key] IN { "P1","P2","P3","P4","P5" }, 'Parent Production Query'[Parent Production Qty.]) ))

RETURN DIVIDE(Val1,val2)

基础度量值：

'Parent Consumption Query'[Parent Issue Stock Qty.]= SUM('Parent Consumption Query'[Issue Total Stock])  
'Parent Production Query'[Parent Production Qty.] =SUM('Parent Production Query'[Activity quantity])

总计错误的效果如图所示：

问题原因 #

1. SUMMARIZE仅对Chips Consuption Query的维度分组，未将上下文正确传递给Consumption度量值——Consumption的计算依赖Parent Consumption Query和Parent Production Query的维度，迭代_Table时，每个行上下文无法正确关联这两个表的对应维度，导致总计计算时上下文混乱。
2. 原逻辑中，SUMX迭代的是Chips表的维度列表，但Consumption在无明细上下文时会直接计算全量的Val1/Val2，而非各分组的Consumption之和。

修正方案 #

改用ADDCOLUMNS显式为每个分组计算对应的Consumption值，确保每个分组的上下文正确传递，再对计算结果求和：

SummerizeConsumption = 
VAR _Table = 
    ADDCOLUMNS(
        SUMMARIZE('Chips Consuption Query','Chips Consuption Query'[Component.Component Level 01.Key]),
        "@Consumption", [Consumption]
    )
RETURN SUMX(_Table, [@Consumption])

或者更严谨地使用SUMMARIZECOLUMNS，确保维度关联正确：

SummerizeConsumption = 
VAR _Table = 
    SUMMARIZECOLUMNS(
        'Chips Consuption Query'[Component.Component Level 01.Key],
        "ConsumptionValue", [Consumption]
    )
RETURN SUMX(_Table, [ConsumptionValue])

验证逻辑 #

修正后的代码会先为每个Component.Component Level 01.Key分组计算对应的Consumption值，再对这些值求和，确保总计是明细行结果的准确累加，解决上下文不匹配导致的总计错误。

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