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