"Неправильные" итоги в Power Pivot и BI

"Почему у меня в сводной таблице, построенной по данным из Power Pivot итоги считаются неправильно?"

"Как сделать так, чтобы Power BI показывал мне правильные итоги в таблице?"

"Это баг или фича? Когда это исправят?"

Эти вопросы я слышу от учеников почти в каждой моей группе по Power Pivot и Power BI, да и в почту они ко мне валятся тоже с завидной регулярностью. Давайте разберёмся, как, на самом деле, вычисляются в этих программах итоговые значения, почему иногда они могут казаться вам "неправильными" и можно ли на это как-то повлиять.

Сразу хочу уточнить, что всё описанное далее одинаково работает как в Power Pivot в Microsoft Excel (когда мы строим сводную по модели данных), так и в Power BI (когда мы делаем простую таблицу или матрицу на странице отчёта), так что совершенно не принципиально в какой именно программе это показывать. Давайте возьмем Power BI, т.к. интерфейс там поудобнее и всё будет более наглядно.

Предположим, что мы работаем с предельно простой моделью, состоящей из двух таблиц, загруженных в Power BI из внешнего Excel-файла: таблицы Sales с данными по продажам за 2020-21 гг. и календарной таблицы Dates.

Между собой эти таблицы связаны по полю даты:

Также я заранее сделал вспомогательную пустую и не связанную ни с чем таблицу Measures, в которую мы будем складывать меры.

Представим, что перед нами встала задача посчитать две ключевые бизнес-метрики: суммарную выручку и количество уникальных клиентов. Само собой, для этого мы создаём две простые меры, щёлкая правой кнопкой мыши по таблице Measures и выбирая команду Создать меру (New measure), а затем вводя её в строку формул:

Выручка всего = SUM ( Sales[Стоимость] )

Клиентов всего = DISTINCTCOUNT ( Sales[ID клиента] )

Теперь добавим на страницу нашего отчёта матрицу и закинем обе эти меры в область Значения (Values). В итоге должна получиться следующая картина:

Что означают эти числа?

В нашей таблице сейчас нет никаких полей в областях строк и столбцов, а на странице нет активных срезов или других визуализаций, в которых может быть что-то выделено - в терминах языка DAX наша ситуация сейчас называется "отсутствием контекста фильтра". По сути это означает, что обе наши меры сейчас считают свои результаты по абсолютно всем исходным данным, имеющимся в таблице Sales, т.е. за все годы мы работали с 400 клиентов и продали им товаров на 10 млн.

Теперь добавим в область строк нашей матрицы какие-нибудь поля, по которым нам интересно "нарезать" наши метрики - например, годы Dates[Год] и способ доставки из Sales[Доставка]:

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

И вот тут-то у многих пользователей и возникает когнитивный диссонанс. Итог по выручке выглядит вполне корректно и вопросов не вызывает - всё чётко складывается из своих составляющих за каждый год и способ доставки. А вот итоги по клиентам, наоборот, выглядят как-то странно и явно "не бьются".

На самом деле, если немного подумать, то всё логично и объяснимо. Если помните, мера выручки нас считается как простая сумма стоимостей всех сделок по таблице Sales. Какое бы поле мы не поместили в область строк (или столбцов) нашей матрицы - годы, месяцы, способы доставки и т.д. - итог по этой мере всегда будет корректно складываться из продаж по всем своим составляющим. Подобные меры в DAX ещё называют аддитивными (от английского «add» – сложение). Другие примеры аддитивных мер: количество заказов, общая прибыль, общие затраты и т.д.

А вот мера активных клиентов в нашей матрице вычисляется уже хитрее и использует функцию DISTINCTCOUNT для подсчёта уникального количества ID клиентов из таблицы Sales.  Многие клиенты покупали у нас и в 2020, и в 2021 году, но в строке итога, где нет контекста лет и отображаются все годы, каждый клиент считается однократно. То же самое и с доставкой - один и тот же клиент вполне мог заказывать у нас доставку своих покупок и на авто, и самолётом, и поездом. Но в строке итога по году, где уже в контексте фильтра нет доставки, а есть только год - все эти разные варианты доставки уже не учитываются.

Что логично и правильно.

Подобные меры в DAX называют, соответственно, неаддитивными – их итоги не считаются как сумма составляющих их значений. Другие примеры неаддитивных мер: процент выполнения плана, средний чек, остаток товара на конец месяца, максимальная цена и т. д.

Теперь, надеюсь, вы понимаете, что итоги в DAX считаются вполне корректно – с точки зрения вычислительного движка, по крайней мере. Другое дело, что для неискушённых бизнес-пользователей эта логика итога может быть не очень понятной. Многим людям (особенно после Excel) более привычно воспринимать итоги именно как сумму всех вышестоящих значений, а не как отсутствие фильтров.

На самом деле, если очень хочется, то вполне можно вмешаться в логику расчёта итогов для любых мер и перекроить её в соответствии с требованиями заказчика или бизнес-логики. В частности, достаточно легко сделать так, чтобы итоги по неаддитивной мере выглядели как аддитивные.

Рассмотрим следующий пример. Предположим, что нам необходимо посчитать среднюю дневную выручку, что легко реализуется следующей мерой:

Выручка в день = AVERAGEX ( Dates, [Выручка всего] )

Здесь мы с помощью функции-итератора AVERAGEX проходим построчно день за днём по календарной таблице Dates и вычисляем меру суммарной выручки для каждого дня, после чего все полученные результаты усредняем. После помещения созданной меры в область Значений нашей таблицы мы увидим следующую картину:

Вполне очевидно, что эта мера неаддитивная – итоги по году представляют из себя среднедневную выручку по всем дням года (когда мы работали) без учёта способов доставки.

Предположим, что заказчик этого отчёта всё же хотел бы видеть в итогах по годам и в общем итоге внизу таблицы именно среднее входящих в них видов доставки. Чтобы такое реализовать, нам потребуется мера похитрее:

Здесь сначала функция SUMMARIZE формирует виртуальную таблицу того же вида, что имеем сейчас в отчёте - с годами и типами доставки внутри каждого года:  

Затем мы проходим по ней построчно с помощью функции-итератора AVERAGEX, в каждой строке для каждой пары год-доставка считаем меру средней дневной выручки, а затем это всё усредняем, как и требовал наш заказчик. Само собой, вместо усреднения можно было складывать (тогда нужна была бы функция SUMX вместо AVERAGEX), считать количество (COUNTX) и т.д. 

Таким образом, мы переопределяем логику расчёта "неправильных" итогов нужным нам образом.

Ссылки по теме

• Основы работы в Microsoft Power BI
• План-факт анализ в сводной таблице с Power Pivot и Power Query
• Скользящее среднее в Microsoft Power BI