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

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

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

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

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

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

У нас есть книга Excel с данными о продажах онлайн магазина за несколько лет. В нашем распоряжении три листа: сведения о продажах (даты, продукты, данные о клиентах, продавцы, регионы), о менеджерах и о возращенных заказах.

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

Строим отчет

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

Давайте построим самый простой график, который только можно придумать: Доход по дате.

На основании такой диаграммы мы можем сказать, как менялись продажи на протяжении 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, тогда для Прибыли. Все просто.

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

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

Теперь вместо полей “Profit” и “Sales” будем использовать “Filter”. В зависимости от позиции переключателя мы будем получать либо графики для прибыли, либо для продаж. Можно еще и скидки добавить в качестве KPI. Вот пример, как актуализировать таблицы, если Вы решили внести еще один показатель для сравнения.

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

Начнем с того графика, что строили в самом начале статьи: KPI по дате. Мы говорим KPI, потому что в итоге, на панели управления сами укажем, по каким значениям строить диаграммы.

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

Построим воронку продаж для товаров (так как товаров у нас очень много, то мы построим график для категории товаров).

Возможно, для анализа нам пригодится распределение KPI по штатам. Построим карту с нанесением на нее пузырьков, размеры которых пропорциональны вкладу этого региона в продажи/прибыль/скидки.

И последняя диаграмма, которую мы отобразим на панели – распределение заказов по месяцам. Для этого из таблицы Calendar возьмем только столбец Month. В данном случае нам не нужно изменение иерархии. Мы хотим посмотреть, как менялось количество заказов в зависимости от месяца. Конечно, такая диаграмма будет напоминать тренд продаж по месяцам, но это не обязательно. Для этого и строится график: чтобы сравнить.

Это единственная диаграмма, которая не зависит от нашего фильтра. Какой элемент из KPI мы бы не выбрали, плитка останется неизменной. Она реагирует только на фильтр года.

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

В итоге наша панель примет во такой вид:

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

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

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

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