V tomto příspěvku si ukážeme různé příklady výpočtu kumulativních součtů v jazyku DAX a v Power BI. Kumulativní součty budeme tvořit přímo v měřítku. Každé měřítko se pak bude lišit podle toho, pro jaký atribut je vytvořeno, a to v závislosti na významu nebo na hodnotách v použitém sloupci nebo sloupcích.
K tomuto tématu je k dispozici také video:
Jako obvykle budeme pracovat se cvičným Power BI souborem Adventure Works DW 2020.pbix, ve kterém je navíc vytvořeno měřítko [Prodeje], které má následující definici.
Měřítko:
Měřítko [Prodeje] bude vracet sumu za prodeje produktů v aktuálním kontextu vyhodnocení. Toto měřítko je navíc aditivní přes všechny dimenze v modelu, a proto je to ideální kandidát pro ukázky kumulativních součtů.
V následujících příkladech si postupně ukážeme výpočet kumulativních prodejů přes časovou dimenzi, výpočet kumulativních prodejů přes kategorie produktů podle jejich ziskovosti, a nakonec výpočet kumulativních prodejů přes produkty, a to od produktu s nejvyššími prodeji po produkt s nejnižšími prodeji.
Kumulativní součty přes kalendářní tabulku
Výpočet kumulativních součtů je při práci s kalendářní tabulkou obvykle univerzální pro všechny atributy (sloupce) z této tabulky. Důvodem je hierarchická struktura kalendářní tabulky, ve které můžeme každý atribut rozdělit na konkrétní dny. Při práci s atributy z kalendářní tabulky tak můžeme všechny výpočty tvořit na úrovni konkrétních dnů, a tyto výpočty pak agregovat například do měsíců, kvartálů anebo let. Na stejném principu fungují všechny Time intelligence výpočty, které jsou vyhodnoceny také na úrovni jednotlivých dnů, ale zobrazovat je můžeme pro jakékoliv jiné atributy z kalendářní tabulky.
Uvažujme například následující jednoduché měřítko, které bude vracet kumulativní prodeje od začátku roku vždy po poslední den dostupný v aktuálním kontextu vyhodnocení.
Měřítko:
Funkce DATESYTD() je Time intelligence funkce, která vrací všechny dny od začátku roku po poslední den dostupný v aktuálním kontextu vyhodnocení. Tento rozsah dnů je pak přidán do filtru funkce CALCULATE() před vyhodnocením měřítka [Prodeje] v prvním argumentu.
Poznámka: V měřítku [Prodeje (YTD)] nemusíme odstraňovat filtry z datumové tabulky pomocí funkce REMOVEFILTERS() v případě, kdy je datumová tabulka označena jako tabulka kalendářních dat. Více informací na toto téma můžete najít v samostatném příspěvku pod tímto odkazem.
Jak už jsme si řekli dříve, každý atribut z kalendářní tabulky je obvykle složen z konkrétních dnů, a proto můžeme měřítko [Prodeje (YTD)] používat v kontextu jakéhokoliv sloupce z kalendářní tabulky, přestože funkce DATESYTD() pracuje na úrovni dnů.
Jak můžeme vidět na obrázku výše, měřítko [Prodeje (YTD)] je ve vizuálu vyhodnoceno v kontextu měsíců a let, a vrací kumulativní prodeje od začátku roku vždy po poslední den v měsíci anebo roce.
Pokud bychom chtěli vytvořit kumulativní prodeje od začátku období až po poslední den v aktuálním kontextu vyhodnocení, můžeme použít následující výpočet.
Měřítko:
V měřítku [Prodeje (RT)] bude filtr ve funkci CALCULATE() obsahovat všechny dny, které předcházejí poslednímu dnu v aktuálním kontextu vyhodnocení, který získáváme pomocí funkce MAX(). Ani v tomto případě bychom nemuseli odstraňovat filtry z tabulky 'Date' pomocí funkce REMOVEFILTERS(), protože v použitém modelu je tabulka 'Date' označena jako tabulka kalendářních dat.
Jak můžeme vidět na obrázku výše, tak u měřítka [Prodeje (RT)] již nedochází k vynulování při přechodu roku, jak tomu bylo u měřítka [Prodeje (YTD)]. Měřítko [Prodeje (RT)] tak vrací kumulativní prodeje od začátku prodejů po poslední den v aktuálním kontextu vyhodnocení, a to napříč jednotlivými roky.
Více článků, které jsou zaměřené na časové kalkulace, můžete najít na stránce DAX příklady.
V následující části příspěvku si ukážeme výpočet kumulativních prodejů přes kategorie produktů.
Kumulativní prodeje přes kategorie produktů
V této části příspěvku budeme chtít získat kumulativní prodeje přes kategorie produktů podle procentuální ziskovosti. Každý produkt je zařazen do kategorie podle jeho hrubého zisku. Produkty se ziskovostí nad 50 % na jeden prodaný kus jsou v kategorii Top, produkty se ziskovostí nad 40 % jsou v kategorii Vysoká, produkty nad 30 % v kategorii Střední, a zbytek produktů je v kategorii nízká.
Na následujícím obrázku je zobrazen vzorek dat z vybraných sloupců tabulky 'Product'.
Sloupce 'Product'[Kategorie (% ziskovost)] a 'Product'[Kategorie (% ziskovost) řazení] jsou vytvořeny jako počítané sloupce v tabulce 'Product'. Jejich definice není pro výpočet kumulativních součtů důležitá, nicméně v přiloženém souboru jsou tyto výpočty přirozeně k dispozici.
Co ale důležité je že sloupec 'Product'[Kategorie (% ziskovost)] je v modelu seřazen podle sloupce 'Product'[Kategorie (% ziskovost) řazení]. To je důležité, aby se kategorie produktů zobrazovaly ve vizuálech ve správném pořadí. Kategorie Top je číslo 1, kategorie Vysoká je číslo 2, a tak dále.
Pokud by sloupec 'Product'[Kategorie (% ziskovost)] nebyl seřazen podle sloupce 'Product'[Kategorie (% ziskovost) řazení], kategorie by se řadili abecedně, což by v tomto případě bylo nežádoucí chování.
DAX dotaz, který generuje vizuál zobrazený na obrázku výše, pak obsahuje také sloupec 'Product'[Kategorie (% ziskovost) řazení], který je použitý pro správné řazení zobrazených kategorií. Tento sloupec tak můžeme využít ve výpočtu a zobrazit si kumulativní prodeje, a to od kategorie Top, přes kategorie Vysoká a Střední až po kategorii Nízká.
Měřítko:
V měřítku [Prodeje (RT Kategorie)] musíme pro dosažení správného výsledku použít sloupec určený pro řazení. Pokud bychom použili sloupec s textovým popisem kategorie, sčítaly bychom prodeje postupně od první kategorie v abecedním pořadí, a nikoliv od kategorie nejlepší po kategorii nejhorší, tak jak je toto pořadí vyjádřeno ve sloupci 'Product'[Kategorie (% ziskovost) řazení]. Zapomenout nesmíme také na odstranění filtrů ze sloupce 'Product'[Kategorie (% ziskovost)], který je v řádcích vizuálu.
Měřítku [Prodeje (RT Kategorie)] vrací v prvním řádku vizuálu prodeje za produkty v kategorii Top, ve druhém řádku prodeje za produkty v kategoriích Top a Vysoká, ve třetím řádku prodeje za produkty v kategoriích Top, Vysoká a Střední, a tak dále.
Při výpočtu kumulativních prodejů podle kategorií je obvykle nutné mít k dispozici sloupec s číselným vyjádřením pořadí jednotlivých kategorií. Výjimkou mohou být kategorie pojmenované abecedně, například Kategorie A, Kategorie B, a tak dále. U kategorií s konstantním textem a číslem v názvu musíme být velmi opatrní, protože například názvy Kategorie 1, Kategorie 2, Kategorie 3 atd. budou správně řazeny pouze do Kategorie 9. Následná Kategorie 10 by byla abecedně zařazena na druhé místo za Kategorii 1. Z tohoto důvodu je obvykle spolehlivější pracovat ve výpočtech, ve kterých záleží na pořadí, s číselným vyjádřením pořadí.
V následující části příspěvku si ukážeme výpočet kumulativních prodejů přes názvy produktů, kdy budeme sčítat prodeje od produktu s nejvyšší částkou prodejů po produkt s nejnižší částkou prodejů. Pořadí tedy nebude záviset na sloupci použitém ve vizuálu, ale na hodnotě měřítka, které bude vyhodnoceno v kontextu tohoto sloupce.
Kumulativní prodeje produktů podle sumy prodejů
Při výpočtu kumulativních prodejů podle sumy prodejů u jednotlivých produktů budeme ve výpočtu potřebovat seřadit produkty sestupně podle měřítka [Prodeje], podobně jako na obrázku níže.
K tomuto účelu můžeme použít funkci WINDOW() následujícím způsobem.
Měřítko:
První proměnná nazvaná ProduktyAProdeje bude obsahovat virtuální tabulku se všemi produkty a s jejich prodeji. Tuto tabulku následně používáme ve funkci WINDOW(), která bude vracet v každém řádku vizuálu tabulku s produkty, kde výčet produktů bude začínat prvním produktem v absolutním pořadí podle prodejů, a končit aktuálním produktem v aktuálním řádku vizuálu. Jinými slovy, tabulka, kterou bude vracet funkce WINDOW(), bude obsahovat v každém řádku vizuálu všechny produkty, jejichž prodeje jsou větší nebo rovno prodejům aktuálního produktu v aktuálním řádku vizuálu. Tato tabulka bude následně přidána do filtru před vyhodnocením měřítka [Prodeje] v prvním argumentu funkce CALCULATE(). Více informací o stále ještě relativně nové DAX funkci WINDOW() můžete najít v samostatném příspěvku.
Jak můžeme vidět na obrázku výše, měřítko [Prodeje (RT produkty podle prodejů)] vrací kumulativní prodeje od produktu s nejvyššími prodeji po produkt s nejnižšími prodeji.
Pokud do vizuálu matice přidáme do řádků další sloupec, například sloupec s kategoriemi produktů, tak měřítko [Prodeje (RT produkty podle prodejů)] bude vracet kumulativní prodeje pouze v rámci každé kategorie. Důvodem je že ve filtru funkce CALCULATE() přepisujeme vnější filtry nastavené pouze na sloupec 'Product'[Product]. Vnější filtry nastavené na jiné sloupce z modelu ale na celý výpočet stále působí.
Jak můžeme vidět na obrázku výše, tak například v kategorii Clothing vrací měřítko [Prodeje (RT produkty podle prodejů)] kumulativní prodeje od produktu s nejvyššími prodeji v této kategorii po produkt s nejnižšími prodeji.
Výpočet v měřítku [Prodeje (RT produkty podle prodejů)] může být použit například jako základ pro vytvoření ABC analýzy produktů.
Shrnutí
Jak jsme si ukázali na příkladech v tomto příspěvku, výpočty kumulativních součtů se mohou lišit v závislosti na tom, pro jaký atribut je konkrétní výpočet vytvořen. Například při práci s datumovou tabulkou můžeme spoléhat na hierarchickou strukturu kalendářních atributů. Díky tomu můžeme při výpočtu kumulativních součtů pracovat na úrovni jednotlivých dnů, a výsledné měřítko zobrazovat v kontextu jakéhokoliv sloupce z datumové tabulky. Ostatní dimenzní tabulky ale obvykle nemají pevně danou hierarchickou strukturu, a běžně se tak může stát, že výpočet kumulativních součtů je určen k vyhodnocení pouze v kontextu jednoho konkrétního sloupce. V takovémto případě je vhodné programově ošetřit, aby se daný výpočet zobrazoval pouze v kontextu sloupce, pro který je určen, a to například pomocí kombinace funkce IF() s funkcemi ISINSCOPE() nebo HASONEVALUE().
Komentáře
Okomentovat