Как сделать меру в power bi
Обновлено: 07.07.2024
Приветствую всех, кто работает с информацией! Если Вы еще не знакомы с Power BI, то сегодня Вам предстоит сделать удивительное открытие! Приготовьтесь в тому, что теперь ваша жизнь разделится на "до и после Power BI".
Ведь речь пойдет о новой технологии работы с данными, чем мы все использовали прежде.
Забудьте всё, что Вы знали об excel; забудьте про долгую и нудную обработку таблиц и ячеек.
Встречайте сверхзвуковую скорость и возможности программиста в руках обычного экономиста! И заметьте, Вам не нужно учиться писать программный код!
Наступила новая эпоха, господа! Эпоха Power BI. И управленческий учет уже никогда не будет прежним.
Как вымирают динозавры: макросы, ВПР и сводные таблицы.
про эволюцию Excel и как становятся ненужными все другие "навороченные" функции, которые еще вчера казались чудесами автоматизации
Для начала, я хочу коротко описать некоторый функционал программы Excel, который считался (да и сейчас считается!) высшим пилотажем в среде экономистов: это макрос, ВПР и сводная таблица.
Макрос - это алгоритм действий, записанный пользователем. Когда при обработке однотипных файлов выполняются однотипные операции, самые продвинутые пользователи программы записывают с помощью макроредактора (встроенного в Excel) последовательность действий. Запись делается один раз и этой последовательности присваивается сочетание горячих клавиш. В дальнейшем, при нажатии этих клавиш, макрос запускается и программа повторяет эти операции с каждым последующим файлом самостоятельно и быстро, уже без участия человека.
ВПР - это функция Excel, которая называется Вертикальный просмотр. Функция ищет заданные значения (или диапазон значений) в столбце и возвращает любые данные из строки, которая соответствует ячейке с найденным значением в столбце. Таким образом, функция ВПР позволяет связать две таблицы.
Сводная таблица - это инструмент обработки данных в Excel. Сводная таблица может автоматически сортировать, рассчитывать суммы и получать средние значения.
Эволюция шла пока мы спали. В то время, как экономисты писали макросы, разбирались с ВПР или учились создавать сводные таблицы, - в Excel появились новые надстройки, которые вывели все эти процессы на принципиально новый уровень.
Но мы тогда об этом не знали.
Сначала в версии Excel 2010 года появилась новая надстройка - Power Query. Это событие прошло незамеченным для большинства пользователей. Дело в том, что это была надстройка, а надстройки в Excel имеют такую особенность, что их нужно дополнительно устанавливать, иначе их в программе не видно. Так вот, Power Query никто не устанавливал, потому что никто про него просто не знал.
Power Query - это технология подключения к данным (ЗАПРОСЫ).
Что он умеет:
1) подключение к данным из разных источников
2) фиксация шагов в редакторе запросов (этот функционал, кстати, похож на макрос, но, в отличие от макроса, который записывается целым куском - здесь конструктор)
3) операции над запросами: запросы можно дублировать, объединять и изменять.
Сами данные Query не изменяет. Использует новый язык с коротким названием: М.
Дальше - больше. Появилась принципиально новая технология для хранения и обработки данных - Power Pivot.
Возможно, Вы и ранее слышали об ограничении числа строк в Excel. Но наверняка не воспринимали это серьезно. Ну когда нам накопить такой документ в 1 048 576 строк?
Однако сейчас такие базы легко возникают. Карта передвижения авто, информация о поведении пользователей на сайте, регистрация других событий. Ограничение давно достигнуто и превышено.
Power Pivot - это технология хранения данных, которая позволяет снять все ограничения и значительно повысить скорость обработки.
Pivot так же, как и Query, является надстройкой и появился он тоже в 2010 году.
Если очень коротко, то Pivot - это прокачанная сводная таблица, которая включает только СТОЛБЦЫ и СВЯЗИ между ними (в отличие от Excel, который использует ячейки).
У Pivota есть свой язык DAX, это язык функций и формул, который отличается от привычного языка формул Excel (но не сильно).
Power Pivot заменил и ВПР, и сводные таблицы на принципиально новом уровне, но и это еще не всё!
В 2013 в Excel появилась надстройка Power View - подсистема для построения интерактивных визуализаций и красивых отчетов.
Спорим, Вы тоже про нее не знали и продолжали создавать деревянные экселевские диаграммы по старинке?
А еще через 2 года, в 2015, все эти 3 повера: Power Query, Power Pivot и Power View - были выделены и объединены в отдельной программе Power BI.
И это уже не Excel, а новый продукт и новый шаг в развитии бизнес-аналитики и котором пойдет речь в нашей статье.
Многие не любят аналитику, потому что не понимают, как с ней работать и зачем. Сегодня на примере системы Microsoft Power BI расскажем, как знание простой программы для аналитики может облегчить жизнь любому бизнесу. И не важно, аналитик вы или маркетолог.
Если хотя бы раз в жизни вам нужно было сделать красивый отчет, вы знаете, что это отнимает очень много времени. Нужно найти данные, проанализировать, скомпоновать и красиво визуализировать. Для упрощения процесса и жизни маркетологов/аналитиков/предпринимателей Microsoft придумали Power BI.
Эта бесплатная программа умеет распознавать и подключать более чем к 70 источникам данных. Например, xlsx, csv файлы, txt, данные из баз SQL. Также она может очистить данные или обработать их и привести весь миллион табличек к единой модели данных. Или вы можете задать собственные нестандартные метрики, которые используются конкретно в вашей компании.
Итак, есть 5 составляющих, по которым система работает:
Это же своего рода алгоритм работы программы. То есть сначала нам нужно получить необходимые данные как раз в одноименном окне. У нас откроется окно, где нужно выбрать данные для подключения. Их можно вытащить из штатных баз данных, например MySQL, из табличек Excel или из интернет-ресурсов типа MailChimp, Facebook и других.
По логике отдаленно похоже на работу со слоями в фотошопе. В целом, в редакторе мы можем чистить, обрабатывать, приводить данные к единому виду, если они были из разных источников, что-то объединить или разъединить.
Тут все относительно просто — мы можем устанавливать связи между столбцами разных таблиц, сформировать им направленность (однонаправленные/двунаправленные связи), также можем связать несколько таблиц между собой. Здесь, конечно, нужно изучать инструментарий, чтобы на выходе было понятно, точно и красиво. Хотя то же самое касается инструментов редактора.
На самом деле есть масса вариантов, кому может пригодиться знание этой программы. Ей пользуются продуктовые аналитики, SEO-специалисты, разработчики и тестировщики. Power BI будет одинакова полезна как в IT-компании, так и в e-commerce. Ведь всегда лучше полагаться на реальные цифры, чтобы понять, куда сделать шаг для дальнейшего развития.
Минимальный вариант использования —просматривать готовые отчеты коллег, чтобы сделать выводы или посмотреть количество текущих запасов. В программе есть информационная панель реального времени.
Маркетолог может посмотреть прибыльность разных каналов продаж, чтобы усилить какие-то из них или совсем отключить. Кстати, Power BI можно подключить к Google Analytics и посмотреть, например, количество посещений сайта.
Также специалист по продажам может ориентироваться на отчеты, чтобы понимать свою эффективность или изучать данные о новых клиентах. Руководителям компаний в принципе необходимо смотреть и разбираться в отчетах, чтобы понять, что вообще происходит. К слову, посмотреть отчеты можно даже из приложения, удобно во время командировок.
Ну а созданием этих отчетов может заниматься кто угодно – коммерческий директор, руководитель отдела продаж и т.д. Конечно, на более глубоком и профессиональном уровне это делают аналитики.
Power BI — настоящий спасатель в мире огромного количества данных, которые нужно красиво и понятно упорядочить. Самое важное, что можно сделать это с любыми типами данных и привести их к единому виду. Объединить отчет из Google Analytics и MySQL.
Программа довольно проста в использовании, поэтому изучить функционал полезно будет не только аналитикам. Все отчеты, созданные в программе, могут находиться в облачном хранилище. То есть их можно смотреть в любое время, в любом месте и делать нужные выводы.
28.04.2020 | Инна Медвежер, Юлия Гречина - г. Санкт-Петербург | 1
Рассмотрим следующую условную таблицу данных:
Из таблицы видно, что Проект 2, Проект 3 и Проект 5 имеют связанные с ними отделы, а Проект 1 и Проект 4 — нет.
Мы использовали условное форматирование, чтобы выделить разным цветом (применили код цвета) проекты, которые имеют связанные с ними отделы, а какие нет.
Чтобы получить конечный результат в виде отформатированной цветом таблицы, мы выполняли следующие простые действия:
Для того, чтобы вы могли легко воспользоваться данной функцией рассмотрим наши действия более детально (пошагово).
ШАГ_1
Для начала создаем тестовую Меру следующим образом:
2.VAR pi = SELECTEDVALUE(‘Лист1′[Отдел])
Затем помещаем визуальную таблицу в отчет и добавляем столбцы проекта, отдела и Меры Test1.
Как показано на данном рисунке, используемая нами Мера, позволила определить, в каком из проектов есть отдел, а в каких нет.
ШАГ_2
Затем создаем новую Меру [Color Project], взяв синтаксис из меры Test1, которую создали выше, и изменив ее для вывода имен цветов. Это можно сделать либо путем ввода шестнадцатеричных кодов, либо общих названий цветов (на английском языке).
2.VAR Pi = SELECTEDVALUE(‘Лист1′[Отдел])
Шаг_3
Далее необходимо создать условное форматирование для столбца (Проект), чтобы он был окрашен в соответствии с Мерой [Color Project], которую мы создали ранее.
ШАГ_4
Для этого создаем Меру [Color Test] на основе логики, использовавшейся ранее:
2.VAR Pi = SELECTEDVALUE (‘Лист1′[Отдел])
Условное форматирование является полезным инструментом для визуализации как числовых, так и текстовых данных. Применяйте его в своей работе, это очень удобно!
Как и обещал, начинаю делиться с вами полезными случаями из практики.
Довольно часто возникает потребность в том, чтобы при выборе даты в срезе, выводились данные, которые являются "активными" на эту дату. То есть дата среза должна быть между датой 1 (начала действия) и датой 2 (окончания).
Дано: список контрактов, у каждого есть дата начала и дата окончания. В дашборд вынесен срез (slicer) по дате, который взят из справочника и фильтрует разные таблицы и графики на листе.
Задача: Вывести в таблицу все активные контракты за выбранный месяц. То есть, если в срезе мы выбираем сентябрь 2020, то в таблице должны отразиться все контракты, которые были открыты до сентября (включительно) и закрыты после сентября.
На первый взгляд, задача простая. Вы можете установить связь между календарем и таблицей "Контракты" по дате закрытия. Однако в этом случае, вы получите только те контракты, который были закрыты в выбранном месяце. Это не то, что нам нужно.
Есть еще вариант - установить 2 среза: один по дате открытия контракта, второй по дате закрытия. Но, во первых, это не удобно, во вторых это дополнительный срез, которые будет "болтаться" на листе. Напомню, что по условиям задачи у нас есть другие данные, на которые влияет срез по дате.
Давайте рассуждать дальше. Вот если бы нам знать какой месяц выбран в срезе, мы бы могли сравнить его с датой начала и датой окончания и понять активен ли контракт в этом месяце. Нет проблем, воспользуемся функцией SELECTEDVALUE . Эта функция позволит нам "посмотреть" на отфильтрованную таблицу после выбора значения в срезе.
У функции SELECTEDVALUE есть пара особенностей:
1) В том виде, в котором я ее применил, она не будет работать, если вы выбираете несколько месяцев. Это также решаемая проблема, но не в рамках данной статьи. В данном случае, мы настраиваем срез так, чтобы можно было выбрать только один месяц.
2) SELECTEDVALUE не работает в столбце таблицы. Нам ничего не мешает ее туда вписать, но она не будет показывать выбранное значение среза. Это связано с особенностями обновления данных, формулы в таблицах не пересчитываются при изменении значений среза. Она работает только в мере.
Второй пункт создает нам проблемы, так как было бы удобно создать дополнительный столбец и сравнить каждую строчку таблицы с выбранным значением среза. С другой стороны мера не умеет работать со столбцом без функций - агрегаторов. Значит будем использовать агрегатор, в качестве функции агрегатора выберем SUMX . Особенность функции SUMX заключается в том, что она считает таблицу построчно, запоминает посчитанное для каждой строки значение в виртуальной памяти, а затем суммирует эти значения. Ключевое слово - построчно .
Но сначала нужно доработать календарь. Добавим в него столбец с концом месяца. Далее будем сравнивать даты начала и окончания контракта с концом месяца. Это позволит нам включить в таблицу те контракты, которые начались до 30/31 числа выбранного месяца и закончились после 30/31 (то есть уже в след. месяце). Сделать это очень просто, достаточно использовать функцию ENDOFMONTH
Далее обернем SELECTEDVALUE в SUMX . Пишем формулу:
Попробую поэтапно объяснить написанную формулу:
1 Функция FILTER фильтрует таблицу "Contracts" по указанным правилам, а именно: дата открытия контракта должна быть меньше или равно концу месяца, который выбрали в срезе + дата закрытия должна быть больше чем конец месяца, который выбрали в срезе.
2 Функция SUMX в качестве первого параметра принимает таблицу, которая уже отфильтрована функцией FILTER. В качестве второго параметра можно указать любое числовое значение. Я сделал дополнительный столбец с номером месяца. Логика такая: SUMX получает нужную нам таблицу с контрактами, которые активны в выбранный месяц, далее SUMX проходит по каждой строке таблицы, запоминает значение NUMBER и в итоге суммирует все значения.
3 Функция IF тут нужна, чтобы разделить активные и неактивные контракты. Я это сделал через сравнение SUMX с нулем. Таким образом, если вы выберете на срезе месяц, в котором нет активных контрактов, функция FILTER вернет пустую таблицу, SUMX будет нечего суммировать и она вернет ноль, а IF вернет "no"
Да, забыл сказать, связь между таблицей контрактов и календарем нужно удалить.
Ну и заключительная часть нашей работы: выносим нашу меру в фильтры отчета, выбираем "содержит" и пишем "Yes".
Пошаговое руководство для создания первого отчета в Power BI от ведущего специалиста по автоматизации интернет-рекламы в eLama Екатерины Тюлюкиной.
Поэтому сейчас компании делают выбор в пользу подготовки отчетности с помощью BI систем. Одна из самых известных и популярных — Power BI.
Что такое Power BI
Это линейка продуктов Microsoft, разработанная для обеспечения на предприятиях удобной и прозрачной работы с отчетностью. Всего в нее вошло семь продуктов: Power BI Desktop, Power BI Services, Power BI Embedded, Power BI Mobile, Power BI Report Server, Шлюзы PBI. Но для решения большинства задач, связанных с анализом продвижения, будет достаточно двух:
Power BI Desktop — приложение для работы с данными, его необходимо установить на компьютер. С его помощью можно подключить источники данных, структурировать их и построить визуализированный отчет с таблицами и диаграммами.
Power BI Services — это портал, облачный сервис, в котором публикуются все отчеты, созданные в Power BI Desktop. Функции этого портала скорее административные, с его помощью, например, можно управлять всеми отчетами в организации и доступами к ним.
Принцип работы Power BI достаточно прост: данные из различных каналов передаются в систему с помощью коннекторов, проходят обработку и визуализируются в заранее подготовленном шаблоне. На выходе получается готовый структурированный отчет, который может самостоятельно обновляться в зависимости от заданных настроек.
Упрощенная схема работы Power BI с несколькими популярными источниками
Созданным отчетом можно делиться, внедрять на сайт или просто использовать для мониторинга, система позволяет настроить оповещения о критических показателях и многое другое.
При этом в большинстве случаев будет достаточно базовых возможностей программы и покупка платного пакета не потребуется. Например, в бесплатной версии Power BI можно использовать все встроенные источники данных, визуализации и службы. Потребность в платной подписке Power BI Pro, как правило, возникает, когда появляется необходимость работать над отчетностью вместе с коллегами и клиентами без выгрузки в интернет, а в Power BI Premium — когда нужно обрабатывать огромные массивы данных. Оценить и сравнить возможности Power BI Pro и Power BI Premium можно на сайте сервиса.
Power BI может помочь сэкономить много времени, сил и средств при подготовке отчетности. Настроив его инфраструктуру единожды, специалист будет долго наслаждаться автоматически создаваемыми отчетами. Однако настройка и подготовка первого отчета не такая простая, как может показаться изначально. Иногда для создания подключения к источникам данных, недоступным по умолчанию, или структурирования полученной информации может потребоваться помощь программиста.
Важно отметить, что по этой причине многие компании пользуются сервисами с готовыми решениями, которые помогают избежать сложностей с коннекторами, моделированием данных и предоставляют уже готовые шаблоны с набором визуализаций.
Ниже поговорим о том, как начать работу с Power BI самостоятельно и разберем основные возможности его работы.
Как начать пользоваться отчетами в Power BI
Для начала потребуется скачать версию Power BI Desktop на компьютер. Программа работает только на Windows. Для работы на устройствах от Apple потребуется в первую очередь установить виртуальную систему Windows и уже после загрузить Power BI Desktop.
Если почты с корпоративным доменом нет, можно зарегистрировать бесплатную пробную версию Office 365 — так вы получите доменное имя в системе Microsoft и сможете использовать почту для подключения Power BI. Подробнее, о том как пройти регистрацию этим способом, можно прочесть в справке Power BI.
После завершения регистрации откройте скачанное приложение Power BI Desktop и войдите в систему. Вы увидите чистый рабочий стол, на котором предстоит создать первый отчет. Далее работа, как правило, строится из нескольких этапов.
Этап 1. Получение данных
Сейчас Power BI поддерживает интеграцию с более чем 70 готовыми источниками, среди них — рекламные площадки, системы веб-аналитики, базы данных. Весь список доступен в справочном центре.
Подключив один или несколько источников к Power BI, вы сможете строить визуализации.
Вот кратко о подключении Google Analytics: нажимаем Get Data в приложении Power BI, выбираем Online Services, затем находим Analytics, подтверждаем связь с аккаунтом в системе аналитики, выбираем нужную таблицу из массива данных и нажимаем Load. Таблица с данными будет загружена в систему и с ней можно работать.
Важно отметить, что в Google Analytics данные могут семплироваться, и в таком случае для работы с точными данными потребуется подобрать другое решение для его интеграции или найти в интернете инструкции, описывающие способы обойти семплирование.
Этап 2. Работа с данными в отчете, моделирование данных, связи
С помощью Power Query — специальной надстройки внутри Power BI для импорта и обработки данных — можно выполнять различные операции с данными: удалить столбец, изменить тип данных, объединить таблицы и т. д. Power Query позволяет сохранять используемые запросы и применяет настройку к новым данным. Подробнее о возможностях надстройки и работе с ней — в справке.
Помимо этого, в Power BI можно рассчитать дополнительные показатели, например, что-то суммировать, найти разницу, процент от числа или произвести более сложные вычисления. Такие операции можно делать прямо в отчетах — с помощью мер, вычисляемых столбцов и вычисляемых таблиц.
Все вычисления в Power BI выполняются на языке DAX (краткое руководство).
Однако привести данные к необходимому виду — это еще не все. Если загружаемые данные поступают в отчет в виде сразу нескольких таблиц (что чаще всего бывает при использовании более одного источника), необходимо задать связи между ними. Без связей не получится строить зависимость между несколькими источниками.
По умолчанию Power BI установит связи между столбцами с идентичным названием, остальные придется задать вручную. Их можно установить в нескольких режимах, выберите тот, который удобен вам:
Всего в Power BI существуют четыре типа кратности: многие к одному, один ко многим, многие ко многим и один к одному. Все они обозначают отношение значений в столбце одной таблицы к значениям в столбце другой таблицы (подробнее в справке).
Также каждая связь должна иметь параметр направления кросс-фильтрации, от него зависит, как будут обрабатываться таблицы. Чаще всего используется однонаправленная фильтрация, к двунаправленной фильтрации прибегают когда одна из таблиц соотносится с несколькими таблицами сразу (подробнее в справке).
Этап 3. Визуализации
Ниже на скриншоте — несколько простых визуализаций:
1 — Круговая диаграмма. 2 — Карточка с числом. 3 — Индикаторная диаграмма. 4 — Карточка с текстом. 5 — Таблица.
Помимо вышеперечисленных визуализаций, Power BI есть всевозможные диаграммы, карты, графики, срезы и т. д. Полный список — в справке. Вы с легкостью найдете вариант, применимый к вашему набору данных.
Однако для каждого бизнеса важны свои метрики. Поэтому перед тем как создавать отчет, рекомендую определиться с показателями: какие из них вы собираетесь отслеживать и почему, а уже затем подбирать визуализацию.
В списке визуализаций также можно найти и срезы, они позволяют делать фильтрацию в отчетах. Например, можно отфильтровать данные в отчете по дате или типу рекламной системы, типу устройств и другим.
Этап 4. Выгрузка, внедрение и публикация отчета
Когда отчет попадет в Power BI Services, его можно будет экспортировать в PowerPoint или PDF, скачать, но самое приятное — опубликовать в интернет. Это позволяет в любой момент получить доступ к отчету по ссылке, при этом данные в отчете могут обновляться, а ссылка на отчет останется неизменной.
При выборе этой функции система предложит два варианта:
Создать код внедрения для размещения на сайте.
Этап 5. Настройка автообновления по расписанию
Обновление происходит автоматически: данные загружаются в шаблон отчета без участия специалиста — и это экономит время сотрудников.
Кроме того, данные нельзя ненамеренно изменить, что исключает возможность ошибок в отчетах из-за человеческого фактора.
Поэтому перед настройкой частоты обновлений отчетов, проверьте частоту обновлений данных у источника.
Заключение
Конечно, сначала процесс работы и внедрения отчетности в Power BI может показаться сложным. Но когда настройки будут сделаны, а отчет готов, вы поймете, за что многие так ценят работу с этим продуктом.
Чтобы разобраться в работе Power BI, посмотрите уроки от Microsoft, они дают ответы на все основные вопросы, которые возникают при работе с сервисом.
Читайте также: