Když se poprvé učíte používat Power Pivot, většina uživatelů zjistí, že skutečná moc je v nějakém způsobu agregování nebo výpočtu výsledku. Pokud mají data sloupec s číselnými hodnotami, můžete je snadno agregovat tak, že je vyberete v kontingenční tabulce nebo Power View seznam polí. Vzhledem k tomu, že je číselná, bude automaticky sečtena, zprůměrována, počítána nebo bez ohledu na typ agregace, kterou vyberete. Označuje se jako implicitní míra. Implicitní míry jsou skvělé pro rychlou a snadnou agregaci, ale mají limity a tyto limity lze téměř vždy překonat explicitními opatřeními a počítané sloupce.
Podívejme se na příklad, ve kterém používáme počítaný sloupec k přidání nové textové hodnoty pro každý řádek v tabulce s názvem Produkt. 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 zahrnoval název kategorie produktu z tabulky Kategorie produktu. V naší tabulce Product (Produkt) můžeme vytvořit počítaný sloupec s názvem Kategorie produktu:
Náš nový vzorec Kategorie produktu používá funkci RELATED DAX k získání hodnot ze sloupce ProductCategoryName v související tabulce Product Category (Kategorie výrobku) a potom tyto hodnoty pro každý produkt (každý řádek) zadá do tabulky Product (Produkt).
Toto je skvělý příklad toho, jak můžeme pomocí počítaného sloupce přidat pevnou hodnotu pro každý řádek, který můžeme později použít v oblasti ŘÁDKY, SLOUPCE nebo FILTRY v kontingenční tabulce nebo v Power View sestavě.
Vytvoříme další příklad, ve kterém chceme vypočítat ziskové rozpětí 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 existuje vztah mezi tabulkou Prodej a tabulkou Kategorie produktu. V tabulce Prodej 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 odečtením hodnot ve sloupci COGS od hodnot ve sloupci SalesAmount, například:
Teď můžeme vytvořit kontingenční tabulku a přetáhnout pole Kategorie produktu do sloupce a naše nové pole Zisk do oblasti HODNOTY (sloupec v tabulce v 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 zisků pro každou z různých kategorií produktů. Náš výsledek vypadá takhle:
V tomto případě má zisk smysl jenom jako pole v poli HODNOTY. Pokud bychom do oblasti SLOUPCE dali zisk, naše kontingenční tabulka by vypadala takhle:
Pole Zisk neposkytuje žádné užitečné informace, pokud je umístěné v oblastech SLOUPCE, ŘÁDKY nebo FILTRY. Dává smysl jenom jako agregovaná hodnota v oblasti HODNOTY.
Udělali jsme vytvoření sloupce s názvem Zisk, který vypočítá ziskovou marži pro každý řádek v tabulce Prodej. Potom jsme přidali zisk do oblasti VALUES naší kontingenční tabulky a automaticky jsme vytvářeli implicitní míru, ve které se vypočítá výsledek pro každou kategorii produktů. Pokud si myslíte, že jsme pro naše kategorie produktů skutečně vypočítali zisk dvakrát, máte pravdu. Nejprve jsme vypočítali zisk pro každý řádek v tabulce Prodej a pak jsme přidali Zisk do oblasti VALUES, kde byl agregovaný pro každou kategorii produktů. Pokud si taky myslíte, že ve skutečnosti nepotřebujeme vytvořit počítaný sloupec Zisk, máte také správnou hodnotu. Ale jak pak vypočítáme zisk bez vytvoření počítaného sloupce Zisk?
Zisk by se skutečně lépe vypočítal jako explicitní míra.
Pro tuto dobu opustíme náš počítaný sloupec Zisk v tabulce Prodej a Kategorie produktu ve sloupcích SLOUPCE a Zisk v hodnotách naší kontingenční tabulky a porovnáme tak naše výsledky.
V oblasti výpočtu tabulky Prodej vytvoříme míru s názvem Celkový zisk (aby se zabránilo konfliktům pojmenování). Nakonec bude mít stejné výsledky jako dřív, ale bez počítaného sloupce Zisk.
Nejprve v tabulce Prodej vybereme sloupec SalesAmount a potom kliknutím na AutoSum vytvoříme explicitní součet z míry SalesAmount. Nezapomeňte, že explicitní míra je míra, která se vytváří v oblasti výpočtu tabulky v Power Pivotu. Totéž platí pro sloupec COGS. Přejmenováme tyto celkové prodejeAmount a Celkový coGS, aby se snadněji identifikovaly.
Potom vytvoříme další míru s tímto vzorcem:
Celkový zisk:=[ Celková hodnota ProdejeAmount] - [Celkový výsledek_celkového_tržby]
Poznámka: Náš vzorec můžeme také napsat jako Celkový zisk:=SUMA([SalesAmount]) – SUMA([COGS]), ale vytvořením samostatných měr Celkové prodejeAmount a Celkové coGS je můžeme použít i v naší kontingenční tabulce a můžeme je použít jako argumenty ve všech důměrných vzorcích.
Po změně formátu naší nové míry celkového zisku na měnu ji můžeme přidat do naší kontingenční tabulky.
Naše nová míra celkového zisku vrátí stejné výsledky jako vytvoření počítaného sloupce Zisk a jeho umístění do hodnot. Rozdíl je v tom, že míra celkového zisku je mnohem efektivnější a náš datový model je čistší a štíhlejší, protože v tuto chvíli počítáme jenom pro pole, která pro naši kontingenční tabulku vybereme. Tento počítaný sloupec Zisk vlastně 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, je potřeba také zpracovávat zdroje k přepočtu všech hodnot ve sloupci Zisk. Zdroje, jako je tento, opravdu nepotřebujeme, protože chceme vypočítat zisk, když v kontingenční tabulce vybereme pole, pro která chceme mít zisk, například kategorie produktů, oblast nebo podle kalendářních dat.
Podívejme se na další příklad. Ten, kde počítaný sloupec vytváří výsledky, které na první pohled vypadají správně, ale......
V tomto příkladu chceme vypočítat částky prodeje jako procento z celkových prodejů. V naší tabulce Prodej vytvoříme počítaný sloupec s názvem % prodejů, například:
Náš vzorec uvádí: Pro každý řádek v tabulce Prodej vydělte částku ve sloupci SalesAmount součtem součtu všech částek ve sloupci SalesAmount.
Pokud vytvoříme kontingenční tabulku a přidáme kategorii produktů do sloupce SLOUPCE a vybereme náš nový sloupec % prodejů, který se přidá do hodnot HODNOT, dostaneme celkový součet % prodejů pro každou z našich kategorií produktů.
Ok. Zatím to vypadá dobře. Ale přidáme průřez. Přidáme Kalendářní rok a pak vybereme rok. V takovém případě vybereme 2007. To je to, co dostaneme.
Na první pohled se to může zdát správné. Naše procenta by ale měla skutečně součet 100 %, protože chceme znát procento z celkových prodejů pro každou z našich kategorií produktů za rok 2007. Co se tedy pokazilo?
Náš sloupec % prodejů vypočítal procenta pro každý řádek, který je hodnotou ve sloupci SalesAmount vydělenou součtem všech hodnot ve sloupci SalesAmount. Hodnoty v počítaných sloupci jsou pevné. Je to neměnný výsledek pro každý řádek v tabulce. Když jsme do naší kontingenční tabulky přidali % prodejů, agregoval se jako součet všech hodnot ve sloupci SalesAmount. Tento součet všech hodnot ve sloupci % prodeje bude vždy 100 %.
Tip: Nezapomeňte si přečíst Kontext ve vzorcích jazyka DAX. Poskytuje dobrou znalost kontextu na úrovni řádků a kontextu filtru, což je to, co tady popisujeme.
Počítaný sloupec % prodeje můžeme odstranit, protože nám nepomůže. Místo toho vytvoříme míru, která správně vypočítá procento celkových prodejů bez ohledu na použité filtry nebo průřezy.
Pamatujete si míru TotalSalesAmount, kterou jsme vytvořili dřív, která jednoduše sečte sloupec SalesAmount? Použili jsme ho jako argument v našem měření celkového zisku a znovu ho použijeme jako argument v našem novém počítaném poli.
Tip: Vytváření explicitních opatření, jako je celkový prodejAmount a Celkový coGS, je užitečné nejen v kontingenční tabulce nebo sestavě, ale jsou užitečné také jako argumenty v jiných opatřeních, když potřebujete výsledek jako argument. Díky tomu jsou vzorce efektivnější a snadněji čitelné. Toto je dobrá praxe modelování dat.
Vytvoříme novou míru s následujícím vzorcem:
% z celkových prodejů:=([Celková hodnotaProdeje]) / CALCULATE([Celkový prodejAmount], ALLSELECTED())
Tento vzorec uvádí: Vydělte výsledek ze součtu prodejůAmount součtem hodnoty SalesAmount bez jiných 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 funkcích jazyka DAX.
Pokud teď do kontingenční tabulky přidáme naše nové % celkových prodejů, dostaneme:
To vypadá líp. Naše % celkových prodejů pro každou kategorii produktů se teď počítá jako procento z celkových prodejů za rok 2007. Pokud v průřezu CalendarYear vybereme jiný rok nebo více než jeden rok, získáme nová procenta pro naše kategorie produktů, ale celkový součet je pořád 100 %. Můžeme přidat i další průřezy a filtry. Naše míra % celkových prodejů vždy vytvoří procento z celkových prodejů bez ohledu na použité průřezy nebo filtry. U položek se výsledek vypočítává vždy podle kontextu, který určují pole v polích SLOUPCE a ŘÁDKY, a podle všech použitých filtrů nebo průřezů. Toto je moc opatření.
Tady je několik pokynů, které vám pomůžou při rozhodování, jestli je počítaný sloupec nebo míra pro konkrétní potřebu výpočtu v pořádku:
Použití počítaných sloupců
-
Pokud chcete, aby se nová data v kontingenční tabulce objevila na řádcích, sloupcích nebo filtrech nebo na ose, legendě nebo dlaždici PODLE ve vizualizaci Power View, musíte použít počítaný sloupec. Stejně jako běžné sloupce dat lze počítané sloupce použít jako pole v libovolné oblasti a pokud jsou číselné, počítají se i v hodnotách VALUES.
-
Pokud chcete, aby nová data byla pevnou hodnotou řádku. Máte třeba tabulku kalendářních dat se sloupcem kalendářních dat a chcete další sloupec, který obsahuje jenom číslo měsíce. Můžete vytvořit počítaný sloupec, který vypočítává pouze číslo měsíce z kalendářních dat ve sloupci Datum. Příklad: =MONTH('Datum'[Datum]).
-
Pokud chcete přidat textovou hodnotu pro každý řádek do tabulky, použijte počítaný sloupec. Pole s textovou hodnotou nelze nikdy agregovat v hodnotách VALUES. Například =FORMAT('Datum'[Datum];"mmmm") nám dává název měsíce pro každé datum ve sloupci Datum v tabulce Datum.
Použít míry
-
Pokud bude výsledek výpočtu vždy záviset na dalších polích, která vyberete v kontingenční tabulce.
-
Pokud potřebujete provádět složitější výpočty, jako je výpočet počtu na základě filtru určitého řazení nebo výpočet meziročního výpočtu nebo rozptylu, použijte počítané pole.
-
Pokud chcete zachovat minimální velikost sešitu a maximalizovat jeho výkon, vytvořte co nejvíce výpočtů. V mnoha případech mohou být všechny vaše výpočty míry, což výrazně zmenšuje velikost sešitu a urychluje dobu aktualizace.
Mějte na paměti, že vytváření počítaných sloupců, jako jsme to udělali s naším sloupcem Zisk, není nic špatného a pak je agregujete v kontingenční tabulce nebo sestavě. Je to vlastně opravdu dobrý a snadný způsob, jak se o svých výpočtech dozvědět a vytvořit si vlastní výpočty. S růstem porozumění těmto dvěma mimořádně výkonným funkcím Power Pivotu budete chtít vytvořit nejefektivnější a nejpřesnější datový model, který můžete. Doufáme, že to, co jste se tady naučili, pomůže. Existují i další opravdu skvělé zdroje, které vám můžou pomoct. Tady je jenom několik: Kontext ve vzorcích jazyka DAX, Agregace v Power Pivotua Centrum zdrojů JAZYKA DAX. A i když je o něco pokročilejší Excel zaměřená na účetní a finanční specialisty, je ukázka modelování Excel analýzy dat zisků Excel ztrát načtená s vynikajícími příklady modelování dat Excel vzorců.
