使用DAX按属性计算NPS分数异常问题排查

问题背景 #

数据结构包含：

• ID：重复值（如1,1,2,3...）
• Attribute：4种属性（在线预订、机上WiFi服务、机内服务、清洁度）
• Value：评分值（1-5）
• Customer type：用户类型（首次用户/回头客）

需求是计算各属性的NPS分数，在堆叠条形图（按贬损者/被动者/推荐者分组）的工具提示中展示，但当前所有条形对应的NPS分数完全相同，不符合预期。

现有DAX代码：

Passives = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Value] = 3,
    Satisfaction[Customer type] = "First-time"
    )

Detractors = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Value] >= 1,
    Satisfaction[Value] <= 2,
    Satisfaction[Customer type] = "First-time"
 )

Promoters = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Value] >= 4,
    Satisfaction[Value] <= 5,
    Satisfaction[Customer type] = "First-time")

NPS by attribute Score = 
var CurrentAttribute = SELECTEDVALUE(Satisfaction[Attribute])

var TotalResponses = CALCULATE(
    COUNTROWS(Satisfaction),
    ALLEXCEPT(Satisfaction,
    Satisfaction[Attribute])
)
var PromotersCount = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Value] >= 4, 
    Satisfaction[Value] <= 5, 
    Satisfaction[Customer type] = "First-time", 
    ALLEXCEPT(Satisfaction,
    Satisfaction[Attribute]
))

var DetractorCount = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Value] >= 1,
    Satisfaction[Value] <= 2,
    Satisfaction[Customer type] = "First-time",
    ALLEXCEPT(Satisfaction,
    Satisfaction[Attribute]
    ))

var PromoterPercentage = DIVIDE(PromotersCount, TotalResponses, 0)
VAR DetractorPercentage = DIVIDE(DetractorCount, TotalResponses, 0)
RETURN
(PromoterPercentage - DetractorPercentage) * 100

问题根源 #

代码中使用ALLEXCEPT(Satisfaction, Satisfaction[Attribute])会清除当前筛选上下文里的所有筛选条件（包括条形对应的Value区间筛选，比如贬损者的Value>=1&&<=2），只保留Attribute的筛选。这导致不管你查看的是属性下的哪个分组条形，计算的都是该属性所有首次用户的全局NPS，因此所有条形的NPS分数完全一致。

解决方案 #

根据你的预期需求，提供三种修复方案：

方案1：每个条形工具提示显示属性整体NPS（合理业务逻辑） #

如果希望鼠标悬停在任意分组条形上时，都显示该属性的整体NPS，同时保留当前条形的筛选上下文，将ALLEXCEPT替换为ALLSELECTED(Satisfaction[Value])，仅移除Value区间的筛选：

NPS by attribute Score = 
VAR TotalResponses = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Customer type] = "First-time",
    ALLSELECTED(Satisfaction[Value]) -- 保留Attribute筛选，移除Value区间筛选
)
VAR PromotersCount = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Value] >= 4 && Satisfaction[Value] <= 5,
    Satisfaction[Customer type] = "First-time",
    ALLSELECTED(Satisfaction[Value])
)
VAR DetractorCount = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Value] >= 1 && Satisfaction[Value] <= 2,
    Satisfaction[Customer type] = "First-time",
    ALLSELECTED(Satisfaction[Value])
)
VAR PromoterPercentage = DIVIDE(PromotersCount, TotalResponses, 0)
VAR DetractorPercentage = DIVIDE(DetractorCount, TotalResponses, 0)
RETURN
(PromoterPercentage - DetractorPercentage) * 100

方案2：每个条形工具提示显示分组对应的NPS贡献值（满足分数差异预期） #

如果希望每个分组条形显示自身对属性NPS的贡献值（推荐者显示正占比，贬损者显示负占比，被动者显示0），使用以下代码：

NPS by Group Contribution = 
VAR TotalAttributeResponses = CALCULATE(
    COUNTROWS(Satisfaction),
    Satisfaction[Customer type] = "First-time",
    KEEPFILTERS(Satisfaction[Attribute]) -- 保留当前属性的筛选
)
VAR CurrentGroup = SWITCH(
    TRUE(),
    MAX(Satisfaction[Value]) >= 4 && MAX(Satisfaction[Value]) <= 5, "Promoter",
    MAX(Satisfaction[Value]) >= 1 && MAX(Satisfaction[Value]) <= 2, "Detractor",
    "Passive"
)
RETURN
SWITCH(
    CurrentGroup,
    "Promoter", DIVIDE(COUNTROWS(Satisfaction), TotalAttributeResponses, 0) * 100,
    "Detractor", -DIVIDE(COUNTROWS(Satisfaction), TotalAttributeResponses, 0) * 100,
    0
)

方案3：每个条形显示分组自身的NPS值（非标准定义，仅满足分数差异） #

如果仅需要每个分组条形显示不同的分数（不符合NPS标准定义），可以直接返回分组对应的固定值：

NPS by Group = 
SWITCH(
    TRUE(),
    MAX(Satisfaction[Value]) >= 4 && MAX(Satisfaction[Value]) <= 5, 100,
    MAX(Satisfaction[Value]) >= 1 && MAX(Satisfaction[Value]) <= 2, -100,
    0
)

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