在Power BI中计算各客户各时段Top10数值的平均值

问题背景 #

原始数据集 #

Time    Date        ID        Value
12 am   1st Oct     1001      100
12 am   2nd Oct     1001      203
12 am   3rd Oct     1001      403
....    ......      ....      ....
11 pm   20th Oct    1001      566
12 am   1st Oct     1002      150
12 am   2nd Oct     1002      153
12 am   3rd Oct     1002      403
....    ......      ....      ....
11 pm   10th Oct    1002      666

每个客户（ID）的每个时段（Time）都包含20天的数值数据，需要计算每个客户各时段下，20天数据中Top10最高数值的平均值。

预期输出 #

Time  ID     Average
12am  1001   105
1am   1001   056
...   ....   ...
11pm  1001   298
12am  1002   456
1am   1002   856
...    ...   ...
11pm  1002   166

实现步骤 #

1. 数据预处理（可选） #

如果原始数据的Time字段包含空格（如"12 am"），建议先在Power Query编辑器中清洗格式：

• 选中Time列，使用替换值功能，将空格替换为空，统一为"12am"格式，避免分组时出现不一致。

2. 创建DAX度量值 #

在Power BI的建模选项卡中，新建度量值，输入以下DAX代码（将'数据表'替换为你的实际表名）：

Top10平均值 = 
VAR 当前分组 = ALLSELECTED('数据表'[Time], '数据表'[ID])
VAR 分组Top10数据 = CALCULATETABLE(
    TOPN(10, '数据表', '数据表'[Value], DESC),
    当前分组
)
RETURN
AVERAGEX(分组Top10数据, '数据表'[Value])

代码说明：

• 当前分组：锁定当前筛选上下文的Time和ID组合，确保只计算该分组内的数据
• 分组Top10数据：筛选出该分组下Value最大的前10条记录
• AVERAGEX：对筛选出的Top10记录，计算Value的平均值

3. 构建可视化报表 #

• 从字段面板中，将Time和ID拖入报表的行区域
• 将新建的Top10平均值拖入值区域
• 按需调整格式：比如设置平均值的小数位数、数字格式等

特殊情况处理（可选） #

如果部分Time+ID组合下的数据不足10条，上述度量值会计算现有所有数据的平均值。若要针对这种情况返回特定值（如空白），可以修改DAX代码：

Top10平均值 = 
VAR 当前分组 = ALLSELECTED('数据表'[Time], '数据表'[ID])
VAR 分组数据量 = CALCULATE(COUNTROWS('数据表'), 当前分组)
VAR 分组Top10数据 = CALCULATETABLE(
    TOPN(10, '数据表', '数据表'[Value], DESC),
    当前分组
)
RETURN
IF(分组数据量 >=10, AVERAGEX(分组Top10数据, '数据表'[Value]), BLANK())

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