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