Jedním ze základních výpočtů při práci s daty v Power BI, v Excelu nebo v SSAS Tabulárním modelu je výpočet procentuálních podílů. Tento článek popisuje dva přístupy k výpočtu procentuálního podílu. V prvním příkladu si ukážeme výpočet s použitím funkcí CALCULATE() a REMOVEFITERS(). V druhém příkladu použijeme funkci CALCULATE() v kombinaci s funkcí ALLSELECTED().
Výpočet procentuálních podílů v jazyku DAX a Power BI
V příkladech budeme pracovat se cvičným Power BI souborem Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s příklady z tohoto příspěvku je dostupný ke stažení pod tímto příspěvkem.
K tomuto tématu je k dispozici také video:
V první částí si ukážeme výpočet procentuálního podílu s funkcí REMOVEFILTERS(). Následovat bude výpočet procentuálního podílu s funkcí ALLSELECTED(), včetně zobrazení mezivýpočtů pro dělence a dělitele.
Ve všech příkladech budeme pracovat s jednoduchým měřítkem [Prodeje], ve kterém sčítáme částky za prodeje produktů, které najdeme ve sloupci 'Sales'[Sales Amount].
Měřítko:
Měřítko [Prodeje] bude vracet sumu za prodané produkty v aktuálním kontextu vyhodnocení.
V řádcích vizuálu jsou, stejně jako v průřezu na levé straně, kategorie produktů ze sloupce 'Product'[Category]. Měřítko [Prodeje] je pak v každém řádku vizuálu vyhodnoceno v kontextu filtru aktuální kategorie. Pokud bychom chtěli vypočítat procentuální podíl prodejů v kategorii vůči celkovým prodejům za všechny kategorie, potřebujeme vydělit aktuální hodnotu měřítka [Prodeje] prodeji za všechny kategorie, což je hodnota, kterou vidíme v řádku souhrnů celkem. Abychom v každém řádku vizuálu viděli prodeje za všechny kategorie, což je hodnota, kterou potřebujeme pro dělitele, musíme v děliteli před vyhodnocením měřítka [Prodeje] odstranit filtry ze sloupce použitého v řádcích vizuálu.
Výpočet procentuálního podílu s REMOVEFILTERS
Při výpočtu dělitele budeme chtít odstranit filtry pocházející z řádků vizuálu matice. K tomuto účelu můžeme použít funkci REMOVEFILTERS(). Definice dělitele s použitím funkce REMOVEFILTERS() může vypadat například následovně.
Poznámka: V příkladech v tomto příspěvku budeme pro odstranění filtrů používat funkci REMOVEFILTERS(). Stejného efektu bychom ale v tomto konkrétním příkladu dosáhli také pomocí starší funkce ALL().
Měřítko:
Nové měřítko si můžeme přidat do dříve vytvořeného vizuálu, spolu s původním měřítkem [Prodeje] a hodnotami ze sloupce 'Product'[Category] v řádcích.
Na obrázku výše můžeme vidět, že výsledek nového měřítka [Prodeje (všechny kategorie)] opravdu vrací v každém řádku vizuálu sumu prodejů za všechny kategorie. Tuto hodnotu tedy můžeme použít jako dělitele pro výpočet procentuálního podílu prodejů v jednotlivých kategoriích. Celý výpočet v jednom měřítku může vypadat například následovně.
Měřítko:
Pro výpočet podílu je použita funkce DIVIDE(), která automaticky ošetřuje dělení nulou. Prvním argumentem funkce DIVIDE() je měřítko [Prodeje], které vrací hodnotu prodejů v aktuálním kontextu vyhodnocení. Dělitel v druhém argumentu funkce DIVIDE() obsahuje stejný výpočet, který jsme použili dříve pro definici měřítka [Prodeje (všechny kategorie)]. Výsledek si můžeme zobrazit v dříve vytvořeném vizuálu.
Měřítko [% Prodeje (všechny kategorie)] vrací procentuální podíl prodejů v dané kategorii vůči prodejům ve všech kategoriích.
Zde je důležité si uvědomit, že v děliteli odstraňujeme před vyhodnocením měřítka [Prodeje] ze sloupce 'Product'[Category], a nikoliv přímo z řádků vizuálu matice. Jinými slovy odstraňujeme filtry ze sloupce 'Product'[Category], ať už je tento sloupec použitý kdekoliv v reportu.
Pokud tedy v průřezu, kde jsou také kategorie produktů, vybereme pouze některé kategorie, tento filtr nijak neovlivní dělitele v měřítku [% Prodeje (všechny kategorie)].
Měřítko [% Prodeje (všechny kategorie)] tak vrací pro každou kategorii procentuální podíl prodejů aktuální kategorie vůči prodejům za všechny kategorie. V řádku souhrnů pak vidíme procentuální podíl prodejů tří vybraných kategorií vůči prodejům za všechny kategorie.
Pokud bychom chtěli vytvořit tzv. vizuální procentuální podíl, tedy procentuální podíl prodejů aktuální kategorie vůči prodejům pouze za ty kategorie, které vidíme v řádcích vizuálu, stačí nahradit funkci REMOVEFILTERS() funkcí ALLSELECTED().
Výpočet procentuálního podílu s ALLSELECTED
Pro znázornění rozdílu mezi funkcí REMOVEFILTERS() a funkcí ALLSELECTED() při výpočtu procentuálního podílu si můžeme porovnat mezivýpočty pro dělitele s jednou a druhou funkcí.
Měřítka:
Pokud nepoužijeme filtr v průřezu, výsledek obou měřítek s výpočtem dělitele je stejný a představuje sumu prodejů za všechny kategorie.
Pokud ovšem použijeme filtr na určité kategorie produktů v průřezu, výsledky obou měřítek již budou jiné.
Měřítko [Prodeje (všechny kategorie)] stále vrací prodeje za všechny kategorie. Měřítko [Prodeje (všechny vybrané kategorie)] ale vrací prodeje pouze za ty kategorie, které vidíme v řádcích vizuálu a které jsou současně vybrané v průřezu.
Pokud tedy jako dělitele pro výpočet procentuálního podílu použijeme variantu s funkcí ALLSELECTED() s argumentem ve formě sloupce s kategoriemi produktů, pak bude dělitele obsahovat sumu prodejů za všechny kategorie vybrané v průřezu. Při výpočtu tedy dojde pouze k odstranění filtrů z řádků vizuálu, ale vnější filtry z průřezu jsou zachovány. Definice měřítka s výpočtem procentuálního podílu s použitím funkce ALLSELECTED() pak může vypadat následovně.
Poznámka: Výše popsané chování funkce ALLSELECTED() je velmi zjednodušené. Detailnější informace o této funkci můžete najít v samostatném příspěvku pod tímto odkazem.
Měřítko:
Nové měřítko si můžeme vložit do vizuálu spolu měřítkem [Prodeje] a s původním výpočtem procentuálních prodejů s použitím funkce REMOVEFILTERS().
Měřítko [% Prodeje (všechny kategorie)] vrací procentuální podíl prodejů aktuální kategorie vůči prodejům za všechny kategorie. Na druhou stranu, měřítko [% Prodeje (všechny vybrané kategorie)] vrací procentuální podíl prodejů v aktuální kategorii vůči prodejům za všechny vybrané kategorie v průřezu. V měřítku [% Prodeje (všechny vybrané kategorie)] tak počítáme tzv. vizuální procentuální podíl.
V tomto příspěvku jsme si na jednoduchém příkladu ukázali rozdíl mezi výpočtem procentuálního podílu s použitím funkce REMOVEFILTERS() a s použitím funkce ALLSELECTED(). Funkce ALLSELECTED() a REMOVEFILTERS() jsme používali s argumentem ve formě odkazu na sloupec s kategoriemi produktů. Tyto funkce však můžeme použít také s argumenty ve formě tabulek, nebo bez argumentů. Pokud použijeme funkce ALLSELECTED() a REMOVEFILTERS() bez argumentů, dojde k aplikování stejné logiky popsané v tomto příspěvku na všechny sloupce v celém modelu.
Při vytváření výpočtů podobných příkladům v tomto příspěvku musíme vždy pečlivě zvažovat, které filtry chceme odstranit a které naopak zachovat. Dále musíme uvažovat v kontextu vyhodnocení měřítek. Musíme tedy brát v úvahu filtry uvnitř vizuálu a také případné vnější filtry působící na výpočet z jiných vizuálů. Pokud si u komplexnějších výpočtů nejsme jisti, jaké hodnoty vrací jednotlivé dílčí výpočty uvnitř měřítka, je vždy dobré zobrazit si tyto dílčí výpočty samostatně, stejně jako jsme si výpočty jednotlivých mezivýpočtů zobrazovali v příkladech uvedených v tomto příspěvku.
Komentáře
Okomentovat