在PowerBI中基于计数聚合创建动态Z-score

需求说明 #

需创建DAX度量值，实现按日期（年月）+异常代码维度的条目计数，并根据切片器筛选（如「新/旧任务」）动态更新均值、标准差及Z值计算。

数据源表结构与示例数据 #

期望输出 #

无筛选时结果 #

筛选「NEW」任务时结果 #

DAX度量值实现 #

假设数据表名为异常任务表，请根据实际表名替换：

1. 条目计数 #

条目计数 = COUNTROWS('异常任务表')

• 作用：在当前筛选上下文下，统计对应日期+异常代码的条目数量，无匹配时自动返回0。

2. 分组均值 #

分组均值 = 
VAR 当前异常代码 = SELECTEDVALUE('异常任务表'[异常代码])
VAR 汇总表 = 
    SUMMARIZE(
        ALLSELECTED('异常任务表'),
        '异常任务表'[日期（格式：mmm yy）],
        '异常任务表'[异常代码],
        "@计数", [条目计数]
    )
VAR 筛选后汇总表 = FILTER(汇总表, [异常代码] = 当前异常代码)
RETURN
    AVERAGEX(筛选后汇总表, [@计数])

• 作用：计算当前异常代码在所有选中日期中的平均计数，ALLSELECTED()保留切片器筛选结果，确保均值随筛选动态调整。

3. 分组总体标准差 #

分组标准差 = 
VAR 当前异常代码 = SELECTEDVALUE('异常任务表'[异常代码])
VAR 汇总表 = 
    SUMMARIZE(
        ALLSELECTED('异常任务表'),
        '异常任务表'[日期（格式：mmm yy）],
        '异常任务表'[异常代码],
        "@计数", [条目计数]
    )
VAR 筛选后汇总表 = FILTER(汇总表, [异常代码] = 当前异常代码)
RETURN
    STDEVX.P(筛选后汇总表, [@计数])

• 作用：使用总体标准差STDEVX.P计算当前异常代码的计数波动，适配切片器筛选结果。

4. Z值计算 #

Z值 = 
VAR 当前计数 = [条目计数]
VAR 均值 = [分组均值]
VAR 标准差 = [分组标准差]
RETURN
    IF(标准差 = 0, 0, DIVIDE(当前计数 - 均值, 标准差))

• 作用：按公式(当前计数 - 均值)/标准差计算Z值，添加判断避免标准差为0时的除以0错误，结果可通过可视化设置保留两位小数。

使用步骤 #

1. 将上述度量值添加至PowerBI模型
2. 创建表格/矩阵视觉对象，行字段选择日期（格式：mmm yy）和异常代码，值字段依次添加条目计数、分组均值、分组标准差、Z值
3. 添加「新/旧任务」字段作为切片器，即可实现筛选时Z值相关指标的动态更新

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