Вперше навчаючись використовувати надбудову Power Pivot, більшість користувачів усвідомлюють, що справжня сила полягає в тому, щоб агрегувати або обчислювати результат певним чином. Якщо дані містять стовпець із числовими значеннями, його можна легко агрегувати, вибравши у зведеній таблиці або списку полів Power View. За своєю природою, оскільки це числове значення, його буде автоматично підсумовуватися, усереднюватися, підраховуватися або інший вибраний тип агрегування. Це називається неявною мірою. Неявні міри дуже корисні для швидкого та легкого агрегування, але вони мають обмеження, які майже завжди можна подолати за допомогою явних мір і обчислюваних стовпців.
Спочатку розгляньмо приклад, у якому обчислюваний стовпець додається нове текстове значення до кожного рядка таблиці "Product". Кожен рядок таблиці товарів містить різноманітні відомості про кожен продукт, який ми продаємо. У нас є стовпці «Назва товару», «Колір», «Розмір», «Дилерська ціна» тощо. У нас є ще одна пов'язана таблиця "Категорія продуктів", яка містить стовпець "Назва_категорії_продукту". Нам потрібно, щоб кожен товар у таблиці "Продукт" включав назву категорії товару з таблиці "Категорія товарів". У таблиці "Товари" можна створити обчислюваний стовпець з іменем "Категорія продуктів" таким чином:
Наша нова формула "Категорія продукту" використовує функцію RELATED DAX, щоб отримати значення зі стовпця ProductCategoryName у пов'язаній таблиці "Категорія продуктів", а потім ввести ці значення для кожного товару (кожного рядка) таблиці "Продукт".
Це чудовий приклад того, як можна за допомогою обчислюваного стовпця додати фіксоване значення до кожного рядка, яке можна використати згодом в області "РЯДКИ", "СТОВПЦІ" або "ФІЛЬТРИ" зведеної таблиці чи у звіті Power View.
Створімо ще один приклад, у якому потрібно обчислити норму прибутку для категорій товарів. Це типовий сценарій, навіть у багатьох навчальних посібниках. У нашій моделі даних є таблиця "Збут" із даними про транзакції, між таблицями "Збут" і "Категорія продуктів" є зв'язок. У таблиці "Збут" є стовпець, у якому вказано обсяг продажів, і ще один стовпець про витрати.
Можна створити обчислюваний стовпець, який обчислює суму прибутку для кожного рядка, віднявши значення в стовпці COGS від значень у стовпці SalesAmount. Ось так:
Тепер можна створити зведену таблицю та перетягнути поле "Категорія продукту" до стовпців, а нове поле "Прибуток" – до області "ЗНАЧЕННЯ" (стовпець у таблиці в PowerPivot – це поле в списку полів зведеної таблиці). Результатом є неявна міра під назвою "Сума прибутку". Це сукупна кількість значень зі стовпця "Прибуток" для кожної категорії товарів. Наш результат виглядає так:
У цьому випадку значення "Прибуток" має сенс лише як поле в функції VALUES. Якщо помістити поле "Прибуток" в область "СТОВПЦІ", зведена таблиця виглядатиме так:
Поле "Прибуток" не надає жодної корисної інформації, якщо його розміщено в областях "СТОВПЦІ", "РЯДКИ" або "ФІЛЬТРИ". Воно має сенс тільки як агрегатне значення в області "ЗНАЧЕННЯ".
Ми створили стовпець "Прибуток", у якому обчислюється коефіцієнт прибутку для кожного рядка в таблиці "Продаж". Потім ми додали значення "Прибуток" до області "ЗНАЧЕННЯ" зведеної таблиці, автоматично створивши неявну міру, де для кожної категорії продуктів обчислюється результат. Якщо ви думаєте, що ми дійсно двічі розрахували прибуток для наших категорій товарів, ви маєте рацію. Спочатку ми обчислили прибуток для кожного рядка в таблиці "Збут", а потім додали прибуток до області "ЗНАЧЕННЯ", у якій він агрегувався для кожної категорії продуктів. Якщо ви думаєте, що створювати стовпець обчислення "Прибуток" не потрібно, ви маєте рацію. Але як тоді обчислити прибуток, не створюючи стовпець обчислення прибутку?
Прибуток, дійсно, краще було б обчислити як явну міру.
Наразі ми залишимо стовпець "Обчислений прибуток" у таблиці "Збут" і "Категорія продукту" в розділі "СТОВПЦІ", а стовпець "Прибуток" у розділі "ЗНАЧЕННЯ" зведеної таблиці, щоб порівняти результати.
В області обчислення таблиці "Продажі" буде створено показник " Загальний прибуток " (щоб уникнути конфліктів назв). Зрештою, це дасть ті ж результати, що й раніше, але без стовпця обчислення прибутку.
Спочатку в таблиці "Продажі" вибираємо стовпець "Обсяг_продажів" і натискаємо кнопку "Автосума", щоб створити явний показник "Сума обсягу продажів ". Пам'ятайте, що явна міра – це міра, яка створюється в області обчислення таблиці в надбудові Power Pivot. Те ж саме робимо для стовпчика COGS. Ми перейменуємо ці поля Total SalesAmount і Total COGS , щоб їх було легше ідентифікувати.
Потім створимо іншу міру за такою формулою:
Total Profit:=[Total SalesAmount] – [Total COGS]
Примітка.
Ми також можемо записати нашу формулу як Total Profit:=SUM([SalesAmount]) - SUM([COGS]), але, створивши окремі міри Total SalesAmount і Total COGS, ми можемо використовувати їх також у зведеній таблиці та використовувати як аргументи в багатьох інших формулах мір.
Змінивши новий формат міри загального прибутку на грошову, ми можемо додати її до зведеної таблиці.
Ви можете побачити, що наш новий показник "Загальний прибуток" повертає такі ж результати, що й при створенні стовпця обчислення "Прибуток" і його розташуванні в полі "ЗНАЧЕННЯ". Відмінність полягає в тому, що показник загального прибутку набагато ефективніший і робить модель даних чистішою та економнішою, оскільки ми проводимо обчислення в момент і лише для полів, вибраних у зведеній таблиці. Зрештою, цей стовпець обчислення прибутку нам не потрібен.
Чому ця остання частина важлива? Обчислювані стовпці додають дані до моделі даних, а дані займають пам'ять. Якщо оновити модель даних, для переобчислення всіх значень у стовпці "Прибуток" знадобляться ресурси обробки. Нам не потрібно використовувати такі ресурси, тому що нам дуже потрібно обчислити прибуток, коли ми вибираємо потрібні поля в зведеній таблиці, наприклад категорії продуктів, регіон або за датами.
Розглянемо ще один приклад. Такий, де обчислюваний стовпець створює результати, які на перший погляд виглядають правильними, але....
У цьому прикладі ми хочемо обчислити обсяги продажів як відсоток від загального обсягу збуту. Ми створимо обчислюваний стовпець із назвою " % від продажів " у таблиці "Продажі", як-от:
Формула має такий вигляд: Для кожного рядка в таблиці "Продажі" поділіть суму в стовпці "Обсяг_продажів" на загальний підсумок у стовпці "Обсяг продажів".
Якщо створити зведену таблицю, додати категорію продукту до стовпців і вибрати новий стовпець " % від збуту ", щоб помістити його в таблицю "ЗНАЧЕННЯ", ми отримаємо загальний показник "% від продажів" для кожної категорії товарів.
Гаразд. Поки що це виглядає непогано. Але давайте додамо роздільник. Додаємо CalendarCalendar Year і вибираємо рік. У цьому випадку вибираємо 2007. Ось що у нас виходить.
На перший погляд це все одно може здатися правильним. Але наші відсотки повинні становити 100%, тому що ми хочемо знати відсоток від загального обсягу продажів для кожної з наших категорій продуктів за 2007 рік. Що ж пішло не так?
У нашому стовпці "% від продажів" обчислюється відсоток для кожного рядка, тобто значення стовпця "Обсяг продажів", поділене на загальний підсумок усіх значень у стовпці "Обсяг продажів". Значення в обчислюваних стовпцях фіксовані. Вони є незмінним результатом для кожного рядка в таблиці. Коли ми додали % від даних "Продажі " до зведеної таблиці, їх було об'єднано як суму всіх значень у стовпці "Обсяг продажів". Сума всіх значень у стовпці «% від продажів» завжди дорівнює 100%.
Порада.
Обов'язково прочитайте контекст у формулах DAX. Він забезпечує чітке розуміння контексту на рівні рядка та контексту фільтра, що ми тут описуємо.
Можна видалити стовпець обчислюваного "% від продажів", тому що це нам не допоможе. Натомість ми створимо міру, яка правильно обчислюватиме відсоток від загального обсягу продажів незалежно від застосованих фільтрів чи роздільників.
Пам'ятаєте раніше створений вимір TotalSalesAmount, який просто підсумовує стовпець SalesAmount? Ми використовували її як аргумент в мірі загального прибутку та будемо використовувати знову як аргумент у нашому новому обчислюваному полі.
Порада.
Явні міри, як-от "ЗагальнийОбсяг Продажів" і "Загальний показник COGS", не лише корисні самі по собі у зведеній таблиці або звіті, але й як аргументи в інших вимірах, коли результат потрібен як аргумент. Це робить формули ефективнішими та зручнішими для читання. Це належна практика моделювання даних.
Створимо нову міру за такою формулою:
% від загального обсягу продажів:=([Загальний обсяг продажів]) / CALCULATE([Загальний обсяг продажів], ALLSELECTED())
Формула говорить: Поділіть результат із поля Total SalesAmount на значення sum of SalesAmount, не використовуючи фільтри стовпців або рядків, окрім тих, що визначено у зведеній таблиці.
Порада.
Обов'язково прочитайте про функції CALCULATE і ALLSELECTED у довіднику DAX.
Тепер, якщо ми додамо наш новий % від загального обсягу продажів до зведеної таблиці, отримаємо:
Так виглядає краще. Тепер наш % від загального обсягу продажів для кожної категорії продуктів обчислюється як відсоток від загального обсягу продажів за 2007 рік. Якщо в роздільнику CalendarYear вибрати інший рік або кілька років, ми отримаємо нові відсотки для наших категорій продуктів, але загальний підсумок усе одно дорівнює 100%. Можна також додати інші роздільники та фільтри. Показник ''% від загального обсягу продажів'' завжди обчислюватиметься у відсотках від загального обсягу продажів незалежно від застосованих роздільників або фільтрів. За допомогою мір результат завжди обчислюється відповідно до контексту, визначеного полями в рядках і стовпцях, а також застосованими фільтрами чи роздільниками. У цьому сила заходів.
Нижче наведено кілька порад, які допоможуть вам вирішити, чи підходить обчислюваний стовпець або міра для певних потреб обчислення.
Використання обчислюваних стовпців
- Щоб нові дані відображалися в рядках, стовпцях, ФІЛЬТРАХ зведеної таблиці чи на осі, легенді чи фрагменті у візуалізації Power View, потрібно використовувати обчислюваний стовпець. Так само, як і звичайні стовпці даних, обчислювані стовпці можна використовувати як поле в будь-якій області, а якщо вони числові, їх також можна агрегувати в ЗНАЧЕННЯХ.
- Якщо потрібно, щоб у рядку було фіксоване значення. Наприклад, є таблиця дат зі стовпцем дат, а ще один стовпець бажаний містить лише номер місяця. Ви можете створити обчислюваний стовпець, який обчислює лише номер місяця на основі дат у стовпці Date. Наприклад, =MONTH('Date'[Date]).
- Щоб додати текстове значення для кожного рядка до таблиці, використовуйте обчислюваний стовпець. Поля з текстовими значеннями не можна агрегувати в речення VALUES. Наприклад, =FORMAT('Date'[Date],"mmmm") дає нам назву місяця для кожної дати в стовпці "Дата" в таблиці дат.
Міри використання
- Якщо результат обчислення завжди залежатиме від інших полів, вибраних у зведеній таблиці.
- Використовуйте обчислюване поле, якщо потрібно виконати складніші обчислення, наприклад обчислити кількість на основі певного фільтра або обчислити рік або відхилення.
- Якщо потрібно звести розмір книги до мінімуму та максимально збільшити її продуктивність, створіть якомога більше мір обчислення. У багатьох випадках усі обчислення можна виміряти, що значно зменшує розмір книги та прискорює оновлення.
Майте на увазі: немає нічого поганого в тому, щоб створити обчислювані стовпці, як це було зроблено з нашим стовпцем "Прибуток", а потім об'єднати їх у зведену таблицю або звіт. Це насправді хороший і простий спосіб дізнатися про власні обчислення та створити їх. У міру того як ви розумітимете ці дві надзвичайно потужні функції Power Pivot, вам знадобиться створити якомога ефективнішу й точнішу модель даних. Сподіваюся, те, що ви дізналися тут, допоможе. Є й інші справді чудові ресурси, які можуть допомогти і вам. Ось лише деякі з них: контекст у формулах DAX, агрегації в Power Pivot і Центр ресурсів DAX. Незважаючи на те, що зразок моделювання та аналізу даних про прибуток і збитки за допомогою надбудови Microsoft Power Pivot в Excel трохи складніший і орієнтований на фахівців у галузі бухгалтерського обліку та фінансів, містить багато прикладів чудових формул і моделювання даних.