Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):


Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы продолжим изучать DAX функции, создающие в Power BI и Power Pivot сводные таблицы, а конкретно, это GROUPBY и SUMMARIZECOLUMNS.

Напомню, что в предыдущей статье мы уже рассматривали лидера по формированию сводных таблиц — функцию SUMMARIZE, ну а сейчас, как я уже написал выше, мы рассмотрим еще пару инструментов в этой теме.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

 

DAX функция GROUPBY в Power BI и Power Pivot

GROUPBY () — создает сводную таблицу, сгруппированную по указанным столбцам (название столбцов конфигурируется из названия самой исходной таблицы и названия исходного столбца для группировки).

Также, часто я встречаю раздельное написание этой функции, как DAX GROUP BY, что неправильно…

Синтаксис:

GROUPBY (
    'Таблица';
    [Столбец 1]; [Столбец 2]; ...; [Столбец N];
    "Имя столбца 1"; Выражение 1;
    "Имя столбца 2"; Выражение 2;
    "..."; ...;
    "Имя столбца N"; Выражение N
)

Где:

  • ‘Таблица’ — исходная существующая таблица или табличное выражение, значения которых мы хотим сгруппировать
  • [Столбец] — столбец для группировки
  • «Имя столбца» — имя создаваемого столбца для значений группировки
  • Выражение — вычисляемое выражение для значений группировки

Выражение обязательно должно содержать статистическую DAX функцию формата X (SUMX, MAXX, AVERAGEX…), во входных параметрах которой, в качестве таблицы подставляется служебное выражение CURRENTGROUP ().

Давайте разберем все параметры GROUPBY, в том числе и служебное выражение CURRENTGROUP на примере формулы.

Для разбора примера создадим в модели данных вычисляемую таблицу по формуле с участием GROUPBY.

Так как в Excel (Power Pivot) в модели данных создавать вычисляемые таблицы нельзя, то пример будем рассматривать на основе Power BI — в ней можно создавать физические вычисляемые таблицы. А в Excel вычисляемые таблицы создаются только виртуально, во время вычисления самих формул.

Итак, в Power BI Desktop имеется исходная таблица «Продажи Менеджеров»:

Исходная таблица

 

Создадим во вкладке «Моделирование» вычисляемую таблицу на основе формулы с участием DAX функции GROUPBY:

Сводные Продажи По Менеджерам = 
GROUPBY (
    'ПродажиМенеджеров';
    'ПродажиМенеджеров'[Отдел]; 'ПродажиМенеджеров'[Менеджер];
    "Продажи";
    SUMX (
        CURRENTGROUP ();
        'ПродажиМенеджеров'[Продажи]
    )
)

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

Во втором и третьем параметрах (вторая строка параметров) мы прописали столбцы [Отдел] и [Менеджер]. Именно по ним и будет происходить группировка всех значений.

В четвертом параметре (третья строка параметров) мы прописали название нового столбца, в котором расположатся агрегированные значения в создаваемой сводной таблице.

В пятом параметре, согласно синтаксису GROUPBY, расположилась вложенная функция SUMX на основе которой, будет рассчитываться агрегированная сумма всех продаж по группам. В качестве входящей таблицы в SUMX указана служебное выражение CURRENTGROUP.

Итак, результатом выполнения формулы на основе DAX функции GROUPBY будет следующая сводная таблица:

Результат выполнения формулы на основе DAX функции GROUPBY

 

В этой созданной таблице при помощи GROUPBY нам удалось собрать все продажи воедино по каждому менеджеру каждого отдела в организации.

Теперь, рассмотрим еще одну похожую функцию по формированию сводных таблиц.

 

DAX функция SUMMARIZECOLUMNS в Power BI и Power Pivot

SUMMARIZECOLUMNS () — очень похожая DAX функция на GROUPBY, а тем более на SUMMARIZE. Она также создает сводную таблицу, но, в данном случае, с возможностью фильтрации группируемых столбцов.

Синтаксис:

SUMMARIZECOLUMNS (
    [Столбец 1]; [Столбец 2]; ...; [Столбец N];
    Фильтр;
    "Имя Столбца 1"; Выражение 1;
    "Имя Столбца 1"; Выражение 1;
    "..."; ...;
    "Имя Столбца N"; Выражение N
)

Синтаксис функции SUMMARIZECOLUMNS очень похож на синтаксис GROUPBY, который мы рассматривали выше, за исключением лишь той разницы, что:

  • в первом параметре не нужно прописывать исходную таблицу;
  • в выражении мы можем использовать не только X функции, но и любые другие функции агрегирования
  • здесь появился еще один параметр — фильтр, по которому производится фильтрация столбцов для группировки

Рассмотрим пример формулы на основе DAX функции SUMMARIZECOLUMNS. Как и выше, пример мы будем рассматривать в Power BI на основе все той же исходной таблицы «Продажи Менеджеров»:

Исходная таблица

 

Создадим в Power BI Desktop во вкладке «Моделирование» новую вычисляемую таблицу и пропишем там следующую формулу с участием функции SUMMARIZECOLUMNS:

СводныеПродажиПоМенеджерам = 
SUMMARIZECOLUMNS (
    'ПродажиМенеджеров'[Отдел]; 'ПродажиМенеджеров'[Менеджер];
    FILTER ('ПродажиМенеджеров'; 'ПродажиМенеджеров'[Менеджер] <> "Петров");
    "Продажи";
    SUM ('ПродажиМенеджеров'[Продажи])
)

В первой строке мы прописали столбцы [Отдел] и [Менеджер], по которым будет происходить группировка значений.

Во второй строке прописали фильтр, созданный на основе DAX функции FILTER. Данная функция фильтрует исходную таблицу «Продажи Менеджеров» по столбцу [Менеджер], где его значения не должны быть равны значению «Петров».

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

В четвертой строке само выражение агрегации на основе функции SUM, которая сложит все продажи по категориям группировки.

Итак, результатом выполнения формулы выше на основе работы DAX функции SUMMARIZECOLUMNS будет следующая сводная таблица:

Результат выполнения формулы на основе работы DAX функции SUMMARIZECOLUMNS

 

Как мы видим, создалась сводная таблица по продажам менеджеров, но без менеджера Петров, так как его мы отфильтровали в самой формуле SUMMARIZECOLUMNS.

Итак, на этом, с разбором DAX функций, создающих сводные таблицы в Power BI и Power Pivot, все. Успехов Вам, друзья!

 

Антон БудуевС уважением, Будуев Антон.
Проект «BI — это просто»

 

 

 

Присоединяйтесь к нашим социальным сетям

Именно в них оперативно и каждый день Вам будут доступны наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи 

по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel...)

Наша группа Вконтакте Мы в Инстаграме Наша группа в Фейсбук Наш YouTube канал
Наша группа VK
Подписаться на наш YouTube канал

 

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

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

 

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D


Вам это может быть интересно. Другие статьи нашего обучающего портала:


Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

* Заполняя форму отправки комментария своими персональными данными (имя, email, сайт и др.), Вы автоматически подтверждаете свое согласие на обработку своих персональных данных