Na osy grafů, do řádků nebo sloupců vizuálů anebo do Power BI průřezů můžeme vložit pouze hodnoty ze sloupců, které jsou součástí tabulek nahraných v modelu. V tomto příspěvku si ukážeme trik, jak přiřadit k hodnotám ze sloupců výsledek měřítka, což může imitovat samotné použití měřítka tam, kde můžeme používat pouze hodnoty ze sloupců.
K tomuto tématu je k dispozici také video:
Pro znázornění celého řešení použijeme vlastní kategorie produktů. Do které kategorie daný produkt patří budeme zjišťovat přímo v měřítku, protože celé řešení, aby dávalo smysl, musí být dynamické. Začneme tedy vytvořením měřítka, které bude vracet kategorie produktů podle jejich prodejů a následně si ukážeme postup, jak imitovat použití tohoto měřítka s kategoriemi v průřezu anebo v řádcích vizuálu.
Rozdělení produktů do kategorií podle prodejů dynamicky v měřítku
Měřítko, které si pro tento příklad vytvoříme, bude vracet čtyři kategorie produktů. Kategorie pojmenujeme jednoduše podle abecedního pořadí. Nejlepší produkty podle prodejů budou v kategorii A, další produkty v pořadí budou v kategorie B, a tak dále až po kategorii D.
Hranice prodejů pro zařazení aktuálního produktu do jedné z kategorií budou plně dynamické, to znamená že budou reagovat na vnější filtry působící na výpočet. Horní hranice pro zařazení produktů do kategorie A bude dána hodnotou prodejů produktu s nejvyššími prodeji v aktuálním kontextu vyhodnocení. Spodní hranice kategorie D bude dána prodeji produktu s nejnižšími prodeji v aktuálním kontextu vyhodnocení. Na základě těchto dvou krajních hodnot si následně dopočítáme zbývající spodní a horní hranice pro všechny čtyři kategorie.
Pro lepší vysvětlení celého rozdělení si můžeme uvést jednoduchý příklad. Produkt s nejvyššími prodeji měl prodeje 200. Produkt s nejnižšími prodeji 100. Kategorie D bude obsahovat produkty v rozmezí 100 až 125, kategorie C bude obsahovat produkty v rozmezí 125 až 150, kategorie B produkty v rozmezí 150 až 175 a kategorie A produkty s prodeji od 175 do 200.
Na základě logiky popsané v předchozím ilustrativním příkladu si vytvoříme měřítko, které bude vracet čtyři kategorie produktů. Důležité je si uvědomit, že spodní a horní hranice pro vytvoření jednotlivých kategorií je dána prodeji nejlepšího a nejhoršího produktu v aktuálním kontextu vyhodnocení, a všechny ostatní hranice všech ostatních kategorií se od těchto dvou hodnot budou odvíjet. To znamená, že pokud se budeme dívat na produkty například za všechny roky, hranice pro zařazení produktu do kategorie A budou výrazně vyšší, než pokud se budeme dívat na produkty a jejich prodeje pouze v jednom konkrétním roce.
Stejným způsobem může rozsah jednotlivých kategorií ovlivňovat jakýkoliv filtr působící na výpočet v reportu. To je důvod, proč tyto kategorie nemůžeme jednoduše vytvořit v počítaném sloupci, protože nasimulovat všechny kombinace filtrů, které může uživatel vytvořit v reportu by bylo velmi obtížné.
Měřítko, které bude vracet kategorie produktů, bude vypadat následovně.
Měřítko:
Ve výše uvedeném měřítku dojde nejdříve k vytvoření virtuální tabulky v proměnné ProduktyAProdeje, která obsahuje dva sloupce. První sloupec s názvy produktů a druhý sloupec s prodeji těchto produktů. Následně je pomocí funkce MINX(), respektive MAXX(), vybrána nejnižší a nejvyšší hodnota prodejů z této virtuální tabulky.
Pokud odečteme od prodejů nejlepšího produktu prodeje nejhoršího produktu, dostaneme rozsah prodejů, ve kterém jsou všechny produkty. Když tento rozsah vydělíme číslem 4 (čtyři kategorie), dostaneme rozsah jedné kategorie, což je hodnota uložená v proměnné RozsahKategorie.
K zařazení produktu do příslušné kategorie dojde v proměnné Vypocet, kde jednoduše porovnáváme, jestli prodeje aktuálního produktu patří do příslušné kategorie.
Pokud nové měřítko vložíme do vizuálu matice, spolu s produkty v řádcích a s měřítkem [Prodeje] v hodnotách, výsledek bude vypadat následovně.
Měřítko [Kategorie produktů] vrací pro každý produkt kategorii, a to podle prodejů aktuálního produktu a rozsahu kategorií. Na obrázku výše pak můžeme například vidět, že produkty v kategorii A měly rozsah prodejů mezi 3 309 673, 22 a 4 400 592,80. Pokud ale do reportu přidáme například průřez s roky, a vybereme pouze jeden rok, změní se přirozeně hodnoty měřítka [Prodeje], a stejně tak se změní rozsah jednotlivých kategorií.
Jak je možné vidět na obrázku výše, nyní jsou v kategorii A produkty s prodeji v rozsahu 928 454,73 až 1 236 157,81. Výběrem jednoho roku v průřezu se změní hodnoty prodejů u jednotlivých produktů, a dojde také k přepočítání hranic pro jednotlivé kategorie, protože ty jsou odvozeny dynamicky od produktu s nejvyššími a nejnižšími prodeji v aktuálním kontextu vyhodnocení.
Měřítko [Kategorie produktů] nyní vrací požadované kategorie, nicméně zobrazovat tyto kategorie v měřítku není příliš efektivní. Obecně se totiž kategorie používají k filtrování nebo ke shlukování atributů z nižších úrovní v tabulkách. Měřítko ale nemůžeme použít v průřezu, stejně jako na osách grafů anebo v řádcích a sloupcích tabulek.
V následující části příspěvku si proto ukážeme postup, jak toto omezení obejít a jak imitovat použití měřítka tam, kde patří hodnoty ze sloupců nahraných v modelu.
Přiřazení hodnoty měřítka k hodnotám ze sloupce z modelu
Celý následující postup se bude skládat ze dvou kroků. V prvním kroku si vytvoříme odpojenou tabulku, která bude obsahovat jeden sloupec se všemi hodnotami, které může vracet měřítko [Kategorie produktů].
Tuto tabulku si můžeme vytvořit jednoduše jako počítanou tabulku pomocí následujícího DAX kódu.
Počítaná tabulka:
Výsledná tabulka obsahuje jeden sloupec se čtyřmi kategoriemi. Jedná se o všechny čtyři kategorie, které může vracet měřítko [Kategorie produktů].
Tabulka 'Kategorie produktů' bude odpojená od zbytku tabulek v modelu, tzn. že nebudeme tuto tabulku připojovat pomocí relací k ostatním tabulkám v modelu.
Sloupec s kategoriemi produktů z této nové tabulky nyní můžeme použít například v průřezu.
Jak můžeme vidět na obrázku výše, v průřezu jsou vybrané pouze dvě kategorie, nicméně tento filtr ještě nijak neovlivňuje to co vidíme ve vizuálu matice, kde jsou stále všechny produkty ve všech kategoriích.
Ve druhém kroku si vytvoříme pomocné měřítko, které později použijeme pro filtrování vizuálu matice v postranním panelu filtrů.
Toto nové měřítku bude vracet číslo 1 v případě, že kategorie aktuálního produktu, kterou vrací měřítko [Kategorie produktů], bude v rozsahu kategorií ve sloupci 'Kategorie produktů'[Kategorie], které jsou dostupné v aktuálním kontextu vyhodnocení. V opačném případě bude výsledkem měřítka číslovka 0.
Měřítko:
Pro kontrolu si můžeme měřítko [Filtr kategorií produktů] vložit do vizuálu matice a zobrazit si jeho výsledky.
Měřítko [Filtr kategorií produktů] nyní vrací číslo 1 pro produkty, jejichž kategorie jsou vybrané také v průřezu. Jedná se o kategorie A a B. U kategorií C a D vrací měřítko číslo 0. Pokud nyní zafiltrujeme vizuál matice pouze na ty řádky, ve kterých vrací měřítko [Filtr kategorií produktů] číslo 1, dosáhneme požadovaného efektu.
V této chvíli již můžeme odebrat měřítka [Kategorie produktů] a [Filtr kategorií produktů] z vizuálu matice, a nastavit v postranním panelu filtrů filtr na základě hodnoty měřítka [Filtr kategorií produktů] následujícím způsobem.
Poznámka: Před nastavením filtru v postranním panelu filtrů je nutné mít označený vizuál, pro který chceme tento filtr použít. V opačném případě bychom nastavovali filtr na celou aktuální stránku reportu, případně na všechny stránky.
Jakmile máme nastavený filtr v postranním panelu filtrů, výběr kategorie v průřezu bude filtrovat vizuál matice.
Jak můžeme vidět na obrázku výše, v průřezu jsou nyní vybrané pouze dvě kategorie, a produkty které vidíme ve vizuálu matice patří právě do těchto vybraných kategorií.
Sloupec s kategoriemi produktů, který je nyní použitý v průřezu, můžeme vložit také do řádků vizuálu matice.
I přesto že používáme v průřezu a v řádcích vizuálu kategorie, které jsou v tabulce nahrané v modelu, přiřazení produktů do těchto kategorií je plně dynamické, protože závisí na výsledku měřítka [Kategorie produktů].
Pokud přidáme průřez například s roky z kalendářní tabulky, a vybereme pouze jeden rok, celý vizuál matice se přepočítá, včetně kategorií produktů.
Podobným způsobem bychom mohli přidat filtr například na konkrétní obchody, konkrétní zákazníky atd. Vždy bychom následně viděli produkty v jednotlivých kategoriích, rozdělené rovnoměrně podle sumy za prodeje těchto produktů v aktuálním kontextu vyhodnocení. Stejně tak se nemusíme omezovat pouze na použití vizuálu matice, ale podobným způsobem můžeme pracovat také s grafy a jinými Power BI vizuály.
Komentáře
Okomentovat