在 Power BI 中使用 DAX 計算動態截尾平均值:快速指南

為什麼需要截尾平均值

在數據分析中,標準的 AVERAGE 函數是工作主力,但它有一個顯著的弱點:它極易受到離群值的扭曲影響。單個極端值,無論是高還是低,都可能使整個結果偏斜,誤導數據的真實集中趨勢。

\ 這就是截尾平均值變得至關重要的地方。它透過從計算中排除指定百分比的最小值和最大值,提供了更穩健的平均值衡量標準。

\ 雖然現代 Power BI 模型內建了 TRIMMEAN 函數,但在使用即時連線連接到較舊的 Analysis Services (SSAS) 模型時,此函數通常無法使用。本文提供了一個穩健的手動 DAX 模式,可複製此功能,並保持完全動態,響應報表中的所有交叉分析篩選器和篩選器。

動態截尾平均值的 DAX 解決方案

此量值透過移除最低 10% 和最高 10% 的值,然後對剩餘 80% 取平均值,來計算 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(…, 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) 上關注我或與我聯繫。祝您探索愉快!👋