Достаточно часто при написании формул в Power BI возникает потребность перевода данных на более высокий уровень детализации, на котором уже и должны производиться дополнительные вычисления. Говоря более простым языком, в этой статье мы разберёмся как сгруппировать данные с помощью DAX по аналогии с классическим GROUP BY из SQL.
К счастью, в DAX есть несколько выражений, которые отлично с этим справляются.
Итак, давайте разберёмся с этой темой на примере следующего сценария. У нас есть таблица с продажами «Sales», где записаны отдельные транзакции с детализацией до конкретной товарной позиции. И мы хотим анализировать средний чек в динамике или по разным категориям товаров/клиентов. Но мы хотим анализировать на уровне чека (корзины покупок для интернет-магазинов), а не на уровне конкретного товара. Таким образом, обычный AVERAGE здесь работать не будет. То есть наша задача состоит в том, чтобы сгруппировать продажи до уровня чека, и только потом рассчитать среднее значение.
Это очень распространенный сценарий, который достаточно легко реализовать в Power BI с помощью DAX. На самом деле существует даже несколько способов сделать это!
Подход №1: Общая сумма продаж / Уникальные чеки
=SUM(Sales[SalesAmount] )
/ DISTINCTCOUNT(Sales[OrderNumber] )
Это, наверное, самый простой вариант решения нашего сценария. Правда в этом подходе фактически ничего и не группируется. Для расчёта среднего чека этого достаточно, но что если нам нужно вычислить максимальную сумму чека? Или среднюю стоимость товарной позиции в чеках с суммой от 50 до 100$? Тут уже без группировки/агрегирования не обойтись, и нам в это помогут двухэтапные формулы, описанные в следующих двух способах.
Подход №2: AggX(VALUES)
На первом этапе мы группируем таблицу на уровне чеков и суммируем продажи, а на втором этапе вычисляем среднее значение по всем чекам.
=AVERAGEX (
VALUES ( Sales[OrderNumber] );
CALCULATE ( SUM ( Sales[SalesAmount] ) )
)
В этой формуле есть три ключевые части:
- Функция VALUES ведет себя как подзапрос и возвращает список уникальных номеров чеков (OrderNumbers), по которым мы и хотим сгруппировать таблицу.
- CALCULATE(SUM ()) определяет как мы будем группировать суммы продаж (SalesAmount) до уровня чека – используем суммирование.
- AVERAGEX — это внешняя функция, которая по каждому из элементов в списке уникальных номеров чеков рассчитывает CALCULATE(SUM()), тем самым сворачивая SalesAmount на уровне чека. И затем вычисляется среднее этих значений.
Подход №3: AggX(SUMMARIZE)
По сути, этот подход выполняет то же самое, что и описанный выше подход №2. Но вместо того, чтобы использовать выражение VALUES для получения списка уникальных чеков (OrderNumbers), мы будем использовать SUMMARIZE для группировки.
Функция SUMMARIZE работает следующим образом:
SUMMARIZE(<table>; <groupBy_columnName>[; <groupBy_columnName>]…[; <name>; <expression>]…)
Первый параметр — таблица, которую мы хотим сгруппировать (Sales).
Второй параметр — столбец для группировки (Sales [OrderNumber]). При желании можно группировать и по дополнительным столбцам.
Именно благодаря последним двум параметрам функция SUMMARIZE обладает дополнительной гибкостью, которая может пригодиться, когда нужно больше чем просто список значений из одного столбца. Давайте рассмотрим наш пример, вот формула:
=AVERAGEX (
SUMMARIZE ( Sales; Sales[OrderNumber] );
CALCULATE ( SUM ( Sales[SalesAmount] ) )
)
В этом выражении внутренний SUMMARIZE работает над таблицей Sales и группирует её по OrderNumber. Результатом является список уникальных OrderNumbers, который нам и нужен (как и VALUES). С этим списком внешний AVERAGEX построчно вычисляет сумму продаж SalesAmounts, а затем рассчитывает среднее значение по всем суммам на уровне чеков.
Другой вариант использования SUMMARIZE может быть полезен в самых разных ситуациях, он заключается в том, чтобы выполнять агрегирование значений при группировке и передавать во внешнюю функцию уже целую таблицу (со столбцами группировки и агрегированными значениями). Таким образом, в нашем сценарии мы можем фактически переместить CALCULATE (SUM ()), который сворачивает продажи с уровня товарных позиций до уровня чека, внутрь SUMMARIZE:
=AVERAGEX (
SUMMARIZE (
Sales;
Sales[OrderNumber];
«SumByOrder»; CALCULATE ( SUM ( Sales[SalesAmount] ) )
);
[SumByOrder]
)
При этом внутренний SUMMARIZE ведет себя как подзапрос SQL, который возвращает не только список чеков (OrderNumbers), но и сагрегированный SalesAmounts для каждого чека. Затем внешний AVERAGEX уже с этой таблицей из 2-х столбцов построчно подбирает значения для SumByOrder, а затем вычисляет среднее.
Всё достаточно прямолинейно. Единственный нюанс в том, что в рамках SUMMARIZE мы присваиваем агрегированному выражению «псевдоним» (в нашем случае «SumByOrder»). Таким образом, таблица, в которую возвращается SUMMARIZE, включает этот столбец, и вы можете ссылаться на него по имени во внешней функции AggX.