Расчёт динамического усечённого среднего в Power BI с использованием DAX: краткое руководство

Зачем вам нужно усеченное среднее

В анализе данных стандартная функция AVERAGE является основным инструментом, но у нее есть существенный недостаток: она очень восприимчива к искажениям из-за выбросов. Одно экстремальное значение, высокое или низкое, может исказить весь результат, неправильно представляя истинную центральную тенденцию данных.

\ Именно здесь усеченное среднее становится необходимым. Оно обеспечивает более надежную меру среднего значения, исключая из расчета определенный процент наименьших и наибольших значений.

\ В то время как современные модели Power BI имеют встроенную функцию TRIMMEAN, эта функция часто недоступна при использовании Live Connection к более старой модели Analysis Services (SSAS). Эта статья предоставляет надежный ручной шаблон DAX, который воспроизводит эту функциональность и остается полностью динамичным, реагируя на все срезы и фильтры в вашем отчете.

Решение DAX для динамического усеченного среднего

Эта мера вычисляет 20% усеченное среднее, удаляя нижние 10% и верхние 10% значений перед усреднением оставшихся 80%.

\ Вы можете вставить этот код непосредственно в строку формулы «Новая мера».

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). Приятного изучения!👋