如何在PowerPivot中计算每月各星期几的唯一ID平均出现次数？

问题说明 #

需要构建数据透视表：

• 行维度：星期几（周一至周日）
• 列维度：年月组合（如2024-11）
• 值：每月对应星期几的唯一ID统计（根据需求分为两种场景）

数据集包含Date、Year_Month、Day_of_Weak、ID字段，示例数据及期望结果如下：

示例数据 #

期望结果（场景1：唯一ID总数） #

期望结果（场景2：每日唯一ID平均值） #

原有度量的问题 #

你提供的DAX度量存在以下问题：

• 透视表的行和列已经限定了Day_of_Weak和Year_Month的上下文，无需额外用MAX函数筛选，冗余的筛选逻辑会导致计算范围混乱
• AVERAGEX迭代的是原始表的逐行数据，而非按日期分组后的统计行，导致DISTINCTCOUNT计算的是整个筛选范围内的总唯一ID，而非每日的唯一ID数，最终结果不符合预期

正确DAX实现 #

场景1：计算每月对应星期几的唯一ID总数 #

直接利用透视表的上下文，计算当前Day_of_Weak和Year_Month范围内的唯一ID数量：

Unique IDs Total = 
DISTINCTCOUNT('Raport_wszystkich_wejsc_JJ'[ID])

场景2：计算每月对应星期几的每日唯一ID平均值 #

先按日期分组计算每日的唯一ID数，再对这些数值取平均：

Avg Daily Unique IDs = 
VAR DailyUniqueCounts = 
    SUMMARIZE(
        'Raport_wszystkich_wejsc_JJ',
        'Raport_wszystkich_wejsc_JJ'[Date],
        "DailyUnique", DISTINCTCOUNT('Raport_wszystkich_wejsc_JJ'[ID])
    )
RETURN
    IF(
        COUNTROWS(DailyUniqueCounts) = 0,
        BLANK(),
        AVERAGEX(DailyUniqueCounts, [DailyUnique])
    )

度量解释 #

• 场景1：透视表的行和列已经锁定了Day_of_Weak和Year_Month的范围，DISTINCTCOUNT直接计算该范围内的唯一ID总数，完全匹配你示例中的期望结果。
• 场景2：
  1. 使用SUMMARIZE按Date分组，生成包含每日唯一ID数的临时表DailyUniqueCounts
  2. AVERAGEX迭代临时表，计算每日唯一ID数的平均值
  3. 加入IF判断，避免无数据时显示错误值，返回空值（对应N/A）

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