Як в Excel 2010 підрахувати різні значення за допомогою DAX

За допомогою мови DAX можна підрахувати кількість різних значень, які відповідають умовам фільтра. У цій статті буде докладно розглянута відповідна методика.
Отже, DAX дозволяє підрахувати кількість різних значень, що відповідають умовам фільтра. Чи усвідомили ви всю привабливу силу цього твердження? Досвідчені користувачі, які створили не один десяток складних зведених таблиць, нерідко висловлювали претензії з приводу їх обмежених обчислювальних можливостей. У розділі «Використання макросів для поліпшення звітів зведених таблиць», присвяченому VBA, пропонується досить складна формула = 1 / СЧЕТЕСЛІМН (…) для підрахунку відрізняються значень. Засобами DAX можна підрахувати кількість різних значень, які відповідають умовам фільтра.

Для створення нового запобіжного за допомогою DAX клацніть на кнопці Створити міру (New Measure), що знаходиться на вкладці PowerPivot. Зверніть увагу: в даному випадку мова йде не про вкладку у вікні PowerPivot, а про вкладку PowerPivot, що знаходиться на стрічці Excel. Отже, клацніть на значку Створити міру (рис. 10.30).

Мал. 10.30. Кнопка Створити міру знаходиться на вкладці PowerPivot стрічки Excel 2010

Після клацання на кнопці Створити міру на екрані з’являється діалогове вікно Параметри заходи (Measure Settings), показане на рис. 10.31.

Мал. 10.31. Змініть параметри заходи а цьому діалоговому вікні

  1. В поле Ім’я таблиці (Table Name) вказується ім’я базової таблиці, в якій знаходяться основні числові дані. За допомогою списку змініть значення в цьому полі з Weather на Sales.
  2. В поле Ім’я заходи (Measure Name) введіть ім’я DayCount.
  3. В поле користувача ім’я використовується той же ім’я, що і в поле Ім’я заходи.
  4. Як заходи завжди вибираються агрегує функції, а не функції рівня осередки. Тому зупиніться на агрегує функції, такий як sum або COUNTROWS.
  5. У розглянутому прикладі застосовується «магічна» функція Distinct (Sales [Date]). Ця функція для кожного осередку зведеної таблиці повертає перелік різних значень для рядків, які відповідають умовам фільтра.
  6. Після введення формули клацніть на кнопці Перевірити формулу (Check Formula), щоб переконатися в коректності синтаксису формули (рис. 10.32).

Зверніть увагу на те, що функція Distinct повинна використовуватися в стовпці базової таблиці. Її неможливо застосовувати до значень, що знаходяться в пов’язаної таблиці. Звичайно, результат її застосування може змінюватися за допомогою фільтрів, створених в пов’язаної таблиці, але все ж значення, по відношенню до яких застосовується функція Distinct, повинні перебувати в базовій таблиці. В результаті застосування цієї функції повертається складається з одного стовпця таблиця, яка містить перелік різних значень. Для підрахунку кількості значень скористайтеся формулою = CountRows (Distinct (Sales [Date])).

Мал. 10.32. Створення формули з подальшою перевіркою синтаксису

В даному випадку під ім’ям DayCount мається на увазі промекуточний результат, який ілюструє концепцію використання функції Distinct. Потім можна визначити міру шляхом ділення Net Sales на DayCoimt.
В цьому випадку слід пропустити набір значень DayCount і створити міру Sales Per Day за допомогою єдиної формули = SUM (Sales [Net Sales]) / COUNTROWS (Distinct (Sales [Date])). На рис. 10.33 показані значення в стовпці продажів за день (Sales Per Day), що залежать від інтенсивності опадів і розташування торгової точки.

Мал. 10.33. Продажі в обох торгових точках збільшуються в сонячні дні

На перший погляд здається, що в непогожі дні оборот торгової точки в аеропорту повинен вирости через затримки багатьох рейсів. Насправді це не так, оскільки стрес, викликаний затримками рейсів, не сприяє шопінгу.

Зверніть увагу на некоректно обчислений результат (рис. 10.33). У рядку Загальний підсумок показник продажу за день для торгової точки, що знаходиться в аеропорту, обчислений правильно (приблизно 2665 дол.). Але якщо загальний підсумок по продажах за день для аеропорту дорівнює 2665 дол., А для торгового центру – 4526 дол., То чому в стовпці Загальний підсумок відображається величина 5833 дол.?

Завдяки наявності проміжного стовпчика Дні (Day Count) цю проблему можна усунути. На рис. 10.34 показаний тестовий звіт, який інформує вас про продажі, кількості днів і середніх продажах за день.

Мал. 10.34. Зверніть увагу на те, що в виділеної осередку може бути помилка

Торгова точка в аеропорту відкрито вже три роки. «Стаж» торгової точки, що знаходиться в торговому центрі, менше. Аеропорт працює на Різдво, а торговий центр закритий. Тому протягом багатьох днів працювала всього лише одна торгова точка.

У стовпці Підсумок Продажі показаний загальний підсумок по продажам в обох торгових точках. Обчислюваний стовпець Дні призначений для підрахунку кількості днів, протягом яких відкрита хоча б одна торгова точка. Загальний обсяг продажів для обох торгових точок склав 6,3 млн дол. Але оскільки обидві торгові точки не завжди працювали в один час, обчислення середнього обсягу продажів за день шляхом ділення 6,3 млн на 1086 днів некоректно.

LocationDayCount = CountRows (Distinct (Sales [LocationDays])) Sales Per Store Per Day = Sum (Sales [Net Sales]) / CountRows (Distinct (Sales [LocationDays]))

І хоча ці нові заходи дають точно такі ж результати для середніх продажів за день в аеропорту або торговому центрі, але в цей раз в стовпці Підсумок Щоденні продажу торгової точки відображається коректне значення середніх продажів за день по кожній торговельній точці.

Мал. 10.35.

Ссылка на основную публикацию