Использование функции Related из надстройки PowerPivot

В следующих нескольких разделах рассматриваются примеры использования файла ChlOWeatherMashup.xlsx, в котором находятся сведения о продажах по дням и набор данных о точках продажи. Компания продает продукты в супермаркете и в аэропорту. Две торговые точки находятся менее чем в 10 милях друг от друга, но при этом демонстрируют различные тенденции продаж.

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

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

  • WeekdayName, использующий формулу =FORMAT(Sales[Date],»dddd») для преобразования даты в день недели.
  • Многие пользователи выражают свое недовольство, когда узнают, что сводные таблицы PowerPivot не поддерживают пользовательские списки, отображающие дни недели, и им приходится создавать вычисляемый столбец WeekdayID.

  • В вычисляемом столбце WeekdaylD используется функция =WEEKDAY(Sales[Date],2). Аргумент 2 имеет то же значение, что и соответствующий аргумент в функции Excel. В частности, он нумерует дни недели таким образом, что понедельнику присваивается значение 1, вторнику — 2 и т.д. Воскресенье получает значение 7. Состоящая из 7 строк таблица Weekday устанавливает соответствие между днями недели (с понедельника по воскресенье включительно) и числами (с 1 до 7 включительно). В результате имена дней недели отображаются корректно.
  • В вычисляемом столбце LocationDays находится формула CONCATENATE (Sales[Location],Sales[Date]). Эта формула используется в дальнейшем при вычислении различных номеров для дней, в течение которых выполняются продажи.

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

В вычисляемом столбце LocationWeek применяется следующая формула: =Concatenate(Sales[Location],Sales[WeekdayName]). Обратите внимание на то, что все перечисленные выше вычисляемые столбцы могут ссылаться на другие вычисляемые столбцы. Этот вычисляемый столбец с помощью установленной связи ссылается на таблицу, в которой представлены сведения о персонале.

Многие пользователи думают, что поскольку PowerPivot «понимает» связи, установленные между таблицами, которые содержат сведения об объемах продаж и персонале, можно создать такую формулу, как =Sales[Net Sales]/Staffing [Staff Level] (рис. 10.25). Но, к сожалению, при выполнении этой формулы появляется сообщение об ошибке.

Рис 10.25. Определенная связь не может автоматически использоваться в вычисляемых столбцах

Рис 10.25. Определенная связь не может автоматически использоваться в вычисляемых столбцах

Причина появления этой ошибки заключается в том, что при выполнении вычислений предпринимается попытка деления значения 2202 в столбце Net Sales на значения в столбце Staff Level, находящемся в таблице Staffing. Для устранения этой проблемы используется функция Related)). Перепишите формулу в виде =Sales[Net Sales]/Related(Staffing[StaffLevel]). Функция Related () сообщает DAX о том, что нужно делить 2202 не на все 14 значений в столбце Staff Level из таблицы Staffing, а лишь на одно значение, которое связано со значением AirportSunday.

На рис. 10.26 показан результат применения этой формулы. При просмотре первой строки приходим к выводу о том, что в торговой точке, расположенной в аэропорту, работают два человека. Поэтому объем продаж по отношению к одному человеку составляет половину от значения 2202, т.е. 1101. По вторникам (Tuesday) в магазине работает один человек, поэтому значение в столбце SalesPerPerson совпадает со значением в столбце Net Sales.

Рис. 10.26. Функция Related подобна функции ВПР с одним аргументом

Рис. 10.26. Функция Related подобна функции ВПР с одним аргументом

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

На рис. 10.27 показаны результаты анализа продаж по дням недели в двух торговых точках. Обратите внимание, что пик продаж в торговом центре имеет место по субботам, а в аэропорту — в воскресенье, когда многие отправляются в командировки и закупают все, что нужно для будущей рабочей недели. Обратите внимание на процентные соотношения в строках (рис. 10.27). Анализируя эти соотношения, можно сделать вывод о том, что величины продаж по понедельникам и пятницам практически идентичны.

Рис. 10.27. Пик продаж в аэропорту приходится на воскресенье

Рис. 10.27. Пик продаж в аэропорту приходится на воскресенье

Продолжая выполнение анализа данных, получим результаты, которые близки к ожидаемым. На рис. 10.28 демонстрируется зависимость объема продаж от того, шел ли дождь и насколько сильным он был. Если ваши торговые точки расположены во Флориде, где дождей практически не бывает, вряд ли подобный анализ будет полезным на практике.

Рис. 10.28. Покупатели охотнее совершают покупки в солнечные дни

Рис. 10.28. Покупатели охотнее совершают покупки в солнечные дни

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


Top