Використання обчислюваних стовпців і обчислюваних полів

Під час першого вивчення використання надбудови Power Pivot більшість користувачів виявляють, що реальна сила певною чином об'використовується для агрегації або обчислення результату. Якщо дані містять стовпець із числовими значеннями, їх можна легко агрегувати, вибравши його у зведеній таблиці або списку полів Power View. За природою, оскільки вона числова, її буде автоматично підсумовано, у середньому, підраховано або будь-який тип агрегації, який ви вибираєте. Це називається неявною мірою. Неявні міри чудово підходять для швидкої та простої агрегації, але вони мають обмеження, і ці обмеження майже завжди можна долати явними мірами та обчислюваними стовпцями.

Спочатку розглянемо приклад додавання нового текстового значення для кожного рядка в таблиці "Продукт" за допомогою обчислюваного стовпця. Кожен рядок у таблиці "Продукт" містить усі відомості про кожен продукт, який ми продаємо. У нас є стовпці для "Назва товару", "Колір", "Розмір", "Ціна дилера" тощо. У нас є ще одна пов'язана таблиця з назвою "Категорія товару", яка містить стовпець ProductCategoryName. Ми хочемо, щоб кожен продукт у таблиці "Продукт" включив назву категорії продукту з таблиці "Категорія товару". У таблиці "Продукт" можна створити обчислюваний стовпець "Категорія товару", як описано нижче.

Наша нова формула "Категорія продукту" використовує функцію RELATED DAX, щоб отримати значення зі стовпця ProductCategoryName у пов'язаній таблиці "Категорія товару", а потім вводить ці значення для кожного товару (кожного рядка) у таблицю "Продукт".

Це чудовий приклад того, як за допомогою обчислюваного стовпця можна додати фіксоване значення для кожного рядка, яке можна використовувати пізніше в області "РЯДКИ", "СТОВПЦІ" або "ФІЛЬТРИ" зведеної таблиці або у звіті Power View.

Створимо ще один приклад, у якому потрібно обчислити прибуток для категорій продуктів. Це поширений сценарій, навіть у багатьох підручниках. У нашій моделі даних є таблиця "Збут", яка містить дані про транзакції, і між таблицею "Збут" і таблицею "Категорія товару" існує зв'язок. У таблиці "Продажі" є стовпець із обсягами збуту та інший стовпець із витратами.

Ми можемо створити обчислюваний стовпець, який обчислює суму прибутку для кожного рядка, віднімаючи значення в стовпці COGS від значень у стовпці SalesAmount, наприклад:

Тепер ми можемо створити зведену таблицю та перетягнути поле "Категорія товару" до стовпця СТОВПЦІ, а нове поле "Прибуток" – в область "ЗНАЧЕННЯ" (стовпець у таблиці PowerPivot – це поле в списку полів зведеної таблиці). Результат – це неявна міра під назвою Сума прибутку. Це сукупний обсяг значень зі стовпця прибутку для кожної з різних категорій продуктів. Наш результат має такий вигляд:

У цьому випадку прибуток має сенс лише як поле в аргументі VALUES. Якщо додати прибуток в область СТОВПЦІ, зведена таблиця матиме такий вигляд:

Поле "Прибуток" не надає корисної інформації, якщо її розміщено в областях COLUMNS, ROWS або FILTERS. Це має сенс лише як агреговане значення в області VALUES.

Ми створили стовпець Прибуток, який обчислює прибуток для кожного рядка в таблиці "Продажі". Потім ми додали прибуток до області "ЗНАЧЕННЯ" зведеної таблиці, автоматично створивши неявну міру, де обчислюється результат для кожної з категорій продуктів. Якщо ви думаєте, що ми дійсно обчислюємо прибуток для наших категорій продуктів двічі, ви правильно. Спочатку ми обчислили прибуток для кожного рядка в таблиці "Продажі", а потім додали прибуток до області "ЗНАЧЕННЯ", де його агреговано для кожної з категорій продуктів. Якщо ви також думаєте, що нам насправді не потрібно створювати обчислюваний стовпець Прибуток, ви також правильні. Але як тоді ми обчислюємо прибуток, не створюючи обчислюваний стовпець "Прибуток"?

Прибуток, буде дійсно краще обчислюватися як явна міра.

Наразі ми залишимо свій стовпець "Прибуток" в таблиці "Продажі" та "Категорія товару" в стовпцях "СТОВПЦІ" та "Прибуток у значеннях" зведеної таблиці, щоб порівняти наші результати.

В області обчислень таблиці "Продажі" ми створимо міру "Загальний прибуток"(щоб уникнути конфліктів іменування). Зрештою, він дасть такі ж результати, як і раніше, але без стовпця "Прибуток".

Спочатку в таблиці "Продажі" ми вибираємо стовпець SalesAmount, а потім натискаємо кнопку "Автосума", щоб створити явну міру "Сума продажів". Пам'ятайте, що явна міра – це міра, яку ми створюємо в області обчислень таблиці в надбудові Power Pivot. Ми робимо те ж саме для стовпця COGS. Ми перейменовуємо ці загальний обсяг продажів і загальний обсяг COGS, щоб спростити їх ідентифікацію.

Потім ми створюємо ще одну міру з цією формулою:

Загальний прибуток:=[ Загальний обсяг продажів] – [Загальний обсяг COGS]

Змінивши новий формат виміру "Загальний прибуток" на грошовий, ми можемо додати його до зведеної таблиці.

Нова міра "Загальний прибуток" повертає ті самі результати, що й під час створення обчислюваного стовпця "Прибуток", а потім – у стовпці "ЗНАЧЕННЯ". Відмінність полягає в тому, що загальний прибуток значно ефективніший і робить модель даних чистішою та ощадливішою, оскільки ми обчислюються в той час і лише для полів, вибраних для зведеної таблиці. Зрештою, нам не потрібен обчислюваний стовпець Прибуток.

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

Розгляньмо інший приклад. У такому випадку обчислюваний стовпець створює результати, які на перший погляд виглядають правильно, але....

У цьому прикладі потрібно обчислити обсяги збуту як відсоток від загального обсягу збуту. Ми створюємо обчислюваний стовпець "% продажів " у таблиці "Продажі", наприклад:

У нашій формулі зазначено: Для кожного рядка в таблиці "Продажі" поділіть суму в стовпці SalesAmount на суму SUM усіх обсягів у стовпці SalesAmount.

Якщо ми створимо зведену таблицю та додамо до стовпця СТОВПЦІ категорію продуктів і виберемо новий стовпець "Відсоток продажів ", щоб додати його до значень, ми отримаємо загальну суму % продажів для кожної з наших категорій продуктів.

Гаразд. Це виглядає добре до цих пір. Але додамо роздільник. Ми додаємо календарний рік, а потім вибираємо рік. У цьому випадку ми вибираємо 2007. Це те, що ми отримуємо.

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

У нашому стовпці %of Sales обчислюється відсоток для кожного рядка, який є значенням у стовпці SalesAmount, поділеним на суму всіх значень у стовпці SalesAmount. Значення в обчислюваному стовпці фіксовані. Вони є незмінним результатом для кожного рядка в таблиці. Коли ми додали % продажів до зведеної таблиці, він агрегувався як сума всіх значень у стовпці SalesAmount. Ця сума всіх значень у стовпці %of Sales завжди становитиме 100 %.

Ми можемо видалити наш % обчислюваного стовпця "Продажі", тому що він не допоможе нам. Натомість ми створимо міру, яка правильно обчислює наш відсоток від загального обсягу збуту незалежно від застосованих фільтрів або роздільників.

Пам'ятаєте міру TotalSalesAmount, створену раніше, яка просто підсумовує стовпець SalesAmount? Ми використали його як аргумент у нашому вимірі "Загальний прибуток", і ми будемо використовувати його знову як аргумент у нашому новому обчислюваному полі.

Ми створюємо нову міру з такою формулою:

% від загального обсягу продажів:=([Загальний обсяг продажів]) / CALCULATE([Загальний обсяг продажів], ALLSELECTED())

У цій формулі зазначено: розділіть результат від Total SalesAmount на суму підсумку SalesAmount без будь-яких фільтрів стовпців або рядків, відмінних від визначених у зведеній таблиці.

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

Це виглядає краще. Тепер наш % від загального обсягу продажів для кожної категорії продуктів обчислюється як відсоток від загального обсягу збуту за 2007 рік. Якщо вибрати інший рік або більше одного року в роздільнику CalendarYear, ми отримаємо нові відсотки для наших категорій продуктів, але загальний підсумок все одно становить 100%. Ми також можемо додавати інші роздільники та фільтри. Відсоток від загального обсягу збуту завжди створює відсоток від загального обсягу збуту незалежно від застосованих роздільників або фільтрів. Завдяки мірам результат завжди обчислюється відповідно до контексту, визначеного полями в стовпцях і рядках, а також будь-якими застосованими фільтрами або роздільниками. Це сила заходів.

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

Використання обчислюваних стовпців

• Якщо потрібно, щоб нові дані відображалися в рядках, стовпцях або фільтрах у зведеній таблиці, а також на ОСІ, ЛЕГЕНДІ або ФРАГМЕНТІ У візуалізації Power View, потрібно використовувати обчислюваний стовпець. Так само, як і звичайні стовпці даних, обчислювані стовпці можна використовувати як поле в будь-якій області, і якщо вони числові, їх також можна агрегувати у значеннях VALUES.

• Якщо потрібно, щоб нові дані були фіксованим значенням для рядка. Наприклад, у вас є таблиця дат зі стовпцем дат, і вам потрібен ще один стовпець, який містить лише номер місяця. Ви можете створити обчислюваний стовпець, який обчислює лише номер місяця з дат у стовпці Дата. Наприклад, =MONTH('Date'[Date]).

• Якщо потрібно додати до таблиці текстове значення для кожного рядка, використовуйте обчислюваний стовпець. Поля з текстовими значеннями ніколи не можна агрегувати в аргументі VALUES. Наприклад, =FORMAT('Date'[Date];"mmmm") дає нам назву місяця для кожної дати в стовпці Дата в таблиці "Дата".

Міри використання

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

• Якщо потрібно виконати складніші обчислення, наприклад обчислити кількість на основі фільтра якогось значення або обчислити рік за роком або дисперсію, використовуйте обчислюване поле.

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

Майте на увазі, що не потрібно створювати обчислювані стовпці, як у стовпці "Прибуток", а потім об'єднувати їх у зведеній таблиці або звіті. Це насправді дуже хороший і простий спосіб дізнатися про і створити свої власні розрахунки. Оскільки ви розумієте ці дві надзвичайно потужні функції Надбудови Power Pivot, ви хочете створити найефективнішу та точну модель даних. Сподіваюся, те, що ви дізналися тут, допомагає. Є деякі інші дійсно великі ресурси там, які можуть допомогти вам теж. Нижче наведено лише кілька контекстів у формулах DAX, агрегаціях у надбудові Power Pivot і Центрі ресурсів DAX. Хоча він трохи розширеніший і спрямований на фахівців із бухгалтерського обліку та фінансів, функція моделювання й аналізу даних про прибуток і збитки за допомогою надбудови Microsoft Power Pivot у програмі Excel завантажується з чудовими прикладами моделювання даних і формул.