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

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

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

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

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

Середні значення    Які середні обсяги продажів за цей місяць продавець?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Агрегатні функції DAX

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

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

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

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

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

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

=SUM('Sales'[Amount])

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