Введение в DAX: Меры
Те из Вас, кто знаком со сводными таблицами наверняка знают о "Вычисляемых полях". Они дают возможность проводить дополнительные вычисления в самой сводной таблице. Но их функционал настолько ограничен и ими так неудобно пользоваться, что я всегда предпочитал проводить дополнительные вычисления в самой таблице данных.
Тем не менее, я всегда задавался вопросом, почему Microsoft не могут доработать этот инструмент до конца, вооружив его всем арсеналом существующих функций Excel, чтобы пользователи имели возможность проводить все свои дополнительные вычисления в сводной таблице. Недавно я узнал ответ на этот вопрос.
Вот что пишет Rob Collie в книге Power Pivot and Power BI: The Excel User's Guide to the Data Revolution:
Тем не менее, я всегда задавался вопросом, почему Microsoft не могут доработать этот инструмент до конца, вооружив его всем арсеналом существующих функций Excel, чтобы пользователи имели возможность проводить все свои дополнительные вычисления в сводной таблице. Недавно я узнал ответ на этот вопрос.
Вот что пишет Rob Collie в книге Power Pivot and Power BI: The Excel User's Guide to the Data Revolution:
"У формул сводной таблицы совершенно отдельный от основных формул (тех что используются на листе Excel) движок. Каждый раз, когда наступало время планировать новую версию Excel, нам приходилось решать на что тратить выделенные нам средства. У нас никогда не возникало долгих прений по поводу того инвестировать ли выделенные средства в видимые всем атрибуты, наподобие формул листа Excel или же менее явные. Движок формул сводной таблицы был предан забвению и никогда по-настоящему не улучшался."К счастью с появлением Power Pivot у нас теперь есть полноценный движок формул для работы со сводными таблицами. Так что "Меры" можно охарактеризовать как "формулы которые мы добавляем к сводной таблице". Эти формулы дают нам небывалую мощь и гибкость а преимуществ от их использования гораздо больше чем кажется на первый взгляд.
Добавляем нашу первую меру
Мы можем добавить новую меру двумя способами:
- в окне Excel, через боковую панель "Поля сводной таблицы";
- в окне Power Pivot (в области вычислений)
Начнём с более понятного нам метода, а именно с добавления мер в окне Excel.
Думаю, здесь нужно отметить, что оба метода добавления мер эквивалентны. Т.е. независимо от того какой метод Вы используете для создания меры, в конечном итоге эта мера будет добавлена в модель данных Power Pivot.
Для начала создадим сводную таблицу из нашей модели данных Power Pivot, что также можно сделать двумя способами:
В результате мы получим пустую сводную таблицу на новом листе.
2. Кликнув правой кнопкой мышки по названию таблицы данных в списке полей сводной таблицы и выбрав пункт "Добавить меру ...".
Появится окно с заголовком "Меры", которое мы в дальнейшем будем называть просто "редактором мер".
Обратите внимание на поле со знаком "=". Не будем пока отвлекаться на остальные атрибуты редактора мер, а просто напишем в этом окне следующую формулу:
Как видите кнопка "ОК" ещё неактивна, и чтобы её активировать нам нужно подобрать для нашей меры соответствующее имя и ввести его в поле "Имя меры:".
Жмём "ОК" и перетаскиваем нашу меру в поле "Значения".
Поэкспериментируем с нашей новой мерой ещё. Перетащим НомерМесяца в поле "Строки" и Год в поле "Столбцы".
Как видим наша мера работает нормально.
В данном, конкретном случае мы можем получить желаемый результат двумя способами:
- Нажав на кнопку "Сводная таблица" на вкладке "Главная" в окне Power Pivot;
2. Нажав на кнопку "Сводная таблица" на вкладке "Вставка" в окне Excel
В результате мы получим пустую сводную таблицу на новом листе.
Обратите внимание, что список полей нашей сводной содержит все три таблицы данных из окна Power Pivot. Для меня сам факт того, что у нас появляется возможность полноценно работать сразу с несколькими таблицами данных в одной сводной таблице уже является фантастикой.Теперь приступим непосредственно к созданию новой меры, что опять-таки можно сделать двумя способами:
- Выбрав "Меры > Создать меру ..." на вкладке "Power Pivot";
2. Кликнув правой кнопкой мышки по названию таблицы данных в списке полей сводной таблицы и выбрав пункт "Добавить меру ...".
Появится окно с заголовком "Меры", которое мы в дальнейшем будем называть просто "редактором мер".
Обратите внимание на поле со знаком "=". Не будем пока отвлекаться на остальные атрибуты редактора мер, а просто напишем в этом окне следующую формулу:
=SUM((t_sales[СуммаПродаж])
Как видите кнопка "ОК" ещё неактивна, и чтобы её активировать нам нужно подобрать для нашей меры соответствующее имя и ввести его в поле "Имя меры:".
Жмём "ОК" и перетаскиваем нашу меру в поле "Значения".
Поэкспериментируем с нашей новой мерой ещё. Перетащим НомерМесяца в поле "Строки" и Год в поле "Столбцы".
Как видим наша мера работает нормально.
"Явные" и "Неявные" меры
Пока, что мы не сделали ничего выдающегося. Т.е. тот же самый результат можно было получить просто перетащив в поле "Значения" столбец СуммаПродаж.
В данном, конкретном случае мы можем получить желаемый результат двумя способами:
- Явной мерой - создав нужную формулу в редакторе мер;
- Неявной мерой - просто перетащив нужный столбец в поле "Значения".
Так вот:
НИКОГДА не пользуйтесь неявными мерами! Хоть даже если это и проще, лучше запустить редактор мер и написав формулу создать нужную меру. Вы получите гораздо больше выгоды от использования явной меры.
Создаём Меры со ссылкой на другие Меры
Давайте создадим ещё одну простую меру суммирования для Маржи.
А теперь создадим новую меру, используя те меры, которые были созданы нами ранее.
Мы можем убрать из поля "Значения" прежние меры и переместить туда нашу новую меру.
Давайте вернём [Прибыль] обратно в поле значения
И чуть изменим нашу формулу увеличив прибыль на 10%
У вас должна вылезти ошибка.
Проблема в запятой. Насколько я понял, в отличие от движка формул Excel, движок DAX не локализирован. В нём используются английские имена функций а также принятая в США система отделения целой части числа от дробной не запятой а точкой.
Изменяем запятую на точку и жмём "ОК".
Посмотрите внимательно, значения в столбцах "Процент Прибыли" тоже изменились.
После чего данные в сводной таблице уже отображаются в денежном формате.
Более того, указанный нами формат будет теперь применяться и во всех других сводных таблицах.
Создадим в нашей сводной таблице две новые меры:
И
COUNTROWS()
Как понятно из названия этой функции, она считает количество строк в таблице. К примеру, в примере выше 17 строк со значением 1 в столбце "НомерМесяца". И если считать что каждая транзакция вводилась в отдельную строку, то получается что в Январе было совершено 17 транзакций.
DISTINCTCOUNT()
Данная функция считает количество уникальных значений в указанном столбце. Т.е. хоть в Январе и было совершено 17 транзакций, эти транзакции были совершены в течении 13 (рабочих) дней.
Используя полученные результаты мы можем создать ещё две меры:
Более того, если мы вместо НомерМесяца поставим в поле "Строки" КодПродукта, то получим количество транзакций, дни продаж, продажи за одну транзакцию и продажи за день по каждому продукту.
=SUM(t_sales[Маржа])
А теперь создадим новую меру, используя те меры, которые были созданы нами ранее.
=[Прибыль]/[ИтогоПродаж]
Мы можем убрать из поля "Значения" прежние меры и переместить туда нашу новую меру.
Давайте вернём [Прибыль] обратно в поле значения
И чуть изменим нашу формулу увеличив прибыль на 10%
У вас должна вылезти ошибка.
Проблема в запятой. Насколько я понял, в отличие от движка формул Excel, движок DAX не локализирован. В нём используются английские имена функций а также принятая в США система отделения целой части числа от дробной не запятой а точкой.
Изменяем запятую на точку и жмём "ОК".
Посмотрите внимательно, значения в столбцах "Процент Прибыли" тоже изменились.
Другие выгоды от использования мер
Доступны в любой сводной таблице
До сих пор мы работали лишь с одной сводной таблицей. Но если мы создадим совершенно новую сводную таблицу, все меры которые мы создали в первой сводной будут доступны и в новой.
Одно форматирование применяется во всех сводных таблицах
Давайте добавим все три созданные нами меры в поле "Значения" новой сводной таблицы.
Теперь попробуем изменить форматирование, чтобы придать нашим данным более удобочитаемый вид. Но вместо того чтобы использовать "Формат ячейки" в контекстном меню, либо группу "Число" на вкладке "Главная", кликнем правой кнопкой мыши по одной из этих мер (например [Прибыль]) и вызовем редактор мер.
После чего данные в сводной таблице уже отображаются в денежном формате.
Более того, указанный нами формат будет теперь применяться и во всех других сводных таблицах.
Поглощая по-немногу: COUNTROWS() и DISTINCTCOUNT()
Пока что мы создавали меры используя уже знакомую нам по Excel функцию SUM. Но так как в DAX немало уникальных только для этого движка функций, давайте познакомимся с парой из них, чтобы пробудить Ваш аппетит.Создадим в нашей сводной таблице две новые меры:
[Транзакции]=COUNTROWS(t_sales)
И
[ДниПродаж]=DISTINCTCOUNT(t_sales[ДатаЗаказа])
COUNTROWS()
Как понятно из названия этой функции, она считает количество строк в таблице. К примеру, в примере выше 17 строк со значением 1 в столбце "НомерМесяца". И если считать что каждая транзакция вводилась в отдельную строку, то получается что в Январе было совершено 17 транзакций.
DISTINCTCOUNT()
Данная функция считает количество уникальных значений в указанном столбце. Т.е. хоть в Январе и было совершено 17 транзакций, эти транзакции были совершены в течении 13 (рабочих) дней.
Используя полученные результаты мы можем создать ещё две меры:
[ПродажиЗаТранзакцию]=[ИтогоПродаж]/[Транзакции]И
[ПродажиЗаДень]=[ИтогоПродаж]/[ДниПродаж]
Более того, если мы вместо НомерМесяца поставим в поле "Строки" КодПродукта, то получим количество транзакций, дни продаж, продажи за одну транзакцию и продажи за день по каждому продукту.
Меры это портативные формулы
А теперь просто остановитесь на минутку и подумайте - допустим Вам изначально поручили создать таблицу продаж с разбивкой по месяцам. Т.е. Вы написали формулы считающие количество транзакций и дней продаж с привязкой к номеру месяца. Сколько времени понадобилось бы Вам, сколько громоздких формул пришлось бы написать, чтобы получить этот результат? А потом вдруг Вам сообщают, что нужно сделать то же самое, но в этот раз с разбивкой по продуктам. При использовании стандартных средств Excel это означало бы создавать всё заново и писать формулы уже с привязкой к коду продуктов. Но в Power Pivot Вы просто перетаскиваете нужные значения в соответствующие поля сводной таблицы и всё!
Подписаться на:
Комментарии к сообщению
(
Atom
)
Сообщения
Классно! Спасибо за статьи! У Вас дар объяснять просто и ясно!
ОтветитьУдалить