Агрегирания в Power Pivot

Агрегиранията са начин за свиване, обобщаване или групиране на данни. Когато започнете с необработени данни от таблици или други източници на данни, данните често са плоски, което означава, че има много подробности, но не са организирани или групирани по никакъв начин. Тази липса на резюмета или структура може да затрудни откриването на модели в данните. Важна част от моделирането на данни е да се дефинират агрегирания, които опростяват, абстрахират или обобщават модели в отговор на конкретен бизнес въпрос.

Най-често срещаните агрегирания, като например тези, които използват AVERAGE,COUNT, DISTINCTCOUNT,MAX,MIN или SUM , могат да бъдат създадени в мярка автоматично с помощта на Автосумиране. Други типове агрегирания, като например AVERAGEX, COUNTX, COUNTROWS или SUMX, връщат таблица и изискват формула, създадена с помощта на изрази за анализ на данни (DAX).

Разбиране на агрегиранията в Power Pivot

Избор на групи за агрегиране

Когато агрегирате данни, групирате данни по атрибути като продукт, цена, регион или дата и след това дефинирате формула, която работи върху всички данни в групата. Например когато създавате обща сума за година, вие създавате агрегиране. Ако след това създадете съотношение за тази година спрямо предходната година и го представите като проценти, това е различен тип агрегиране.

Решението как да групирате данните се определя от бизнес въпроса. Агрегиранията например могат да отговорят на следните въпроси:

Брой Колко транзакции има за един месец?

Средни стойности Какви са средните продажби по продавачи през този месец?

Минимална и максимална стойност Кои търговски райони са в петте най-големи по брой продадени бройки?

За да създадете изчисление, което отговаря на тези въпроси, трябва да имате подробни данни, които съдържат числата за преброяване или сумиране, и тези числови данни трябва да бъдат свързани по някакъв начин с групите, които ще използвате за организиране на резултатите.

Ако данните все още не съдържат стойности, които можете да използвате за групиране, като например категория на продукта или името на географския регион, в който се намира магазинът, може да искате да въведете групи във вашите данни, като добавите категории. Когато съставяте групи в Excel, трябва ръчно да въведете или изберете групите, които искате да използвате, измежду колоните в работния лист. Обаче в релационната система йерархиите, като например категории за продукти, често се съхраняват в таблица, различна от таблицата с факти или стойности. Обикновено таблицата на категориите е свързана с фактически данни чрез някакъв ключ. Нека например да откриете, че вашите данни съдържат ИД на продуктите, но не и имената на продуктите или техните категории. За да добавите категорията към текстов работен лист на Excel, ще трябва да копирате в колоната, която съдържа имената на категориите. С Power Pivot можете да импортирате таблицата с категориите продукти във вашия модел на данни, да създадете релация между таблицата с числовите данни и списъка с категориите продукти и след това да използвате категориите, за да групирате данни. За повече информация вж. "Създаване на релация между таблици".

Избиране на функция за агрегиране

След като сте идентифицирали и добавили групиранията, които да използвате, трябва да решите кои математически функции да използвате за агрегиране. Често думата агрегиране се използва като синоним на математическите или статистическите операции, които се използват в агрегирането, като например суми, средни стойности, минимална стойност или брой. Power Pivot обаче ви позволява да създавате персонализирани формули за агрегиране в допълнение към стандартните агрегирания в Power Pivot и Excel.

Например ако имате същия набор от стойности и групи, които бяха използвани в предишните примери, бихте могли да създадете агрегирания по избор, които отговарят на следните въпроси:

Филтрирани бройки Колко транзакции има за месеца, с изключение на прозореца за поддръжка в края на месеца?

Коефициенти, използващи средни стойности във времето Какъв е процентният ръст или спад в продажбите в сравнение със същия период на миналата година?

Групирани минимални и максимални стойности Кои търговски райони са класирани на първо място за всяка продуктова категория или за всяка промоция за продажби?

Добавяне на агрегирания към формули и обобщени таблици

Когато имате обща представа за това как трябва да бъдат групирани данните, за да имат смисъл, и стойностите, с които искате да работите, можете да решите дали да създадете обобщена таблица, или да създадете изчисления в таблица. Power Pivot разширява и подобрява вградените възможности на Excel за създаване на агрегирания, като например суми, брой или средни стойности. Можете да създавате агрегирания по избор в Power Pivot или в прозореца на Power Pivot, или в областта на обобщената таблица на Excel.

• В изчисляема колона можете да създавате агрегирания, които вземат предвид контекста на текущия ред, за да извлечете свързани редове от друга таблица, и след това да сумирате, преброявате или осреднявате тези стойности в свързаните редове.
• Като мярка можете да създавате динамични агрегирания, които използват както филтри, дефинирани във формулата, така и филтри, наложени от дизайна на обобщената таблица и избора на сегментатори, заглавия на колони и заглавия на редове. Мерките, използващи стандартни агрегирания, могат да бъдат създадени в Power Pivot с помощта на автосумиране или чрез създаване на формула. Можете също да създадете неявни мерки с помощта на стандартни агрегирания в обобщена таблица в Excel.

Добавяне на групирания в обобщена таблица

Когато проектирате обобщена таблица, плъзгате полета, които представляват групирания, категории или йерархии, в секцията за колони и редове на обобщената таблица, за да групирате данните. След това плъзгате полета, съдържащи числови стойности, в областта на стойностите, така че да могат да бъдат преброени, усреднени или сумирани.

Ако добавите категории към обобщена таблица, но данните на категорията не са свързани с фактическите данни, може да получите съобщение за грешка или странни резултати. Обикновено Power Pivot ще се опита да поправи проблема, като открива и предлага релации автоматично. За повече информация вж. "Работа с релации в обобщени таблици".

Можете също да плъзгате полета в сегментатори, за да изберете определени групи данни за преглеждане. Сегментаторите ви позволяват интерактивно да групирате, сортирате и филтрирате резултатите в обобщена таблица.

Работа с групирания във формула

Можете също да използвате групирания и категории за агрегиране на данните, които се съхраняват в таблици, като създадете релации между таблиците и след това създадете формули, които използват тези релации за търсене на свързани стойности.

С други думи, ако искате да създадете формула, която групира стойностите по категория, първо ще използвате релация, за да свържете таблицата, съдържаща подробните данни, и таблиците, съдържащи категориите, а след това ще създадете формулата.

За повече информация как да създавате формули, използващи справки, вж. "Справки във формули на Power Pivot".

Използване на филтри в агрегирания

Една нова функция в Power Pivot е възможността да прилагате филтри към колони и таблици с данни не само в потребителския интерфейс и в рамките на обобщена таблица или диаграма, но също и в самите формули, които използвате за изчисляване на агрегиранията. Филтрите могат да се използват във формули както в изчисляеми колони, така и в s.

Например в новите агрегатни функции на DAX, вместо да задавате стойности, които да бъдат сумирани или преброени, можете да зададете като аргумент цяла таблица. Ако не приложите никакви филтри към тази таблица, агрегатната функция ще работи спрямо всички стойности в указаната колона на таблицата. В DAX обаче можете да създадете или динамичен, или статичен филтър в таблицата, така че агрегирането да работи спрямо различно подмножество от данни в зависимост от условията на филтриране и текущия контекст.

Чрез комбиниране на условия и филтри във формули можете да създавате агрегирания, които се променят в зависимост от стойностите, предоставени във формулите, или които се променят в зависимост от избора на редове, заглавия и заглавия на колони в обобщена таблица.

За повече информация вж. "Филтриране на данни във формули".

Сравнение на агрегатните функции на Excel и агрегатните функции на DAX

Следващата таблица съдържа някои от стандартните агрегатни функции, предоставяни от Excel, и предоставя връзки към реализацията на тези функции в Power Pivot. DAX версията на тези функции се държи почти по същия начин като версията на Excel, с някои малки разлики в синтаксиса и обработката на определени типове данни.

Standard Aggregation Functions

Функция	Използвайте
AVERAGE	Връща средната стойност (средноаритметичното) на всички числа в дадена колона.
AVERAGEA	Връща средната стойност (средноаритметичното) на всички стойности в дадена колона. Обработва текстови и нечислови стойности.
COUNT	Преброява числовите стойности в колона.
COUNTA	Преброява стойностите в колона, които не са празни.
MAX	Връща най-голямата числова стойност в колона.
MAXX	Връща най-голямата стойност от набор изрази, изчислени в таблица.
MIN	Връща най-малката числова стойност в колона.
MINX	Връща най-малката стойност от набор изрази, изчислени в таблица.
SUM	Сумира всички числа в дадена колона.

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

DAX съдържа агрегатни функции, които ви позволяват да зададете таблица, върху която да се извършва агрегирането. Следователно вместо просто да събирате или усреднявате стойностите в колона, тези функции ви позволяват да създадете израз, който динамично дефинира данните за агрегиране.

Следната таблица изброява агрегатните функции, които са налични в DAX.

Функция	Използвайте
AVERAGEX	Намира средната стойност на набор от изрази, изчислени в таблица.
COUNTAX	Преброява набор от изрази, изчислени в таблица.
COUNTBLANK	Преброява празните стойности в колона.
COUNTX	Преброява общия брой редове в таблица.
COUNTROWS	Пресмята броя на редовете, върнати от функция на вложена таблица, например функция filter.
SUMX	Връща сумата от набор от изрази, изчислени в таблица.

Разлики между агрегатните функции на DAX и Excel

Въпреки че тези функции имат същите имена като техните аналоги в Excel, те използват системата за анализ в паметта на Power Pivot и са пренаписани за работа с таблици и колони. Не можете да използвате DAX формула в работна книга на Excel и обратно. Те могат да се използват само в прозореца на Power Pivot и в обобщени таблици, които се базират на данни на Power Pivot. Освен това, въпреки че функциите имат идентични имена, поведението им може да е малко по-различно. За повече информация вижте справочните теми за отделните функции.

Начинът, по който колоните се оценяват в агрегиране, също е различен от начина, по който Excel обработва агрегиранията. Един пример може да помогне за илюстрирането.

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

=SUM('Sales'[Amount])

В най-простия случай функцията получава стойностите от единична нефилтрирана колона и резултатът е същият като в Excel, който винаги само събира стойностите в колоната "Количество". В Power Pivot обаче формулата се интерпретира като "Вземете стойността в Quantity за всеки ред от таблицата "Продажби" и след това сумирайте тези отделни стойности. Power Pivot оценява всеки ред, над който се извършва агрегирането, изчислява една скаларна стойност за всеки ред и след това извършва агрегиране на тези стойности. Следователно резултатът от формулата може да бъде различен, ако към таблица са приложени филтри или ако стойностите са изчислени въз основа на други агрегирани, които може да бъдат филтрирани. За повече информация вж. "Контекст във формули на DAX".

Функции за интелигентно време на DAX

В допълнение към функциите за агрегиране на таблици, описани в предишния раздел, DAX има агрегатни функции, които работят със зададени от вас дати и часове, за да предоставят вградено интелигентно време. Тези функции използват диапазони от дати, за да получат свързани стойности и да агрегират стойностите. Можете също да сравнявате стойности в различни диапазони от дати.

Следващата таблица съдържа функциите за интелигентно време, които могат да се използват за агрегиране.

Функция	Използвайте
CLOSINGBALANCEMONTH ЗАТВАРЯНЕ НА БАЛАНСАТРИМЕСЕЧИЕ CLOSINGBALANCEYEAR	Изчислява стойност в края на календара на даден период.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Изчислява стойност в края на календарния период преди дадения период.
ОБЩОMTD TOTALYTD ОБЩОQTD	Изчислява стойност за интервала, който започва в първия ден на периода и завършва на най-късната дата в колоната за указана дата.

Другите функции в раздела "Функция за интелигентно време" (Функции за интелигентно време) са функции, които могат да се използват за извличане на дати или диапазони по избор от дати, които да се използват в агрегиране. Можете например да използвате функцията DATESINPERIOD, за да върнете диапазон от дати, и да използвате този набор от дати като аргумент на друга функция, за да изчислите агрегиране по избор само за тези дати.