Když se většina uživatelů učí používat Power Pivot, zjistí, že skutečná síla nějakým způsobem spočívá v agregaci nebo výpočtu výsledků. 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í nástroje Power View. Vzhledem k tomu, že se jedná o číselné hodnoty, se bude automaticky sčítat, průměrovat, počítat nebo jakýkoli typ agregace, který vyberete. Tomu se říká implicitní míra. Implicitní míry jsou skvělé pro rychlou a snadnou agregaci, mají ale svá omezení a tyto limity je možné téměř vždy překonat explicitními mírami a počítanými sloupci.
Nejdřív se podívejme na příklad, kde pomocí počítaného sloupce přidáme novou textovou hodnotu pro každý řádek v tabulce s názvem Součin. Každý řádek v tabulce Produkt obsahuje všechny druhy informací o každém produktu, který prodáváme. Máme sloupce pro název produktu, barvu, velikost, cenu prodejce atd. Máme další související tabulku s názvem Kategorie produktu, která obsahuje sloupec ProductCategoryName. Chceme, aby každý produkt v tabulce Produkt obsahoval název kategorie produktu z tabulky Kategorie produktu. V naší tabulce Produkt můžeme vytvořit počítaný sloupec s názvem Kategorie produktu takto:
Náš nový vzorec kategorie výrobku používá funkci DAX RELATED k získání hodnot ze sloupce ProductCategoryName v související tabulce Product Category a potom tyto hodnoty zadává pro každý produkt (každý řádek) v tabulce Product (produkt).
Tohle je skvělý příklad toho, jak můžeme pomocí počítaného sloupce přidat do každého řádku pevnou hodnotu, kterou můžeme později použít v oblasti ŘÁDKY, SLOUPCE nebo FILTRY kontingenční tabulky nebo v sestavě Power View.
Vytvořme si 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 Prodej, která obsahuje data transakcí, a mezi tabulkami Prodej a Kategorie výrobku existuje relace. V tabulce Sales máme jeden sloupec, který obsahuje částky prodeje a další sloupec, který obsahuje náklady.
Můžeme vytvořit počítaný sloupec, který vypočítá výši zisku pro každý řádek odečtením hodnot ve sloupci COGS od hodnot ve sloupci SalesAmount (Částka prodeje), třeba takto:
Teď můžeme vytvořit kontingenční tabulku a přetáhnout pole Kategorie výrobku do sloupce a nové pole Zisk do oblasti HODNOTY (sloupec v tabulce doplňku PowerPivot 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ů. Náš výsledek vypadá takto:
V tomto případě dává zisk smysl jenom jako pole ve funkci HODNOTY. Pokud bychom do oblasti SLOUPCE vložili Zisk, vypadala by naše kontingenční tabulka takto:
Naše pole Zisk neposkytuje žádné užitečné informace, pokud je umístěno v oblastech SLOUPCE, ŘÁDKY nebo FILTRY. Smysl má jenom jako agregovaná hodnota v oblasti HODNOTY.
Vytvořili jsme sloupec s názvem Zisk, který vypočítá ziskovou marži pro každý řádek v tabulce Prodej. Pak jsme do oblasti HODNOTY naší kontingenční tabulky přidali Zisk a automaticky jsme vytvořili implicitní míru, kde je výsledek vypočítán pro každou z kategorií produktů. Pokud si myslíte, že jsme zisk pro naše kategorie produktů opravdu vypočítali dvakrát, máte pravdu. Napřed jsme vypočítali zisk pro každý řádek v tabulce Prodej a pak jsme přidali zisk do oblasti HODNOTY, kde byl agregován pro každou z kategorií produktů. Pokud si také myslíte, že jsme ve skutečnosti nepotřebovali vytvořit počítaný sloupec Zisk, máte také pravdu. Jak potom vypočítáme náš zisk, aniž bychom vytvořili počítaný sloupec Zisk?
Zisk by se opravdu lépe počítal jako explicitní míra.
Abychom mohli naše výsledky porovnat, nechme prozatím náš sloupec Zisk počítaný v tabulce Prodej a kategorii produktu ve SLOUPCÍCH a Zisk v HODNOTÁCH naší kontingenční tabulky.
V oblasti výpočtu naší tabulky Sales (Prodej) vytvoříme míru s názvem Total Profit (Celkový zisk), aby nedocházelo ke konfliktům v názvech. Nakonec to vrátí stejné výsledky jako předtím, ale bez počítaného sloupce Zisk.
Nejdřív v tabulce Prodej vybereme sloupec SalesAmount (Částka prodeje) a potom klikneme na tlačítko AutoSum (Automatické shrnutí), abychom vytvořili explicitní míru součtu SalesAmount (Částka prodeje ). Nezapomeňte, že explicitní míra je ta, kterou vytvoříme v oblasti výpočtu tabulky v Power Pivotu. Totéž uděláme pro sloupec COGS. Přejmenujeme je na Total SalesAmount a Total COGS , abychom usnadnili jejich identifikaci.
Pak vytvoříme další míru pomocí tohoto vzorce:
Celkový zisk:=[Total SalesAmount] - [Total COGS]
Poznámka
Mohli bychom také napsat náš vzorec 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 naší kontingenční tabulce a můžeme je použít jako argumenty v nejrůznějších dalších vzorcích měr.
Když změníme formát naší nové míry Celkový zisk na měnu, můžeme ji přidat do naší kontingenční tabulky.
Jak vidíte, naše nová míra Celkový zisk vrací stejné výsledky, jako když vytvoříte počítaný sloupec Zisk a pak ho umístíte do HODNOT. Rozdíl je v tom, že naše měřítko celkového zisku je mnohem efektivnější a díky tomu je náš datový model přehlednější a štíhlejší, protože počítáme v čase a pouze pro pole, která vybereme pro naši kontingenční tabulku. Vlastně ani tento sloupec s výpočtem zisku nepotřebujeme.
Proč je tato poslední část důležitá? Počítané sloupce přidávají data do datového modelu a zabírají paměť. Pokud datový model aktualizujeme, je potřeba ke zpracování zdrojů také přepočet všech hodnot ve sloupci Zisk. Ve skutečnosti nepotřebujeme používat takové zdroje, protože opravdu chceme vypočítat náš zisk při výběru polí v kontingenční tabulce, jako jsou kategorie produktů, oblast nebo kalendářní data.
Podívejme se na další příklad. Takový, kde počítaný sloupec vytváří výsledky, které na první pohled vypadají správně, ale....
V tomto příkladu chceme vypočítat částky prodejů jako procento z celkového prodeje. V naší tabulce Prodej vytvoříme počítaný sloupec s názvem % of Sales , který bude vypadat takto:
Náš vzorec říká: Pro každý řádek v tabulce Sales vydělte částku ve sloupci SalesAmount (Částka) částkou ve sloupci SalesAmount (ČástkaProdeje) součet všech částek ve sloupci SalesAmount (Částka prodeje).
Pokud vytvoříme kontingenční tabulku, přidáme do sloupce SLOUPEC Kategorie produktů, vybereme nový sloupec % z prodeje a převedeme ho do sloupce HODNOTY, dostaneme součet % prodeje pro každou z našich produktových kategorií.
Dobře. To zatím vypadá dobře. Přidejme ale průřez. Přidáme kalendářní rok a vybereme rok. V tomto případě vybereme rok 2007. To je to, co dostáváme.
Na první pohled by se to ještě mohlo zdát správné. Ale naše procenta by měla ve skutečnosti činit 100 %, protože chceme znát procento z celkového prodeje pro každou z našich kategorií produktů za rok 2007. Co se tedy pokazilo?
Náš sloupec % z prodeje vypočítal procentuální hodnotu pro každý řádek, což je hodnota ve sloupci SalesAmount dělená 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 tabulky. Když jsme do naší kontingenční tabulky přidali % z prodeje , bylo to agregované jako součet všech hodnot ve sloupci SalesAmount. Tento součet všech hodnot ve sloupci % z prodeje bude vždycky 100 %.
Tip:
Nezapomeňte si přečíst kontext ve vzorcích jazyka DAX. Poskytuje dobré pochopení kontextu na úrovni řádku a kontextu filtru, což je to, co zde popisujeme.
Náš sloupec Počítané procento prodejů můžeme odstranit, protože nám to nepomůže. Místo toho vytvoříme míru, která správně vypočítá naše procento z celkového prodeje bez ohledu na použité filtry nebo průřezy.
Pamatujete si na míru TotalSalesAmount, kterou jsme vytvořili dříve – tu, která jednoduše sčítala sloupec SalesAmount? Použili jsme ho jako argument v našem ukazateli celkového zisku a znovu ho použijeme jako argument v našem novém počítaném poli.
Tip:
Vytváření explicitních měr, jako je Total SalesAmount (Celkovou částku prodeje) a Total COGS (Celkové náklady na prodané zboží), je užitečné nejen v kontingenční tabulce nebo sestavě, ale může sloužit i jako argumenty v jiných mírách, když potřebujete výsledek jako argument. Díky tomu jsou vaše vzorce efektivnější a čitelnější. To je osvědčený postup modelování dat.
Vytvoříme novou míru podle následujícího vzorce:
% z celkových prodejů:=([Celková částkaprodeje]) / CALCULATE([ČástkaProdeje], ALLSELECTED())
Tento vzorec uvádí: Vydělí výsledek součtu ČástkaProdeje součtem ČástkaProdeje bez filtrů sloupců nebo řádků kromě těch, které jsou definované v kontingenční tabulce.
Tip:
Nezapomeňte si přečíst informace o funkcích CALCULATE a ALLSELECTED v referenční dokumentaci jazyka DAX.
Když teď do kontingenční tabulky přidáme nové procento celkových prodejů , dostaneme:
To vypadá lépe. Nyní se procentuální podíl celkových prodejů pro každou kategorii výrobků vypočítá jako procento z celkového prodeje za rok 2007. Pokud v průřezu kalendářního roku vyberete jiný rok nebo více než jeden rok, získáte nová procenta pro naše kategorie produktů, ale náš celkový součet je stále 100 %. Můžeme přidat i další průřezy a filtry. Naše míra % z celkových prodejů vždy vytvoří určité procento z celkových prodejů 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 případnými použitými filtry nebo průřezy. To je síla opatření.
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 zobrazila ve ŘÁDCÍCH, SLOUPCÍCH nebo FILTRECH v kontingenční tabulce nebo na OSE, LEGENDĚ nebo VEDLE DLAŽDIC ve vizualizaci Power View, musíte použít počítaný sloupec. Podobně jako běžné sloupce dat je možné počítané sloupce použít jako pole v libovolné oblasti, a pokud se jedná o číselné sloupce, lze je také agregovat do funkce HODNOTY.
- Pokud chcete, aby nová data pro řádek obsahovala pevnou hodnotu, Máte například tabulku kalendářních dat se sloupcem kalendářních dat a chcete, aby další sloupec obsahoval pouze čísla měsíců. Můžete vytvořit počítaný sloupec, který z dat ve sloupci Datum vypočítá pouze číslo měsíce. Například =MONTH('Date'[Date]).
- Pokud chcete přidat textovou hodnotu pro každý řádek tabulky, použijte počítaný sloupec. Pole s textovými hodnotami nelze nikdy agregovat ve funkci VALUES. Například =FORMAT('Date'[Date],"mmmm") nám poskytne název měsíce pro každé datum ve sloupci Datum v tabulce Date.
Použití měr
- Pokud bude výsledek výpočtu vždy závislý na ostatních polích, která v kontingenční tabulce vyberete.
- 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 za rokem nebo rozptyl, použijte počítané pole.
- Pokud chcete velikost sešitu omezit na minimum a maximalizovat jeho výkon, vytvořte co nejvíce měr výpočtů. V mnoha případech můžou být všechny vaše výpočty mírou, která výrazně zmenší velikost sešitu a urychlí aktualizaci.
Mějte na paměti, že není nic špatného na tom, když vytvoříte počítané sloupce, jako jsme to udělali se sloupcem Zisk, a pak je zkombinujete do kontingenční tabulky nebo sestavy. Je to vlastně opravdu dobrý a snadný způsob, jak se naučit a vytvářet vlastní výpočty. S tím, jak budete těmto dvěma mimořádně výkonným funkcím Power Pivotu postupně rozumět, 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 také mohou pomoci. Tady jsou některé z těchto úprav: kontext ve vzorcích jazyka DAX, agregace v Power Pivotu a Centrum zdrojů jazyka DAX. A i když je tato ukázka datového modelování a ztrát pomocí Microsoft Power Pivotu v Excelu trochu pokročilejší a určená pro profesionály v oblasti účetnictví a financí, obsahuje skvělé příklady modelování dat a vzorců.