Když se poprvé naučíte používat Power Pivot, většina uživatelů zjistí, že skutečná síla spočívá v agregaci nebo výpočtu výsledku nějakým způsobem. Pokud data obsahují sloupec s číselnými hodnotami, můžete ho snadno agregovat tak, že ho vyberete v kontingenční tabulce nebo v seznamu polí Power View. Vzhledem k tomu, že se jedná o číselnou hodnotu, bude automaticky sečíst, zprůměrovat, spočítat nebo jakýkoli typ agregace, kterou vyberete. To se označuje jako implicitní míra. Implicitní míry jsou skvělé pro rychlou a jednoduchou agregaci, ale mají limity a tyto limity lze téměř vždy překonat explicitními mírami a počítanými sloupci.
Nejprve se podíváme na příklad, kdy pomocí počítaný sloupec přidáme novou textovou hodnotu pro každý řádek v tabulce s názvem Product. Každý řádek v tabulce Product obsahuje nejrůznější informace o jednotlivých produktech, které prodáváme. Máme sloupce Product Name (Název produktu), Color (Barva), Size (Velikost), Dealer Price (Cena prodejce) atd. Máme další související tabulku s názvem Kategorie produktu, která obsahuje sloupec ProductCategoryName. Chceme, aby každý produkt v tabulce Product obsahoval název kategorie produktu z tabulky Kategorie produktů. V naší tabulce Product (Produkt) můžeme vytvořit počítaný sloupec s názvem Product Category (Kategorie produktu):
Náš nový vzorec Kategorie produktu používá funkci RELATED JAZYKA DAX k získání hodnot ze sloupce ProductCategoryName v související tabulce Product Category a pak tyto hodnoty zadá pro každý produkt (každý řádek) v tabulce Product.
Toto je skvělý příklad toho, jak můžeme pomocí počítaného sloupce přidat pevnou hodnotu pro každý řádek, kterou můžeme použít později v oblasti ŘÁDKY, SLOUPCE nebo FILTRy kontingenční tabulky nebo v sestavě Nástroje Power View.
Pojďme vytvořit další příklad, ve kterém chceme vypočítat ziskovou marži pro naše kategorie produktů. To je běžný scénář, a to i v mnoha kurzech. V našem datovém modelu máme tabulku Sales(Prodej), která obsahuje data transakcí, a mezi tabulkou Sales (Prodej) a tabulkou Product Category (Kategorie produktu) existuje relace. V tabulce Sales máme sloupec, který obsahuje částky prodeje, a další sloupec s náklady.
Můžeme vytvořit počítaný sloupec, který vypočítá výši zisku pro každý řádek tak, že odečte hodnoty ve sloupci COGS od hodnot ve sloupci SalesAmount, například takto:
Teď můžeme vytvořit kontingenční tabulku a přetáhnout pole Kategorie produktu na SLOUPCE a naše nové pole Zisk do oblasti HODNOTY (sloupec v tabulce v PowerPivotu je pole v seznamu polí kontingenční tabulky). Výsledkem je implicitní míra s názvem Součet zisku. Jedná se o agregované množství hodnot ze sloupce zisku pro každou z různých kategorií produktů. Výsledek vypadá takto:
V tomto případě má profit smysl pouze jako pole v hodnotě HODNOTY. Kdybychom do oblasti SLOUPCE umístili Zisk, vypadala by kontingenční tabulka takto:
Pole Zisk neposkytuje žádné užitečné informace, když je umístěné v oblastech SLOUPCE, ŘÁDKY nebo FILTRY. Dává smysl pouze jako agregovaná hodnota v oblasti HODNOTY.
Co jsme udělali, je vytvořit sloupec s názvem Zisk, který vypočítá ziskovou marži pro každý řádek v tabulce Sales. Potom jsme do oblasti HODNOTY v kontingenční tabulce přidali profit a automaticky jsme vytvořili implicitní míru, ve které se vypočítá výsledek pro každou z kategorií produktů. Pokud si myslíte, že jsme zisk pro naše kategorie produktů vypočítali dvakrát, máte pravdu. Nejprve jsme vypočítali zisk pro každý řádek v tabulce Sales (Prodej) a pak jsme přidali Profit (Zisk) do oblasti VALUES, kde byl agregován pro každou kategorii produktů. Pokud si také myslíte, že jsme ve skutečnosti nemuseli vytvořit počítaný sloupec Profit, máte také pravdu. Jak pak ale vypočítáme zisk, aniž bychom vytvořili počítaný sloupec Profit?
Zisk, by se ve skutečnosti lépe počítaly jako explicitní míra.
Prozatím necháme náš počítaný sloupec Profit (Zisk) v tabulce Sales (Prodej) a Product Category (Kategorie produktu) v části COLUMNS (Sloupce) a Profit (Zisk) v hodnotě VALUES (Zisk) v kontingenční tabulce, abychom mohli porovnat naše výsledky.
V oblasti výpočtu v naší tabulce Sales vytvoříme míru s názvem Total Profit (aby nedocházelo ke konfliktům názvů). Nakonec vrátí stejné výsledky jako předtím, ale bez počítaný sloupec Profit.
Nejprve v tabulce Sales (Prodej) vybereme sloupec SalesAmount (Částka prodeje) a potom kliknutím na AutoSum (AutoSum) vytvoříme explicitní součet míry SalesAmount. Nezapomeňte, že explicitní míra je ta, která se vytvoří v oblasti výpočtu tabulky v Power Pivotu. To samé uděláme pro sloupec COGS. Tyto položky přejmenujeme na Total SalesAmount a Total COGS , aby se daly snadněji identifikovat.
Pak vytvoříme další míru pomocí tohoto vzorce:
Total Profit:=[ Total SalesAmount] - [Total COGS]
Poznámka: Vzorec bychom také mohli napsat jako Total Profit:=SUM([SalesAmount]) - SUM([COGS]), ale vytvořením samostatných měr Total SalesAmount a Total COGS je můžeme použít i v kontingenční tabulce a můžeme je použít jako argumenty v nejrůznějších vzorcích měr.
Po změně formátu naší nové míry Total Profit na měnu ji můžeme přidat do kontingenční tabulky.
Uvidíte, jak naše nová míra Total Profit (Celkový zisk) vrací stejné výsledky jako vytvoření počítaný sloupec Profit a pak ho umístíte do hodnot VALUES. Rozdíl je v tom, že míra Total Profit (Celkový zisk) je mnohem efektivnější a díky tomu je náš datový model přehlednější a štíhlejší, protože v dané době počítáme pouze pro pole, která vybereme pro kontingenční tabulku. Tento počítaný sloupec profitu ve skutečnosti nepotřebujeme.
Proč je tato poslední část důležitá? Počítané sloupce přidávají data do datového modelu a data zabírá paměť. Pokud aktualizujeme datový model, budou k přepočítání všech hodnot ve sloupci Profit potřeba také prostředky zpracování. Ve skutečnosti nepotřebujeme zabírat takové zdroje, protože chceme vypočítat zisk, když v kontingenční tabulce vybereme pole, pro která chceme zisk, jako jsou kategorie produktů, oblast nebo podle kalendářních dat.
Podívejme se na další příklad. Takový, kde počítaný sloupec vytvoří výsledky, které na první pohled vypadají správně, ale......
V tomto příkladu chceme vypočítat částky prodeje jako procento celkových prodejů. V naší tabulce Sales (Prodej) vytvoříme počítaný sloupec s názvem % of Sales (%of Sales ), například takto:
Náš vzorec uvádí: Pro každý řádek v tabulce Sales vydělte částku ve sloupci SalesAmount součtem sumy všech částek ve sloupci SalesAmount.
Když vytvoříme kontingenční tabulku a přidáme kategorii produktu do sloupce a vybereme nový sloupec % of Sales , který ho vloží do hodnot VALUES, získáme celkový součet % prodeje pro každou z našich kategorií produktů.
Ok. Zatím to vypadá dobře. Ale pojďme přidat průřez. Přidáme kalendářní rok a pak vybereme rok. V tomto případě vybereme 2007. To je to, co máme.
Na první pohled to může vypadat správně. Naše procenta by ale měla být skutečně 100 %, protože chceme znát procento celkových prodejů pro každou z našich produktových kategorií v roce 2007. Tak co se pokazilo?
Náš sloupec % of Sales vypočítal procento pro každý řádek, který představuje hodnotu ve sloupci SalesAmount vydělenou součtem všech hodnot ve sloupci SalesAmount. Hodnoty v počítaném sloupci jsou pevné. Jedná se o neměnný výsledek pro každý řádek v tabulce. Když jsme do kontingenční tabulky přidali % z prodeje , bylo agregováno jako součet všech hodnot ve sloupci SalesAmount. Součet všech hodnot ve sloupci % of Sales bude vždy 100 %.
Tip: Nezapomeňte si přečíst kontext ve vzorcích DAX. Poskytuje dobré porozumění kontextu na úrovni řádků a kontextu filtru, což zde popisujeme.
Náš počítaný sloupec % z prodeje můžeme odstranit, protože nám to nepomůže. Místo toho vytvoříme míru, která správně vypočítá procento celkového prodeje bez ohledu na použité filtry nebo průřezy.
Pamatujete si míru TotalSalesAmount, kterou jsme vytvořili dříve. Ta, která jednoduše sečte sloupec SalesAmount? Použili jsme ho jako argument v naší míře Celkový zisk a použijeme ho znovu jako argument v novém počítaném poli.
Tip: Vytváření explicitních měr, jako je Total SalesAmount a Total COGS, je užitečné nejen samotné v kontingenční tabulce nebo sestavě, ale jsou také užitečné jako argumenty v jiných mírách, pokud potřebujete výsledek jako argument. Vzorce jsou tak efektivnější a čitelnější. Toto je dobrý postup modelování dat.
Vytvoříme novou míru pomocí následujícího vzorce:
% of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Tento vzorec uvádí: Vydělte výsledek z hodnoty Total SalesAmount součtem SalesAmount bez filtrů sloupců nebo řádků, než jsou filtry definované v kontingenční tabulce.
Tip: Nezapomeňte si přečíst informace o funkcích CALCULATE a ALLSELECTED v referenčních informacích jazyka DAX.
Když teď do kontingenční tabulky přidáme nové % celkových prodejů , získáme:
To vypadá lépe. Nyní se procento celkových prodejů pro každou produktovou kategorii vypočítá jako procento z celkového prodeje za rok 2007. Pokud v průřezu CalendarYear vybereme jiný rok nebo více než jeden rok, získáme pro naše kategorie produktů nová procenta, ale celkový součet je stále 100 %. Můžeme přidat i další průřezy a filtry. Míra %of Total Sales (Procento celkového prodeje) vždy vytvoří procento z celkového prodeje bez ohledu na použité průřezy nebo filtry. U měr se výsledek vždy počítá podle kontextu určeného poli ve SLOUPCÍCH a ŘÁDCÍCH a všemi použitými filtry nebo průřezy. To je síla měr.
Tady je několik pokynů, které vám pomůžou při rozhodování, jestli je počítaný sloupec nebo míra vhodné pro konkrétní potřebu výpočtu:
Použití počítaných sloupců
-
Pokud chcete, aby se nová data zobrazovala v oblasti ŘÁDKY, SLOUPCE nebo FILTRY v kontingenční tabulce nebo na OSE, LEGENDĚ nebo DLAŽDICI PODLE ve vizualizaci Nástroje Power View, musíte použít počítaný sloupec. Stejně jako běžné sloupce dat se počítané sloupce dají použít jako pole v libovolné oblasti, a pokud jsou číselné, dají se agregovat i v hodnotě HODNOTY.
-
Pokud chcete, aby nová data byla pevnou hodnotou řádku. Máte například tabulku kalendářních dat se sloupcem kalendářních dat a chcete jiný sloupec, který obsahuje jenom číslo měsíce. Můžete vytvořit počítaný sloupec, který vypočítá jenom číslo měsíce z kalendářních dat ve sloupci Datum. Například =MONTH('Datum'[Datum]).
-
Pokud chcete přidat textovou hodnotu pro každý řádek do tabulky, použijte počítaný sloupec. Pole s textovými hodnotami nelze nikdy agregovat v hodnotě VALUES. Například =FORMAT('Datum'[Datum],"mmmm") nám poskytne název měsíce pro každé datum ve sloupci Datum v tabulce Date.
Použití měr
-
Výsledek výpočtu bude vždy záviset na ostatních polích vybraných v kontingenční tabulce.
-
Pokud potřebujete provádět složitější výpočty, například vypočítat počet na základě nějakého filtru nebo vypočítat rok v průběhu roku nebo odchylku, použijte počítané pole.
-
Pokud chcete zachovat velikost sešitu na minimu a maximalizovat jeho výkon, vytvořte co nejvíce výpočtů, kolik je to možné. V mnoha případech můžou být všechny výpočty míry, které výrazně zmenší velikost sešitu a zrychlí se doba aktualizace.
Mějte na paměti, že není nic špatného na vytvoření počítaných sloupců, jako jsme to udělali u sloupce Zisk, a jejich agregaci v kontingenční tabulce nebo sestavě. Ve skutečnosti je to opravdu dobrý a snadný způsob, jak se seznámit s vlastními výpočty a vytvářet je. S tím, jak budete rozumět těmto dvěma mimořádně výkonným funkcím Power Pivotu, budete chtít vytvořit co nejefektivnější a nejpřesnější datový model. Doufejme, že to, co jste se zde naučili, pomůže. Existuje několik dalších opravdu skvělých zdrojů, které vám mohou také pomoci. Tady je několik z nich: Kontext ve vzorcích DAX, Agregace v Power Pivotu a Centrum prostředků DAX. A i když je to trochu pokročilejší a zaměřené na účetní a finanční profesionály, modelování a analýza dat o zisku a ztrátách pomocí Microsoft Power Pivotu v Excelu obsahuje skvělé příklady modelování dat a vzorců.
