Кога да използвате изчисляеми колони и изчисляеми полета

Когато се научат за първи път как да използват Power Pivot, повечето потребители откриват, че реалната сила е в агрегиране или изчисляване на резултат по някакъв начин. Ако данните ви разполагат с колона с числови стойности, можете лесно да ги агрегирате, като ги изберете в обобщена таблица или списък с полета на Power View. По природа, тъй като е число, той автоматично ще бъде сумиран, осреднен, преброен или какъвто тип агрегиране изберете. Това е известно като неявна мярка. Неявните мерки са чудесни за бързо и лесно събиране, но имат ограничения и тези ограничения почти винаги могат да бъдат преодолени с изрични мерки и изчисляеми колони.

Нека първо да разгледаме пример, в който използваме изчисляема колона, за да добавим нова текстова стойност за всеки ред в таблица с име "Продукт". Всеки ред в таблицата "Продукт" съдържа всякакъв вид информация за всеки продукт, който продаваме. Имаме колони за Име на продукт, Цвят, Размер, Цена на дилър и т.н. Имаме друга свързана таблица с име "Категория продукти", която съдържа колона ProductCategoryName. Това, което искаме, е за всеки продукт в таблицата "Продукт" да включва името на категорията продукти от таблицата "Категория продукти". В нашата таблица "Продукт" можем да създадем изчисляема колона с име "Категория на продукта" по следния начин:

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

Това е чудесен пример как можем да използваме изчисляема колона, за да добавим фиксирана стойност за всеки ред, който можем да използваме по-късно в областта РЕДОВЕ, КОЛОНИ или ФИЛТРИ на обобщена таблица или в отчет на Power View.

Нека създадем друг пример, в който искаме да изчислим марж на печалбата за нашите категории продукти. Това е често срещан сценарий, дори и в много уроци. Имаме таблица "Продажби" в нашия модел на данни, която съдържа данни за транзакции, и има релация между таблицата "Продажби" и таблицата "Категория продукти". В таблицата "Продажби" имаме колона, която има суми на продажби, и друга колона, която има разходи.

Можем да създадем изчисляема колона, която изчислява сума на печалбата за всеки ред, като изваждаме стойностите в колоната COGS от стойностите в колоната SalesAmount, ето така:

Сега можем да създадем обобщена таблица и да плъзнем полето "Категория на продукта" в "КОЛОНИ", а новото ни поле "Печалба" в областта "СТОЙНОСТИ" (колона в таблица в PowerPivot е поле в списъка с полета на обобщена таблица). Резултатът е неявна мярка, наречена Сума на печалбата. Това е агрегирано количество стойности от колоната "Печалба" за всяка от различните категории продукти. Нашият резултат изглежда така:

В този случай "Печалба" има смисъл само като поле в VALUES. Ако поставим Печалба в областта КОЛОНИ, нашата обобщена таблица ще изглежда така:

Нашето поле Печалба не предоставя полезна информация, когато се поставя в областите КОЛОНИ, РЕДОВЕ или ФИЛТРИ. Има смисъл само като агрегирана стойност в областта СТОЙНОСТИ.

Това, което направихме, е да създадете колона с име Печалба, която изчислява марж на печалбата за всеки ред в таблицата "Продажби". След това добавихме Печалба в областта НА СТОЙНОСТИТЕ на нашата обобщена таблица, като автоматично създадохме неявна мярка, където се изчислява резултат за всяка от категориите продукти. Ако мислите, че ние изчисляваме печалбата за нашите категории продукти два пъти, сте прави. Първо изчислихме печалба за всеки ред в таблицата "Продажби" и след това добавихме Печалба в областта СТОЙНОСТИ, където е агрегирана за всяка от категориите продукти. Ако също така мислите, че не е необходимо да създаваме изчисляемата колона Печалба, също така сте правилни. Но как тогава изчисляваме нашата печалба, без да създаваме изчисляема колона Печалба?

Печалбата наистина би била по-добра като изрична мярка.

За момента ще оставим нашата изчисляема колона Печалба в таблицата "Продажби" и "Категория продукти" в КОЛОНИ и Печалба в СТОЙНОСТИТЕ на нашата обобщена таблица, за да сравним нашите резултати.

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

Първо, в таблицата "Продажби" избираме колоната SalesAmount и след това щракваме върху "Автосумиране", за да създадем изрична сума на сумата на продажбите. Не забравяйте, че изричната мярка е тази, която създаваме в областта за изчисляване на таблица в Power Pivot. Правим същото за колоната "COGS". Ще преименуваме тези Total SalesAmount и Total COGS , за да ги направим по-лесни за идентифициране.

След това създаваме друга мярка с тази формула:

Обща печалба:=[ Обща сума на продажбите] – [Общо COGS]

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

Можете да видите нашата нова мярка за обща печалба връща същите резултати като създаването на изчисляема колона Печалба и след това поставянето й в СТОЙНОСТИ. Разликата е нашата мярка за общата печалба е далеч по-ефективна и прави модела на данните по-изчистен и опростен, защото в момента изчисляваме и само за полетата, които избираме за нашата обобщена таблица. Всъщност не ни е нужна изчисляемата колона Печалба в края на краищата.

Защо е важна последната част? Изчисляемите колони добавят данни към модела на данни и данните заемат памет. Ако обновим модела на данни, ресурсите за обработка са необходими и за преизчисляване на всички стойности в колоната Печалба. Не е нужно наистина да приемаме ресурси като този, защото наистина искаме да изчислим нашата печалба, когато изберем полетата, за които искаме Печалба в обобщената таблица, като например категории продукти, регион или по дати.

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

В този пример искаме да изчислим сумите на продажбите като процент от общите продажби. Създаваме изчисляема колона с име % от продажбите в нашата таблица "Продажби", ето така:

Нашата формула гласи: За всеки ред в таблицата "Продажби" разделете сумата в колоната SalesAmount на сумата на всички суми в колоната SalesAmount.

Ако създадем обобщена таблица и добавим Категория на продукта към КОЛОНИ и изберем нашата нова колона % от продажбите , за да я поставим в СТОЙНОСТИ, ще получим общата сума на % от продажбите за всяка от нашите категории продукти.

Ok. Това изглежда добре досега. Но нека добавим сегментатор. Добавяме Календарна година и след това избираме година. В този случай избираме 2007. Това получаваме ние.

На пръв поглед това все още може да изглежда правилно. Но нашите проценти би трябвало наистина да са общо 100%, защото искаме да знаем процента от общите продажби за всяка от нашите продуктови категории за 2007 г. Какво се обърка?

Нашата колона %of Sales изчислява процент за всеки ред, който е стойността в колоната SalesAmount, разделена на общата сума на всички стойности в колоната SalesAmount. Стойностите в изчисляема колона са фиксирани. Те са непроменяем резултат за всеки ред в таблицата. Когато добавихме % от продажбите към нашата обобщена таблица, тя беше агрегирана като сума от всички стойности в колоната SalesAmount. Тази сума от всички стойности в колоната " % от продажбите" винаги ще бъде 100%.

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

Помните ли мярката TotalSalesAmount, която създадохме по-рано, тази, която просто сумира колоната SalesAmount? Използвахме го като аргумент в нашата мярка за общата печалба и ще я използваме отново като аргумент в новото изчисляемо поле.

Създаваме нова мярка със следната формула:

% от общите продажби:=([Обща сума на продажбите]) / CALCULATE([Общо продажбиAmount], ALLSELECTED())

Тази формула гласи: Разделете резултата от Total SalesAmount на сумата на SalesAmount без никакви филтри за колони или редове, различни от тези, дефинирани в обобщената таблица.

Сега, ако добавим нашия нов % от общите продажби към обобщената таблица, получаваме:

Това изглежда по-добре. Сега нашият % от общите продажби за всяка категория продукти се изчислява като процент от общите продажби за 2007 година. Ако изберем различна година или повече от една година в сегментатора CalendarYear, ще получим нови проценти за нашите категории продукти, но общата ни сума все още е 100%. Можем да добавим и други сегментатори и филтри. Нашият % от мярката "Общо продажби" винаги ще произвежда процент от общите продажби, независимо от всички сегментатори или филтри, които сте приложили. С мерките резултатът винаги се изчислява според контекста, определен от полетата в COLUMNS и ROWS, както и от всички приложени филтри или сегментатори. Това е силата на мерките.

Ето няколко указания, които да ви помогнат, когато решавате дали дадена изчисляема колона или мярка е подходяща за определена нужда от изчисление:

Използване на изчисляеми колони

• Ако искате новите ви данни да се показват във "РЕДОВЕ", "КОЛОНИ" или "ФИЛТРИ" в обобщена таблица или по ОС, ЛЕГЕНДА или "ПОДРЕЖДАНЕ ПО" във визуализация на Power View, трябва да използвате изчисляема колона. Точно както обикновените колони с данни, изчисляемите колони могат да се използват като поле във всяка област, а ако са числови, те могат също да бъдат агрегирани в СТОЙНОСТИ.

• Ако искате новите ви данни да бъдат фиксирана стойност за реда. Например имате таблица с дати с колона с дати и искате друга колона, която съдържа само номера на месеца. Можете да създадете изчисляема колона, която изчислява само номера на месеца от датите в колоната "Дата". Например =MONTH('Date'[Date]).

• Ако искате да добавите текстова стойност за всеки ред в таблица, използвайте изчисляема колона. Полетата с текстови стойности никога не могат да се агрегират в VALUES. Например =FORMAT('Date'[Date],"mmmm") ни дава името на месеца за всяка дата в колоната Date в таблицата Date.

Използване на мерки

• Ако резултатът от изчислението винаги ще зависи от другите полета, които изберете в обобщена таблица.

• Ако трябва да извършвате по-сложни изчисления, като например изчисляване на брой на базата на някакъв вид филтър или изчисляване на година по година или дисперсия, използвайте изчисляемо поле.

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

Имайте предвид, че няма нищо лошо в създаването на изчисляеми колони, както направихме с нашата колона Печалба, и след това агрегирането й в обобщена таблица или отчет. Всъщност това е наистина добър и лесен начин да научите и да създадете свои собствени изчисления. Тъй като вашето разбиране на тези две изключително мощни функции на Power Pivot нараства, ще искате да създадете най-ефективния и точен модел на данни, който можете. Надяваме се, че това, което научихте тук, ще ви помогне. Има някои други наистина страхотни ресурси, които също могат да ви помогнат. Ето само няколко: Контекст в DAX формулите, агрегирания в Power Pivot и центъра за ресурси на DAX. И макар че е малко по-напреднал и е насочен към професионалисти в областта на счетоводството и финансите, моделирането и анализът на данни за печалби и загуби с microsoft Power Pivot в Excel се зарежда с чудесни примери за моделиране на данни и формули.