在 Power BI 中使用 DAX 计算动态截尾均值:快速指南

为什么你需要截尾平均值

在数据分析中,标准的 AVERAGE 函数是一个主力工具,但它有一个显著的弱点:它非常容易受到异常值的扭曲影响。一个极端值,无论是高还是低,都可以扭曲整个结果,误导数据真正的中心趋势。

\ 这就是截尾平均值变得至关重要的地方。它通过从计算中排除指定百分比的最小值和最大值,提供了一个更稳健的平均值度量。

\ 虽然现代 Power BI 模型具有内置的 TRIMMEAN 函数,但当使用与较旧的 Analysis Services (SSAS) 模型的实时连接时,此函数通常不可用。本文提供了一个稳健的手动 DAX 模式,可以复制此功能并保持完全动态,响应报告中的所有切片器和筛选器。

动态截尾平均值的 DAX 解决方案

此度量值通过在对剩余 80% 进行平均之前删除底部 10% 和顶部 10% 的值来计算 20% 截尾平均值。

\ 你可以将此代码直接粘贴到"新建度量值"公式栏中。

Trimmed Mean (20%) = VAR TargetTable = 'FactTable' VAR TargetColumn = 'FactTable'[MeasureColumn] VAR LowerPercentile = 0.10 // Defines the bottom 10% to trim VAR UpperPercentile = 0.90 // Defines the top 10% to trim (1.0 - 0.10) // 1. Find the value at the 10th percentile VAR MinThreshold = PERCENTILEX.INC( FILTER( TargetTable, NOT( ISBLANK( TargetColumn ) ) ), TargetColumn, LowerPercentile ) // 2. Find the value at the 90th percentile VAR MaxThreshold = PERCENTILEX.INC( FILTER( TargetTable, NOT( ISBLANK( TargetColumn ) ) ), TargetColumn, UpperPercentile ) // 3. Calculate the average, including only values between the thresholds RETURN CALCULATE( AVERAGEX( FILTER( TargetTable, TargetColumn >= MinThreshold && TargetColumn <= MaxThreshold ), TargetColumn ) )

解构 DAX 逻辑

此公式分三个不同的步骤工作,所有步骤都在当前筛选上下文中执行(例如,用户选择的任何切片器)。

1. 定义关键变量

• TargetTable & TargetColumn:我们将表和列名称分配给变量以获得简洁、可重用的代码。你必须更改 'FactTable'[MeasureColumn] 以匹配你的数据模型。
• LowerPercentile / UpperPercentile:我们定义边界。0.10 和 0.90 意味着我们正在修剪底部 10% 和顶部 10%。要从每端修剪 5%(总共修剪 10%),你可以使用 0.05 和 0.95。

2. 查找百分位阈值

• MinThreshold & MaxThreshold:这些变量存储与我们的百分位边界对应的实际值。
• PERCENTILEX.INC:我们使用这个"迭代器"函数,因为它允许我们首先对表进行 FILTER。
• `FILTER(…, NOT(ISBLANK(…))):这是一个关键步骤。我们仅对目标列不为空白的行计算百分位数。这可以防止 BLANK() 值扭曲百分位计算。
• 结果是 MinThreshold 保存当前可见数据的第 10 百分位数的值(例如 4.5),MaxThreshold 保存第 90 百分位数的值(例如 88.2)。

3. 计算最终平均值

• RETURN CALCULATE(...):CALCULATE 函数是使度量值动态化的关键。它确保整个计算遵守报告中任何切片器或视觉对象应用的筛选器。
• AVERAGEX(FILTER(...)):计算的核心。我们使用 AVERAGEX 来迭代表。
• FILTER(...):我们最后一次筛选 TargetTable。此筛选器是"修剪"。它仅保留 TargetColumn 中的值符合以下条件的行:
• 大于或等于我们的 MinThreshold
• 并且
• 小于或等于我们的 MaxThreshold
• AVERAGEX(..., TargetColumn):AVERAGEX 然后仅对通过筛选的行计算 TargetColumn 的简单平均值。

结论

通过实施此 DAX 模式,你可以创建一个稳健、动态且抗异常值的 KPI。此度量值提供了数据中心趋势的更准确图像,并将在用户与 Power BI 报告交互时即时正确重新计算。

感谢你花时间与我一起探索数据相关的见解。我感谢你的参与。如果你觉得这些信息有帮助,我邀请你在 LinkedIn 或 X(@Luca_DataTeam) 上关注我或与我联系。探索愉快!👋