Obliczanie dynamicznej średniej obciętej w Power BI przy użyciu DAX: Szybki przewodnik

Dlaczego potrzebujesz średniej obciętej

W analizie danych standardowa funkcja AVERAGE jest narzędziem podstawowym, ale ma istotną słabość: jest bardzo podatna na zniekształcenia wynikające z wartości odstających. Pojedyncza wartość ekstremalna, czy to wysoka, czy niska, może przekrzywić cały wynik, błędnie przedstawiając prawdziwą tendencję centralną danych.

\ W tym miejscu średnia obcięta staje się niezbędna. Zapewnia ona bardziej solidny pomiar średniej poprzez wykluczenie określonego procentu najmniejszych i największych wartości z obliczeń.

\ Podczas gdy nowoczesne modele Power BI mają wbudowaną funkcję TRIMMEAN, funkcja ta często nie jest dostępna podczas korzystania z połączenia na żywo ze starszym modelem Analysis Services (SSAS). Ten artykuł przedstawia solidny, ręczny wzorzec DAX, który replikuje tę funkcjonalność i pozostaje w pełni dynamiczny, reagując na wszystkie fragmentatory i filtry w Twoim raporcie.

Rozwiązanie DAX dla dynamicznej średniej obciętej

Ta miara oblicza 20% średnią obciętą poprzez usunięcie dolnych 10% i górnych 10% wartości przed uśrednieniem pozostałych 80%.

\ Możesz wkleić ten kod bezpośrednio do paska formuły "Nowa miara".

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 ) )

Dekonstrukcja logiki DAX

Ta formuła działa w trzech odrębnych krokach, z których wszystkie są wykonywane w bieżącym kontekście filtra (np. niezależnie od fragmentatorów wybranych przez użytkownika).

1. Zdefiniuj kluczowe zmienne

• TargetTable & TargetColumn: Przypisujemy nazwy tabeli i kolumny do zmiennych dla czystego, wielokrotnego użytku kodu. Musisz zmienić 'FactTable'[MeasureColumn], aby dopasować do swojego modelu danych.
• LowerPercentile / UpperPercentile: Definiujemy granice. 0.10 i 0.90 oznaczają, że obcinamy dolne 10% i górne 10%. Aby obciąć 5% z każdego końca (łącznie 10% obcięcia), użyłbyś 0.05 i 0.95.

2. Znajdź progi percentylowe

• MinThreshold & MaxThreshold: Te zmienne przechowują rzeczywiste wartości, które odpowiadają naszym granicom percentylowym.
• PERCENTILEX.INC: Używamy tej funkcji "iteratora", ponieważ pozwala nam ona najpierw przefiltrować tabelę.
• `FILTER(…, NOT(ISBLANK(…))): To jest kluczowy krok. Obliczamy percentyle tylko dla wierszy, w których nasza kolumna docelowa nie jest pusta. Zapobiega to zniekształceniu obliczeń percentylowych przez wartości BLANK().
• Wynikiem jest to, że MinThreshold przechowuje wartość 10. percentyla (np. 4.5), a MaxThreshold przechowuje wartość 90. percentyla (np. 88.2) dla aktualnie widocznych danych.

3. Oblicz końcową średnią

• RETURN CALCULATE(...): Funkcja CALCULATE jest kluczem do uczynienia miary dynamiczną. Zapewnia ona, że całe obliczenie respektuje filtry zastosowane przez dowolne fragmentatory lub wizualizacje w raporcie.
• AVERAGEX(FILTER(...)): Rdzeń obliczeń. Używamy AVERAGEX do iteracji po tabeli.
• FILTER(...): Filtrujemy naszą TargetTable ostatni raz. Ten filtr to "obcięcie". Zachowuje tylko te wiersze, w których wartość w TargetColumn jest:
• Większa lub równa naszemu MinThreshold
• ORAZ
• Mniejsza lub równa naszemu MaxThreshold
• AVERAGEX(..., TargetColumn): AVERAGEX następnie oblicza prostą średnią TargetColumn tylko dla wierszy, które przeszły filtr.

Podsumowanie

Wdrażając ten wzorzec DAX, tworzysz solidny, dynamiczny i odporny na wartości odstające wskaźnik KPI. Ta miara zapewnia dokładniejszy obraz tendencji centralnej Twoich danych i będzie poprawnie przeliczana na bieżąco, gdy użytkownicy wchodzą w interakcję z Twoim raportem Power BI.

---

Dziękuję za poświęcenie czasu na eksplorację spostrzeżeń związanych z danymi ze mną. Doceniam Twoje zaangażowanie. Jeśli uznasz te informacje za pomocne, zapraszam do śledzenia mnie lub nawiązania kontaktu ze mną na LinkedIn lub X(@Luca_DataTeam). Miłej eksploracji!👋