V tomto příspěvku si ukážeme příklad porovnání aktuálních prodejů s plánem prodejů v Power BI a v jazyku DAX. Porovnávat budeme kumulativní prodeje v aktuálním roce s ročním plánem tržeb pro každou kategorii produktů. Výsledný vizuál pak bude zachycovat procentuální plnění ročního plánu k aktuálnímu datu.
K tomuto tématu je k dispozici také video:
V použitém cvičném Power BI souboru, který si můžete stáhnout níže pod tímto příspěvkem, je jako aktuální rok brán rok 2020. Výsledný vizuál pro aktuální rok tak může vypadat například následovně.
Jak je možné vidět na obrázku výše, na základě procentuálního plnění plánu můžeme v průběhu otevřeného období sledovat, jak se postupně plní cílová suma prodejů v jednotlivých kategoriích produktů. U kategorií Accessories a Components vidíme že v polovině roku bylo plnění ročního plánu více než 50 %, zatímco u kategorií Bikes a Clothing jsme v polovině roku pod 50 % oproti plánu.
Pokud bychom se pomocí průřezu přesunuly do roku 2019, budeme moci vidět, zda došlo v některých kategoriích k naplnění celoročního plánu, protože pro rok 2019, který uvažujeme v použitém cvičném souboru jako předchozí rok, máme k dispozici prodeje za celý rok.
V každé buňce na obrázku výše porovnáváme prodeje od začátku roku po poslední den v aktuálním měsíci s celoročním plánem. V prosinci 2019 již máme k dispozici data za prodeje za celý rok, a pro tento měsíc tak vidíme porovnání celoročních prodejů s plánem prodejů pro aktuální rok. Můžeme tak například vidět, že v kategorii Components se podařilo výrazně překonat roční plán, zatímco v kategoriích Accessories a Bikes byly celkové prodeje za rok 2019 mírně pod stanoveným plánem.
Problémem při práci s plány je obvykle rozdílná granularita reálných dat a rozdílná granularita plánu. To je také situace, se kterou se budeme muset vypořádat v našem příkladu, protože reálná data máme dostupná pro každý den nebo pro každý produkt, a plán máme v granularitě roků a kategorií produktů.
Na obrázku výše můžeme vidět tabulku 'Plán' tak jak je nahrána v modelu. Plán je tvořen pro každou kategorii a rok. To že pracujeme s plánem v jiné granuralitě než jsou reálná data je typický jev, protože plány se obvykle netvoří pro každý den a pro každý produkt, ale pro určité agregace.
Tabulka 'Plán' je propojena s tabulkami 'Product' a 'Date' pomocí relací M:N, protože v tabulce 'Product' i v tabulce 'Plán' je každá kategorie produktů vícekrát, a také v tabulce 'Date' a v tabulce 'Plán' je každý rok vícekrát.
Než se dostaneme k samotnému porovnání kumulativních prodejů s plánem, ukážeme si jednu z možností, jak pracovat s plánem prodejů v reportech a jak pomocí jazyka DAX ošetřit, aby se plánované tržby zobrazovali pouze pro roky a kategorie, a nikoliv pro detailnější atributy, jako jsou dny nebo konkrétní produkty, pro které máme k dispozici reálná data.
Práce s plánem prodejů v Power BI a v jazyku DAX
Pro začátek si do modelu přidáme dvě jednoduchá měřítka. Měřítko [Prodeje] bude vracet sumu za prodeje produktů v aktuálním kontextu vyhodnocení.
Měřítko:
V měřítku [Prodeje] jednoduše sčítáme částky za prodeje produktů uložené ve sloupci 'Sales'[Sales Amount] ve faktové tabulce 'Sales'. Další měřítko, které budeme potřebovat by mělo vracet plán prodejů pro každou kategorii a rok.
Měřítko:
V měřítku [Roční plán prodejů (jednoduché)] sčítáme částky ze sloupce 'Plán'[Plán]. V tabulce 'Plán' máme plánovanou částku prodejů pro každou kategorii a rok. Tabulka 'Plán' je ale propojena pomocí relací s tabulkami 'Date' a 'Product', které mají každá také další, podrobnější atributy než je rok, respektive než jsou kategorie produktů.
V případě tabulky 'Product' to mohou být například podkategogrie produktů, barvy produktů nebo jednotlivé produkty. V tabulce 'Date' jsou také detailnější atributy než je rok, protože v této tabulce máme například čtvrtletí, měsíce nebo konkrétní dny.
Pokud vložíme obě nová měřítka do vizuálu matice, ve kterém jsou v řádcích roky z tabulky 'Date' a kategorie produktů z tabulky 'Product', to znamená že vizuál obsahuje stejné atributy které máme také v tabulce 'Plán', obě měřítka budou vracet správné výsledky.
Jakmile se ale dostaneme v rozpadu na nižší úroveň, například na podkategorie produktů, měřítko [Roční plán prodejů (jednoduché)] bude vracet stále plán pro celou kategorii, a ne pro aktuální podkategorii.
Měřítko [Roční plán prodejů (jednoduché)] vrací roční plán prodejů pro kategorii "Bikes" 44 000 000. Stejnou částku ale vidíme také u podkategorií "Mountain Bikes", "Road Bikes" a "Touring Bikes". Důvod proč se u každé podkategorie zobrazuje plán pro celou kategorii je dán granularitou dat v tabulce 'Plán', kde máme data pro jednotlivé kategorie, a dále typem relace mezi tabulkou 'Plán' a tabulkou 'Product'.
Pro popsání celého problému se můžeme zaměřit na jednu konkrétní buňku ve vizuálu matice zobrazeném na obrázku výše. Uvažujme například podkategorii "Road Bikes". Pokud se nacházíme v řádku podkategorie "Road Bikes", tak tabulka 'Product' je v tomto řádku filtrována na kategorii "Bikes" a podkategorii "Road Bikes".
Začít můžeme měřítkem [Prodeje], ve kterém sčítáme hodnoty z jednoho sloupce z tabulky 'Sales'. Při vyhodnocení tohoto měřítka jsou filtry z tabulky 'Product' do tabulky 'Sales' propagovány pomocí relace vytvořené na základě sloupců 'Product'[ProductKey] a 'Sales'[ProductKey]. Při vyhodnocení měřítka [Prodeje] dojde nejdříve k načtení všech hodnot ze sloupce 'Product'[ProductKey], které patří do dané kategorie a podkategorie. Následně jsou tyto hodnoty použity pro zafiltrování tabulky 'Sales', a to prostřednictvím sloupce 'Sales'[ProductKey]. Relace mezi tabulkou 'Product' a 'Sales' funguje spolehlivě pro jakýkoliv atribut z tabulky 'Product', protože sloupec 'Product'[ProductKey] použitý pro vytvoření této relace je sloupec s nejnižší granularitou v tabulce 'Product' a obsahuje jedinečné hodnoty.
U měřítka [Roční plán prodejů (jednoduché)] je ale situace jiná. Relace mezi tabulkou 'Product' a tabulkou 'Plán' je vytvořena na základě kategorií produktů. Sloupec 'Product'[Category] ale obsahuje mnoho duplicitních hodnot. Pokud tedy zafiltrujeme tabulku 'Plán' pouze na podkategorii "Road Bikes", budou všechny řádky v takto zafiltrované tabulce obsahovat ve sloupci 'Product'[Category] hodnotu "Bikes", do které daná podkategorie patří. Hodnota "Bikes" je pak použita pro zafiltrování tabulky 'Plán', a pro podkategorii "Road Bikes" proto vidíme plán prodejů pro celou kategorii, do které tato podkategorie patří. Stejný problém by nastal u jakéhokoliv jiného sloupce z tabulky 'Product', který je pod úrovní kategorií, to znamená pro každý sloupec, protože kategorie produktů je v tabulce produktů v hierarchii na nejvyšší úrovni.
Na stejný problém narazíme také u všech atributů z kalendářní tabulky, které jsou pod úrovní jednotlivých let.
Na obrázku výše je v průřezu vybrán rok 2020 a měsíc březen. Měřítko [Prodeje] vrací správné hodnoty, a to prodeje v daném měsíci a roce a ve vybrané kategorii. Měřítko [Roční plán prodejů (jednoduché)] ale vrací pro každou kategorii stále plán prodejů na celý rok 2020.
Zobrazovat plán prodejů pro atributy, které jsou rozpadem let nebo kategorií, nemá smysl, protože pro nižší úroveň nemáme k dispozici detailnější data. Protože jsou ale měřítka ze své podstaty univerzálně použitelná v jakémkoliv kontextu, bude lepší před zobrazení plánu ověřovat, zda jsou v aktuálním kontextu vyhodnocení k dispozici všechny dny ve vybraných letech a všechny produkty ve vybraných kategoriích. Pro tento účel si můžeme vytvořit technické měřítko, které bude pro uživatele skryté a které bude vracet hodnotu TRUE v případě, že jsou k dispozici všechny dny v roce a všechny produkty v kategorii.
Měřítko:
Nové měřítko si můžeme vložit do původního vizuálu a popsat si logiku jeho fungování.
V průřezu na levé straně máme vybraný celý rok 2020, to znamená že proměnná VsechnyDnyVRoce bude vracet vždy hodnotu TRUE. U tabulky 'Product' je ale situace jiná. Pokud se ve vizuálu průřez nacházíme v řádcích s kategoriemi produktů nebo v řádku celkem, jsou v aktuálním kontextu k dispozici všechny produkty v aktuální kategorii nebo ve všech kategoriích v řádku celkem. V těchto řádcích proto vrací měřítko [Zobrazit plán] hodnotu TRUE. Jakmile se ale dostaneme na nižší úroveň v hierarchii, konkrétně do řádků s podkategoriemi, měřítko [Zobrazit plán] vrací hodnotu FALSE. V řádcích s podkategoriemi produktů totiž nemáme k dispozici všechny produkty z vybrané kategorie, ale pouze jejich podmnožinu. V těchto řádcích proto nebudeme zobrazovat plán prodejů pro celou kategorii.
Podobně jako na filtry z tabulky 'Product' bude měřítko [Zobrazit plán] reagovat na filtry z tabulky 'Date'. Pokud tedy bude filtr nastavený na sloupce z tabulky 'Date' detailnější, než je úroveň let, měřítko [Zobrazit plán] bude vracet hodnotu FALSE.
V průřezu na levé straně je vybraný rok 2020 a dále měsíc březen. To znamená že v aktuálním kontextu nejsou dostupné všechny dny v roce, a proto měřítko [Zobrazit plán] vrací vždy hodnotu FALSE.
Protože celou logiku pro rozhodnutí, zda v aktuálním kontextu budeme nebo nebudeme zobrazovat plánované prodeje máme v pomocném měřítku [Zobrazit plán], měřítko [Roční plán prodejů] již bude jednoduché.
Měřítko:
Novou verzi měřítka ročních plánů prodejů již můžeme bezpečně používat v jakémkoli kontextu, protože se bude zobrazovat pouze pokud jsou k dispozici všechny produkty v kategorii a všechny dny v roce.
Nyní když máme připravené měřítko [Roční plán prodejů] můžeme se přesunout k porovnání kumulativních prodejů v aktuálním roce s ročním plánem prodejů.
Procentuální plnění plánu v jazyku DAX
Začít můžeme opět přípravou vizuálu. Pro začátek budeme pracovat ve vizuálu matice pouze s měsíci v řádcích. Výchozí vizuál tak může vypadat například následovně.
Měřítko [Roční plán prodejů] vrací podle očekávání pro každý měsíc prázdnou hodnotu BLANK, protože v kontextu jednotlivých měsíců nejsou dostupné všechny dny v roce.
Další měřítko, které budeme pro náš účel potřebovat bude vracet kumulativní prodeje v aktuálním roce.
Měřítko:
Nové měřítko si můžeme vložit do původního vizuálu a zobrazit si výsledky.
Ve výsledném měřítku budeme porovnávat kumulativní prodeje s ročním plánem prodejů. Pro tento účel budeme potřebovat načíst roční plán prodejů pro každý měsíc, a proto budeme před vyhodnocením měřítka [Roční plán prodejů] odstraňovat filtry z řádků vizuálu, kde jsou právě jednotlivé měsíce.
Měřítko:
Nové měřítko bude vracet procentuální podíl sumy prodejů za všechny dny před posledním dnem dostupným v aktuálním kontextu vyhodnocení vůči celoročnímu plánu prodejů.
Pokud odebereme všechny mezivýpočty, a do sloupců vizuálu matice vložíme kategorie produktů, můžeme vidět, jak se v jednotlivých kategoriích daří plnit plán v průběhu roku.
Pokud se pomocí průřezu přesuneme do roku 2019, pro který máme k dispozici prodeje za kompletní rok, v posledním měsíci roku již budeme moci vidět, zda se v některé z kategorií podařilo naplnit plán.
Jak můžeme vidět na obrázku výše, celoroční plán pro rok 2019 se podařilo naplnit v kategoriích Clothing a Components.
Shrnutí
Při práci s plánem se obvykle musíme potýkat se situací, kdy plán je tvořen v určité agregaci, zatímco reálné hodnoty máme k dispozici ve větším detailu. Proto je důležité před zobrazováním plánu ověřovat, za je granularita ve které je měřítko vyhodnoceno stejná nebo vyšší než je granularita v rámci které máme k dispozici plán. V opačném případě bychom mohli zobrazovat nesprávné hodnoty, protože pro nižší úrovně detailu, než je tvořen plán nemáme k dispozici odpovídající data.
Komentáře
Okomentovat