Бизнес-анализ на DAX

Power BI Desktop имеет модульную структуру. В него интегрировано серверное ядро SSAS, поддерживающее табличную модель данных и DAX-запросы (DAX – аббревиатура от Data Analysis Expressions). При загрузке Power BI Desktop запускается PowerPivot –модуль обработки данных в оперативной памяти, который выполняет работу по обслуживанию DAX-запросов, распределению нагрузки, управлению памятью и передачу результатов рендеру, отображающему результаты в виде графиков, гистограмм, сводных таблиц и других визуальных элементов Power BI.

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

Если аналитику требуется вычислить экономические показатели на различных срезах данных, с группировкой по разным столбцам и/или по группам столбцов, в DAX это сделать очень просто. Для этого в меню «Моделирование» необходимо создать новую таблицу и записать следующее выражение, синтаксис которого содержит:

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

В результате выполнения этого выражения сформируется новая таблица.

Язык DAX — понятие, для чего, для кого, какие задачи решает

DAX( Data Analysis Expressions) — это язык функций и формул, который входит в состав нескольких приложений от компании Microsoft, а если быть точнее, то в приложение Power BI, в надстройку Excel (Powerpivot) и в SSAS Tabular (SQL Server).

MS DAX — это функциональный язык запросов, который целиком и полностью состоит только из встроенных в него функций. В DAX вы не сможете создать какие-либо свои функции, циклы или что-то еще, что можно создавать в языках программирования. Ведь DAX — это не язык программирования, это именно функциональный язык от Microsoft, при помощи которого, у вас имеется возможность на основе встроенных в него функций создавать свои формулы для расчета необходимых вычислений, обогащая вашу исходную модель данных. И на основе всего этого, в итоге, создавать те самые интерактивные отчеты в Power BI и Excel (Powerpivot).

Если говорить кратко, то DAX позволяет обращаться (создавать запросы) к исходной модели данных, брать из нее необходимые отфильтрованные данные и производить с ними вычисления. А затем, уже на основе этих вычислений, у Вас появляется возможность создавать интерактивные BI отчеты.

Язык DAX и Excel (Powerpivot)

Тем из вас, кто ранее пользовался обычным Excel, а сейчас переходит к работе с языком DAX, то для вас DAX может показаться достаточно знакомым и похожим на Excel. И это действительно так. Дело в том, что язык DAX изначально зародился из под Excel, а конкретнее, в 2010 году его представили на свет в рамках языка функций и формул для надстройки Excel — Power Pivot. И создатели DAX сделали все возможное, чтобы пользователям стандартного Экселя было легко перейти к работе с DAX. В DAX достаточно много функций, которые так или иначе похожи, или чем-то напоминают соответствующие функции в Excel.

Язык DAX и Power BI

Также, есть небольшое различие в языках DAX используемых в разных приложениях, например таких, как Power BI и Excel (Powerpivot). А конкретно, в DAX в Power BI имеется возможность создавать физически и сохранять в модели данных вычисляемые таблицы, чего в Excel (Powerpivot) по крайней мере, сейчас, пока сделать не получится. В Excel можно создавать некие виртуальные таблицы в составе самих формул DAX, но создать физически вычисляемые таблицы и потом сохранить их в модели данных нельзя. Поэтому, если вы только начинаете изучать язык DAX, то лучше всего, по моему мнению, это делать в приложении Power BI: во-первых, в Power BI в DAX можно легко создавать в модели данных вычисляемые таблицы; а во-вторых, само удобство использования DAX в Power BI намного лучше, ведь Power BI — это следующий технологический уровень в BI аналитике, после Excel, и именно поэтому, там процесс работы намного удобнее.

Где нужен DAX

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

Рассмотрим основные атрибуты по порядку:
1. Меры

Меры — это формулы динамических вычислений, результаты которых меняются в зависимости от контекста.

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

2. Вычисляемый столбец

Вычисляемый столбец — это столбец, который Вы добавляете в существующую таблицу (в конструкторе моделей), а затем создаете формулу DAX, которая определяет значения столбца. Если вычисляемый столбец содержит действительную формулу DAX, значения вычисляются для каждой строки сразу после ввода формулы. Затем значения сохраняются в модели данных в памяти.

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

3. Вычисляемая таблица

Вычисляемая таблица — это вычисляемый объект, основанный на выражении формулы, полученном из всех или части других таблиц в той же модели.

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

Описание миссии языка DAX

Итак, подводя итог всей статьи про краткое описание и знакомство с DAX, можно описать его некое предназначение: данный язык функций и формул является неким мостом между вашей исходной моделью данных и тем интерактивным отчетом, который вы в итоге хотите видеть и анализировать; этот функциональный язык запросов дает возможность обогатить исходную модель данных, создавая в ней новые вычисляемые таблицы, столбцы и разные меры (вычисления, на основе которых вы и будете изучать и анализировать всю информацию).

Создаём дашборд с помощью DAX в Power BI — пошаговый алгоритм

Научитесь выстраивать процессы для роста бизнеса и увеличения прибыли.

Пока все аналитики, финансисты и менеджеры сидят на самоизоляции и работают дистанционно, начальство требует подтверждения их занятости и отчеты о проделанной работе. Самый лучший способ продемонстрировать свою эффективность – дома, сидя на диване с чашечкой кофе, создать яркий дашборд, который не только продемонстрирует ваши аналитические способности, но и покажет работодателю, что он не зря платит вам зарплату.

В связи с этим наш цикл статей про Power BI и DAX и курс по работе с этим программным обеспечением сейчас как нельзя кстати.

Если вы помните, мы уже познакомились с типами визуализации, концепциями языка запросов, уяснили, где он может нам пригодиться, узнали синтаксис и даже попробовали написать наши первые формулы. Ну что же, вот теперь мы добрались до самого интересного: пришло время воспользоваться уже полученными знаниями и сотворить что-нибудь этакое (чем потом можно поражать вашего начальника или коллег финансистов и аналитиков). Еще больше полезной информации можно узнать на нашем открытом онлайн-курсе «Введение в бизнес-аналитику».

Загрузка данных

DAX – многофункциональный язык запросов, с помощью которого можно реализовать в Power BI практически любую, даже самую сумасшедшую идею. Наша задача заключается в том, чтобы создать простой, не нагромождённый отчет, но при этом достаточно эффективный. Для этого в течение всей статьи мы будем знакомиться с некоторыми хитростями языка DAX, которые действительно повышают удобство использования панелей мониторинга.

Начнем с самого начала, загрузим данные.

На основании такой диаграммы мы можем сказать, как менялись продажи на протяжении 4 лет.

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

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

Отчет по месяцам

Когда мы создаем панели отчетов, перед нами стоит задача поиска закономерностей и сравнения показателей. Когда аналитик сравнивает одно значение с другим, он начинает говорить с данными на одном языке. Они сами, без особого принуждения, расскажут ему, как и из-за чего «докатились до такой жизни».

Например, можно сравнивать показатели по месяцам и смотреть, происходит рост или спад, насколько реальные показатели лучше или хуже, чем запланированные и так далее.

Для того, чтобы производить сравнение KPI по разным периодам (по годам, соответствующим месяцам, кварталам), нам нужно хорошенько поработать с датами. У нас есть столбец со значениями дат. На их основе мы можем строить прогноз. Но для эффективного сравнения этого недостаточно. Сделаем несложное преобразование с помощью DAX и посмотрим, насколько более гибкой станет наша модель данных.

Создадим таблицу под названием Calendar и воспользуемся одноименной формулой.

CALENDAR – первая формула DAX, которую мы используем сегодня. Она позволит нам построить список дат из указанного диапазона. Наши данные начинаются с 03.01.2014 (всем понятно почему не раньше, праздники же) по 30.12.2017. Поэтому функция будет выглядеть следующим образом:

CALENDAR("2014-01-03, "2017-12-30")

Чтобы не искать в таблице начальную дату и последнюю, можно воспользоваться следующей формулой:

CALENDAR(MINX('Orders'; Orders[Order Date]); MAXX('Orders'; Orders[Order Date]))

Мы получили столбец с датами. Создали еще один столбец с годом, месяцем и кварталом и преобразовали в текстовый формат по формулам:

"Year" = FORMAT([Date], "yyyy"),

"Month"= FORMAT([Date],"MMM"),

"Quarter"= FORMAT([Date],"\QQ")

Такую операцию можно сделать и автоматически об этом знают наши эксперты курса по Power BI. Существуют и другие функции, такие как день недели, номер месяца и так далее. Их также можно использовать для построения столбцов в измерении времени, даты или календаря.

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

Теперь немного изменим наш построенный выше график: данные о дате будем брать из новой таблицы Calendar. И добавим срез по годам, чтобы отслеживать динамику за каждый год по отдельности.

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

Правильно, и то, и другое. Поэтому мы сегодня создадим панель управления таким образом, чтобы благодаря всего лишь одному клику мыши мы могли бы переключаться с дашборда «Продаж» на дашборд «Прибыль», при этом создав всего один.

Заинтриговали? Тогда повторяйте за нами.

Для начала создадим дополнительную таблицу, которая будет содержать названия KPI. Сюда также можно включить прогноз, валовую прибыль и другие показатели эффективности. Но так как у нас в данных есть только два ряда значений, нам выбирать не приходится.

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

Selected KPI = MIN(KPIs[ID KPI])

Теперь мы готовы создать наш переключатель. Для этого создадим новую меру под названием Filter.

Filter = SWITCH([Selected KPI];1; SUM(Orders[Sales]);2; SUM(Orders[Profit]))

Функция SWITCH (переключатель) возвращает результат в зависимости от значения. Как раз то, что нам нужно: если ID KPI = 1, тогда мы строим графики для Продаж, если ID KPI = 2, тогда для Прибыли. Все просто.

Научитесь выстраивать процессы для роста бизнеса и увеличения прибыли.

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

Вуаля! Теперь нам осталось создать визуальный элемент переключателя и сами графики для анализа. Начнем.

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

В любом случае, даже повторив за нашими действиями, но адаптируя команды под свои данные, вы многому научитесь, если ранее не имели опыта работы c DAX в Power BI.

Старайтесь чаще прибегать к подобным мини-разработкам отчетов, панелей мониторинга и дашбордов (не только в период самоизоляции)! Вы увидите, насколько быстрее и легче будет проходить обработка и анализ данных, а также общение с начальством. А это большой плюс, не правда ли?!

Автор: Андрон Алексанян, СОО «Аптека-Центр», эксперт SF Education

Научитесь выстраивать процессы для роста бизнеса и увеличения прибыли.

При подготовке материала использовались источники:
https://vc.ru/newtechaudit/120381-biznes-analiz-na-dax
https://vc.ru/u/624860-iqbi/175891-yazyk-dax-ponyatie-dlya-chego-dlya-kogo-kakie-zadachi-reshaet

Создаём дашборд с помощью DAX в Power BI — пошаговый алгоритм