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

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

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

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

Това е чудесен пример как можем да използваме изчисляема колона, за да добавим фиксирана стойност за всеки ред, която можем да използваме по-късно в областта ROWS, COLUMNS или FILTERS на обобщената таблица или в отчет на Power View.

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

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

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

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

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

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

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

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

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

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

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

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

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

Можете да видите, че нашата нова мярка "Обща печалба" връща същите резултати като създаването на изчисляема колона "Печалба" и след това я поставя в VALUES. Разликата е, че нашата мярка за обща печалба е много по-ефективна и прави нашия модел на данни по-чист и по-икономичен, защото изчисляваме по това време и само за полетата, които избираме за нашата обобщена таблица. В края на това няма нужда от тази изчисляема колона "Печалба".

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

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

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

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

Ако създадем обобщена таблица и добавим категория продукти към COLUMNS и изберете нашата нова колона % от продажбите, за да я сложим в VALUES, получаваме сума от % от продажбите за всяка от нашите продуктови категории.

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

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

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

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

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

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

% от общите продажби:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

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

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

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

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

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

• Ако искате новите ви данни да се показват във визуализациите ROWS, COLUMNS или FILTERS в обобщена таблица или по ОС, LEGEND или TILE BY във визуализация на Power View, трябва да използвате изчисляема колона. Точно както обикновените колони с данни, изчисляемите колони могат да се използват като поле във всяка област и ако са числови, те също могат да бъдат агрегирани в VALUES.

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

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

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

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

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

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

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