合并DAX度量值Absenteeism与BLR Absent后结果异常咨询

原DAX度量值 #

1. Absenteeism度量值 #

Absenteeism = 
VAR OrgList = {
    "Attrition - BLR",
    "BLR - EY Domestic EYG",
    "BLR - EY Domestic EYG V2",
    "BLR - EY Domestic Res",
    "BLR - EY Domestic Res V2",
    "BLR – EY English Gold Platinum V2",
    "BLR - EY International EYG",
    "BLR - EY International EYG V2",
    "BLR - EY International Res",
    "BLR - EY International Res V2",
    "BLR - EY New Joiner",
    "BLR - EY OJT V2",
    "CSI - EY BLR",
    "CSI - EY BLR V2",
    "E-Services - EY BLR",
    "E-Services - EY BLR V2",
    "Etihad - BLR",
    "Etihad - BLR V2",
    "GSS - EY BLR",
    "GSS - EY BLR V2",
    "Quality - BLR V2",
    "SME - EY BLR V2",
    "WFM - EY BLR RTA",
    "WFM - EY BLR RTA V2",
    "1. Other Support - BLR V2"
}

VAR FilterTable =
    FILTER('ActivityHours_Raw', NOT('ActivityHours_Raw'[Organization] IN OrgList))

VAR Globalab =
    CALCULATE(
        IF(
            [Unplanned Leave] = 0,
            0,
            [Unplanned Leave] / ([Paid Hours] - [Planned Leave])
        ),
        FilterTable
    )
Return
Globalab

2. BLR Absent度量值 #

BLR Absent = 
VAR Leave_categories = {"Sick", "Absent Informed ", "Absent Not Informed", "Emergency Leave", "Unpaid Leave", "Sick Paid", "Absent Informed", "Sick Unpaid", "General Unavailability", "NCNS"}
VAR Leave_cal = CALCULATE(COUNT('BLR Attendance'[Attended ]), FILTER('BLR Attendance', 'BLR Attendance'[Attended ] IN Leave_categories || ('BLR Attendance'[Attended ] = "General Unavailability" && 'BLR Attendance'[Date] >= DATE(2023,6,1))))
VAR Agent_count = COUNT('BLR Attendance'[Name])
RETURN
    Leave_cal / Agent_count

问题描述 #

将两个度量值合并为Globalab + BLR Absent后，在矩阵表中得到的结果为13%，但预期返回值应为6%左右的平均值。

问题原因 #

直接相加两个度量值的逻辑存在错误：

• Absenteeism是基于工时比例计算的缺勤率（未计划缺勤工时 ÷ 有效工时）
• BLR Absent是基于人数比例计算的缺勤计数（缺勤记录数 ÷ 员工总数）
  两者统计维度完全不同，简单求和只是将两个独立的比例直接叠加，并非按照业务逻辑计算整体平均值。

修正方案 #

根据实际业务需求选择合适的合并方式：

方案1：加权平均（按员工数或工时权重） #

如果需要计算整体的加权缺勤率，需将两个数据集的缺勤总量除以总基数，而非直接相加比例。以员工数为权重的示例：

Combined Absenteeism = 
VAR GlobalAbsValue = [Absenteeism]
VAR BLRAbsValue = [BLR Absent]
-- 获取两个数据集的员工数作为权重
VAR GlobalEmpCount = CALCULATE(COUNT('ActivityHours_Raw'[EmployeeID]), FilterTable)
VAR BLREmpCount = COUNT('BLR Attendance'[Name])
VAR TotalEmp = GlobalEmpCount + BLREmpCount
RETURN
    IF(TotalEmp = 0, 0, (GlobalAbsValue * GlobalEmpCount + BLRAbsValue * BLREmpCount) / TotalEmp)

方案2：统一统计口径 #

调整其中一个度量值的计算逻辑，使其与另一个保持口径一致。例如，将BLR Absent改为基于工时的缺勤率，或把Absenteeism改为基于人数的比例，再进行合并计算。

方案3：简单平均值（仅适用于两个数据集权重相近的场景） #

如果两个部分的样本量差异不大，可直接取两个比例的平均值：

Combined Absenteeism = 
VAR GlobalAbsValue = [Absenteeism]
VAR BLRAbsValue = [BLR Absent]
RETURN
    (GlobalAbsValue + BLRAbsValue) / 2

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