Агрегування в надбудові Power Pivot

Вибір Групи для агрегації

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

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

Розраховує   Скільки транзакцій було за місяць?

Середні    Які були середні продажі в цьому місяці, продавцем?

Мінімальне та максимальне значення    Які райони продажів були п'ятіркою кращих за обсягом проданих одиниць?

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

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

Вибір функції для агрегації

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

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

Кількість відфільтрованих   Скільки транзакцій було протягом місяця, за винятком вікна обслуговування за кінець місяця?

Співвідношення із середніми значеннями з часом    Яким був відсоток зростання або зниження продажів у порівнянні з аналогічним періодом минулого року?

Згруповані мінімальні та максимальні значення    Які райони збуту були найпопулярнішими для кожної категорії продуктів або для кожної акції збуту?

Додавання груп до зведеної таблиці

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

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

Також можна перетягнути поля до роздільників, щоб вибрати певні групи даних для перегляду. Роздільники дають змогу інтерактивно групувати, сортувати та фільтрувати результати у зведеній таблиці.

Робота з групами у формулі

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

Іншими словами, якщо потрібно створити формулу, яка групує значення за категорією, спочатку слід використати зв'язок, щоб з'єднати таблицю з докладними даними та таблицями, які містять категорії, а потім побудувати формулу.

Докладні відомості про створення формул, які використовують підстановки, див. в статті Підстановки у формулах Power Pivot.

Використання фільтрів у агрегаціях

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

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

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

Докладні відомості див. в статті Фільтрування даних у формулах.

Стандартні функції агрегації

Функції агрегації DAX

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

У таблиці нижче наведено функції агрегації, доступні в DAX.

Відмінності між функціями агрегації DAX і Excel

Хоча ці функції мають ті самі імена, що й їхні колеги з Excel, вони використовують обробник аналітики в пам'яті Power Pivot і були перезаписані для роботи з таблицями та стовпцями. У книзі Excel не можна використовувати формулу DAX і навпаки. Їх можна використовувати лише у вікні Power Pivot та зведених таблицях на основі Power Pivot даних. Крім того, хоча функції мають однакові імена, поведінка може дещо відрізнятися. Докладні відомості див. в статтях, присвячених окремим функціям.

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

Припустімо, потрібно отримати суму значень у стовпці Сума в таблиці Збут, щоб створити таку формулу:

=SUM('Sales'[Amount])

У найпростішому випадку функція отримує значення з одного нефільтрованого стовпця, а результат такий самий, як і в Excel, який завжди просто додає значення в стовпці Сума. Однак у Power Pivot формула інтерпретується як "Отримати значення в полі "Сума" для кожного рядка таблиці "Збут", а потім додати ці окремі значення. Power Pivot обчислює кожен рядок, над яким виконується агрегація, і обчислює одне скалярне значення для кожного рядка, а потім виконує агрегацію цих значень. Таким чином, результат формули може відрізнятися, якщо фільтри застосовано до таблиці або значення обчислюються на основі інших агрегацій, які можуть бути відфільтровані. Докладні відомості див. в статті Контекст у формулах DAX.