Power BI技术问询：如何创建引用其他度量值的DAX度量值

预期可视化效果 #

以KPI为行、度量值为列的矩阵，展示不同维度的KPI计算结果

准备的数据 #

包含Cube、Division、Year、MTD Value等字段的KPI汇总数据表

问题核心 #

• 需要构建KPI为行、度量值为列的矩阵，但现有度量值无法匹配各KPI的差异化计算逻辑
• 目标是创建可复用已有KPI度量值的DAX逻辑，实现矩阵的动态适配

待引用的KPI度量值（LI MTD LY SOH Days DL MockUp） #

LI MTD LY SOH Days DL MockUp = 

var SOHDaysCPD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="SOH (days)",'DataLake KPI Summary'[Division]="Consumer Products",'DataLake KPI Summary'[Year]=2022
    )
var SOHDaysPPD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="SOH (days)",'DataLake KPI Summary'[Division]="Professional Products",'DataLake KPI Summary'[Year]=2022
    )
var SOHDaysLLD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="SOH (days)",'DataLake KPI Summary'[Division]="L'Oreal Luxe",'DataLake KPI Summary'[Year]=2022
    )
var SOHDaysACD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="SOH (days)",'DataLake KPI Summary'[Division]="Active Cosmetics",'DataLake KPI Summary'[Year]=2022
    )
var SOHValueLI = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="B2B SOH (M)",'DataLake KPI Summary'[Year]=2022
    )
var SOHValueCPD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="B2B SOH (M)",'DataLake KPI Summary'[Division]="Consumer Products",'DataLake KPI Summary'[Year]=2022
    )
var SOHValuePPD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="B2B SOH (M)",'DataLake KPI Summary'[Division]="Professional Products",'DataLake KPI Summary'[Year]=2022
    )
var SOHValueLLD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="B2B SOH (M)",'DataLake KPI Summary'[Division]="L'Oreal Luxe",'DataLake KPI Summary'[Year]=2022
    )
var SOHValueACD = CALCULATE(SUM('DataLake KPI Summary'[MTD Value]),ALL(Dim_Date[Date]),
        'DataLake KPI Summary'[Cube]="B2B SOH (M)",'DataLake KPI Summary'[Division]="Active Cosmetics",'DataLake KPI Summary'[Year]=2022
    )

var CPD = SOHValueCPD / SOHDaysCPD
var PPD = SOHValuePPD / SOHDaysPPD
var LLD = SOHValueLLD / SOHDaysLLD
var ACD = SOHValueACD / SOHDaysACD

return
    SOHValueLI/(CPD+PPD+LLD+ACD)

「Column」度量值优化方案 #

核心修改点 #

• 新增自定义KPI度量值的引用逻辑，匹配对应行维度时直接调用已定义的计算
• 用IN运算符简化重复的百分比KPI判断，提升代码可读性
• 修复原代码中重复的判断项，优化格式逻辑

修改后的代码 #

MTD LY DL MockUp = 
// 引用自定义KPI度量值
VAR CustomKPIValue = [LI MTD LY SOH Days DL MockUp]
// 常规MTD计算逻辑
VAR val = TOTALMTD(SUM('DataLake KPI Summary'[MTD Value]), DATEADD(Dim_Date[Date], -1, YEAR))
// 百分比类KPI的平均值计算
VAR val1 = TOTALMTD(AVERAGE('DataLake KPI Summary'[MTD Value]), DATEADD(Dim_Date[Date], -1, YEAR))
// 定义百分比类KPI集合
VAR PercentageKPIs = {
    "Service Rate (%)", "SI FCA (M-3) (%)", "SI FCA Bias (%)",
    "SO FCA (M-3) (%)", "SO FCA Bias (%)", "CDC Country IQ rate (%)",
    "Avg. Total Inventory / CNS (%)", "EOM. Credit/CNS (%)",
    "Overdue / total credit (%)", "Avg Overdue / total credit (%)"
}
// 判断当前行是否为自定义KPI
VAR IsCustomKPI = SELECTEDVALUE('DataLake KPI Summary'[Cube]) = "LI MTD LY SOH Days"

RETURN
    IF(IsCustomKPI, CustomKPIValue,
        IF(SELECTEDVALUE('DataLake KPI Summary'[Cube]) IN PercentageKPIs,
            FORMAT(val1, "0.0%"),
            SWITCH(TRUE(),
                val > 1000, FORMAT(val / 1000, "0.0B"),
                val
            )
        )
    )

额外优化建议 #

• 参数化年份：将硬编码的2022替换为YEAR(DATEADD(MAX(Dim_Date[Date]), -1, YEAR))，实现动态匹配去年年份
• 简化重复计算：针对按Division拆分的逻辑，可编写通用度量值，并用DIVIDE函数处理除零错误
• 矩阵配置：将'DataLake KPI Summary'[Cube]拖至矩阵行区域，优化后的MTD LY DL MockUp拖至值区域，即可实现预期可视化效果

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