usto-excel

блог об Excel и не только

Html

Правила мер

декабря 18, 2018 Комментариев нет :

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



  • Вариант А: в 2001 году общая сумма продаж продукта с кодом 344 составила 98600 долларов США;
  • Вариант Б: если в таблице отфильтровать столбец "Год" так чтобы отображались только строки равные 2001 а потом отфильтровать столбец "КодПродукта" так чтобы отображались только строки равные 344, после чего суммировать значения отображающихся строк по столбцу "СуммаПродаж", то их сумма будет равняться 98600 долларов США.
Если Вы выбрали вариант А, то скорее всего у Вас ещё недостаточно опыта работы со сводными таблицами и возможно Вам следует попрактиковаться с обычными сводными таблицами прежде чем продолжать изучение Power Pivot. Потому что правильный ответ - вариант Б.
Очень важно чтобы Вы понимали процесс вычисления мер так, как это указано в варианте Б, потому что именно так DAX их и вычисляет. Конечно, думать как человек (вариант А) всё ещё важно и даже нормально, когда Вы создаёте меры и получаете правильные результаты. Но, когда Ваша формула мер не работает так как Вы ожидаете, Вам нужно "мыслить как DAX", чтобы отладить её.

Пошаговое изучение примера

Давайте в окне Power Pivot пройдём все этапы описанные в Варианте Б, чтобы получить  более ясную картину того как было получено значение "98600".
В окне Power Pivot откроем вкладку таблицы продаж "t_sales".


Обратите особое внимание на три элемента окна Power Pivot:

  1. Кнопка "Отменить все фильтры" на ленте. На рисунке выше данная кнопка серая, т.е. неактивная, что говорит о том что к таблице не было применено никаких фильтров;
  2. Табло количества строк. Показывает, что таблица продаж, при отключённых фильтрах, состоит из 60398 строк и что курсор находится в строке 1. 

      3. Область вычисления. Данное поле находится внизу таблицы и в нём отображаются все созданные в данной таблице меры с их текущими значениями.



Итак, применим фильтр к столбцу "Год" и выберем "2001".



Сразу же после этого, проверим значения мер и количество строк.



А теперь применим фильтр к столбцу "КодПродукта" и снова проверим значения.


И к своему удовлетворению находим, что эти значения совпадают со значениями сводной таблицы. Более того, [Прибыль] равна $47 462 а [Процент Прибыли] равен 48,1%, точь в точь как в сводной таблице.


Точно также совпадает количество транзакций (количество строк).


И если развернуть окно фильтра в столбце "ДатаЗаказа" и посчитать количество дней в списке, то можно убедиться, что значение [ДниПродаж] тоже вычислено правильно.


Три Золотых Правила мер DAX

Правило А: Меры DAX рассчитываются на основе исходных данных, а не сводных таблиц



Хоть на первый взгляд и кажется, что значение Общего итога столбца "Прибыль" в сводной таблице является суммой остальных строк этого столбца, Вы должны мысленно представлять исходную таблицу данных, и понимать что в движке DAX меры рассчитываются именно на основе этой исходной таблицы.

Правило В: Каждая ячейка мер вычисляется независимо

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

Правило С: Процесс вычисления мер DAX подразделяется на шесть логических шагов

Шаг 1: Определение координат сводной

Прежде чем ознакомиться с введённой Вами формулой, движок DAX определяет координаты ячейки мер в текущей сводной таблице (в поле "Значения", так как это единственное поле сводной таблицы куда можно поместить меры).
Чтобы лучше это проиллюстрировать изменим нашу сводную таблицу. Переместим "Год" в поле "Столбцы", создадим срез из столбца "НомерМесяца" а в поле "Значения" оставим лишь [ИтогоПродаж].


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


У выделенной ячейки мер три "значения координат" сформированных на основе комбинации значений из полей "Строки", "Столбцы" а также значения среза:

t_sales[НомерМесяца]=8
t_sales[Год]=2001
t_sales[КодПродукта]=313

Обратите внимание как координаты описываются в формате Таблица[Столбец]. Сейчас это может казаться странным, но использование этого формата очень пригодится когда мы будем использовать несколько таблиц. Так что привыкайте к этому формату описания координат.
Очень часто набор координат меры называется её Фильтр-контекстом.

Шаг 2: Функция CALCULATE() изменяет Фильтр-контекст

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


Шаг 3: Применение координат фильтра к исходной таблице

Координаты меры (фильтры составляющие фильтр-контекст меры) применяются к исходной таблице. В вышеприведенном примере все три значения координат/фильтры относятся к таблице "t_sales", значит указанные фильтры применяются именно к этой таблице. (Конечно, мы не видим этих фильтров, потому как весь этот процесс происходит внутри движка DAX). В нашем случае, таблица фильтруется на основе значений фильтр-контекста НомерМесяца, КодПродукта и Год.


Шаг 4: Фильтры подчиняются установленным связям

Данный шаг также будет более подробно описан в следующих статьях.

Шаг 5: Вычисление

Только после того как фильтр-контекст ячейки меры был применён к исходной таблице и ТОЛЬКО ПОСЛЕ ЭТОГО, движок DAX приступает к вычислениям на основе записанной в мере формулы.


Шаг 6: Возврат результата

Результат проведённых вычислений возвращается ячейке меры сводной таблице и весь процесс начинается заново уже для следующей ячейки.


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

Кроме Трёх Золотых Правил описывающих процесс работы движка DAX, будет не менее полезно изучить несколько дополнительных правил, способных облегчить наш процесс работы с мерами в будущем:

Никаких "Голых столбцов" в формулах мер 

В формуле мер можно ссылаться на столбец только если эта ссылка помещается внутри какой-либо функции. Прямая "голая" ссылка на столбец приведёт к ошибке.

Данное правило касается только мер но не вычисляемых столбцов.

Ссылаться на столбцы и меры по-разному

При написании формулы меры предпочтительно:

  • при ссылке на столбец, использовать формат ИмяТаблицы[ИмяСтолбца];
  • при ссылке на другую меру, использовать формат [ИмяМеры].
Соблюдение данного правила в дальнейшем облегчит чтение формул в будущем. Так если ссылка содержит название таблицы мы сразу поймём, что это ссылка на столбец и наоборот.

Мера должна применяться к нужной таблице

С помощью строки "Имя таблицы" в редакторе мер можно контролировать то к какой таблице относится выбранная мера.



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

Заключение

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

Введение в DAX: Меры

декабря 05, 2018 1 комментарий :
Те из Вас, кто знаком со сводными таблицами наверняка знают о "Вычисляемых полях". Они дают возможность проводить дополнительные вычисления в самой сводной таблице. Но их функционал настолько ограничен и ими так неудобно пользоваться, что я всегда предпочитал проводить дополнительные вычисления в самой таблице данных.



Тем не менее, я всегда задавался вопросом, почему 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, что также можно сделать двумя способами:

  1. Нажав на кнопку "Сводная таблица" на вкладке "Главная" в окне Power Pivot;


      2. Нажав на кнопку "Сводная таблица" на вкладке "Вставка" в окне Excel




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



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

  1. Выбрав "Меры > Создать меру ..." на вкладке "Power Pivot";


      2. Кликнув правой кнопкой мышки по названию таблицы данных в списке полей сводной              таблицы и выбрав пункт "Добавить меру ...".



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



Обратите внимание на поле со знаком "=". Не будем пока отвлекаться на остальные атрибуты редактора мер, а просто напишем в этом окне следующую формулу:
=SUM((t_sales[СуммаПродаж])

Как видите кнопка "ОК" ещё неактивна, и чтобы её активировать нам нужно подобрать для нашей меры соответствующее имя и ввести его в поле "Имя меры:".


Жмём "ОК" и перетаскиваем нашу меру в поле "Значения".



Поэкспериментируем с нашей новой мерой ещё. Перетащим НомерМесяца в поле "Строки" и Год в поле "Столбцы".


Как видим наша мера работает нормально.

"Явные" и "Неявные" меры

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


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

  1. Явной мерой - создав нужную формулу в редакторе мер;
  2. Неявной мерой - просто перетащив нужный столбец в поле "Значения".
Так вот:
НИКОГДА не пользуйтесь неявными мерами! Хоть даже если это и проще, лучше запустить редактор мер и написав формулу создать нужную меру. Вы получите гораздо больше выгоды от использования явной меры.

Создаём Меры со ссылкой на другие Меры

Давайте создадим ещё одну простую меру суммирования для Маржи.
=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 Вы просто перетаскиваете нужные значения в соответствующие поля сводной таблицы и всё!






Введение в DAX: Вычисляемые Столбцы

ноября 25, 2018 Комментариев нет :

В DAX (Data Analysis eXpressions - язык формул используемый в Power Pivot) существует два метода записи формул - "Вычисляемые столбцы" и "Меры".
Так как суть "Вычисляемых столбцов" должна быть более понятна тем пользователям Excel, которые работали с умными таблицами, мы начнём наше знакомство с DAX именно с них.


Добавляем наш первый Вычисляемый Столбец

Чтобы добавить Вычисляемый Столбец в Power Pivot нужно прежде загрузить в него данные.



Видите этот пустой столбец справа, с заголовком "Добавление столбца"? Выделяем любую ячейку в этом столбце и вводим "=", чтобы начать писать формулу.


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


Отлично, теперь попробуем отнять "СебестоимостьПродукта" от "СуммыПродаж". В строке формул, после ссылки на столбец "СуммаПродаж" пишем знак "-" а потом квадратные скобки "[". Сразу же после этого Power Pivot предлагает нам список с названиями всех столбцов в квадратных скобках.



Вводим букву "С" и Power Pivot моментально сужает список, оставляя только названия тех столбцов, которые начинаются с этой буквы.


Используем стрелки на клавиатуре, чтобы выбрать нужное название и нажимаем клавишу "TAB".


Осталось только нажать "ENTER", чтобы получить наш первый Вычисляемый Столбец.


У Вычисляемых Столбцов более тёмная заливка по сравнению с остальными столбцами таблицы, что помогает быстро отличить их от обычных столбцов.

Как видите, всё это действительно очень напоминает то как пишутся формулы в умных таблицах Excel.






Переименовываем Вычисляемый Столбец

Обратите внимание, что нашему Вычисляемому столбцу было автоматически присвоено имя "Вычисляемый столбец 1". 

В Power Pivot названия столбцов также служат как названия именованных диапазонов.

Так что лучше сразу же переименовать Вычисляемый Столбец и дать ему более понятное имя. Кликнем правой кнопкой мыши по названию столбца и выбрав "Переименовать столбец" переименуем его в "Маржа".


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



 Основные характеристики Вычисляемых Столбцов

Теперь перечислим основные характеристики Вычисляемых Столбцов:
  • Формула Вычисляемого Столбца распространяется на все строки этого столбца. Т.е. в отличие от "умных таблиц" Вы не можете изменять значение или формулу какой-либо отдельной строки Вычисляемого Столбца;
  • На Вычисляемые Столбцы ссылаются по их названию заключённому в квадратные скобки. Это также означает, что названия столбцов могут содержать пробелы;
  • На Вычисляемые Столбцы также можно ссылаться используя перед названием столбца название таблицы в одинарных кавычках - 'НазваниеТаблицы'[НазваниеСтолбца]
          Ссылка типа НазваниеТаблицы[НазваниеСтолбца] также допустима,                          если название таблицы не содержит пробелов.
  • полученные значения Вычисляемого Столбца сохраняются в файле Excel вместе с моделью данных. Это значит что Вычисляемые столбцы являются статичными и пересчитываются лишь:
    • при редактировании формулы Вычисляемого Столбца;
    • при обновлении данных.

Более сложные вычисления

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







Выберем уже знакомую нам по работе с Excel функцию SUM() (СУММА).



Обратите внимание, что так как функция SUM() является функцией агрегирования, она была применена не к отдельно взятой строке столбца СебестоимостьПродукта а ко всему столбцу (т.е. она посчитала общую сумму всех строк столбца).

Многие функции перекочевавшие из Excel в Power Pivot имеют почти те же параметры и характеристики какие у них были в Excel. К примеру, функции MONTH() и YEAR().







Пожалуй пока это всё что нам нужно знать о Вычисляемых Столбцах. В следующей статье мы познакомимся с тем, что Bill Jelen назвал "Самым лучшим, что произошло с Excel за последние 20 лет"


Подписаться на: Сообщения ( Atom )

Recent Posts

Sports

Форма для связи

Имя

Электронная почта *

Сообщение *