Power BI度量值求和异常：行值正确但总计及部分物料数值不符

原始DAX度量值 #

[Raw material effect per 1 unit PY [PC]] = 

VAR PyMovingAvgPrice = [PY Moving Avg Price [LC]]
RETURN
SUMX('RM price development',
     IF ( 'RM price development'[Base] = "Weight" , 
     'RM price development'[RM effect per material unit PC PY PQ] ,
    PyMovingAvgPrice * 'RM price development'[RM effect per material unit PC PY PQ]
))

问题现象 #

• 逐行查看时数值显示正常，但求和总计错误
• 将数据导出为CSV后，发现3种物料的数值与Power Query（PQ）中显示的不一致

关联度量值逻辑 #

关联的[PY Moving Avg Price [LC]]计算逻辑如下：

[PY Moving Avg Price [LC]] = CALCULATE(
    SUMX(Material,
    SUM(Consumption[Consumed Value [LC]])/SUM(Consumption[Consumed Quantity])),SAMEPERIODLASTYEAR('Reporting date'[Date]))

调整后的度量值 #

已对度量值进行调整，代码如下：

[Raw material effect per 1 unit PY [PC]] = 

VAR  RMprice = MAXX('RM price development','RM price development'[Base type])
VAR RMpricePCPY = MAXX('RM price development','RM price development'[RM effect per material unit PC PY PQ])
VAR _row_level =
  IF ( RMprice = "Weight", 
    RMpricePCPY,
    IF(RMprice="Index", [PY Moving Avg Price [LC]]*RMpricePCPY,BLANK()))

VAR _total =
    SUMX (
        ADDCOLUMNS (
            VALUES ( Material[Material no] ),
            "@RM per 1 UoM",
         IF ( RMprice = "Weight" , 
     RMpricePCPY ,
     IF(RMprice="Index", [PY Moving Avg Price [LC]]*RMpricePCPY,BLANK())
)),
        [@RM per 1 UoM]
    )
RETURN
    IF ( HASONEVALUE ( Material[Material no] ), _row_level, _total )

当前状态 #

调整后总计数值有所改善，但仍因3种物料的问题导致结果不正确

内容的提问来源于stack exchange，提问作者Jakub Jabłoński