Výpočet procentuálního podílu v hierarchii v Power BI a DAX

Vložil Petr Tkadlčík

Úvodní obrázek

Cílem příkladu v tomto příspěvku je vytvořit v Power BI výpočet, který bude vracet procentuální podíl prodejů vůči nadřazené položce v hierarchii. Pracovat budeme s hierarchií která osahuje čtyři úrovně. Nejdříve si ukážeme, jak vytvořit výpočty pro každou jednu úroveň samostatně. Následně si vytvoříme jedno měřítko, které bude dynamicky používat různé výpočty podle toho, v jaké úrovni se aktuálně v použité hierarchii nacházíme.

K tomuto tématu je k dispozici také video:

Příklad popsaný v tomto příspěvku je vytvořený ve cvičném Power BI souboru Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je k dispozici ke stažení níže pod tímto příspěvkem.

V příkladech budeme pracovat s měřítkem [Prodeje], které má následující definici.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Měřítko [Prodeje] bude vracet sumu za prodeje produktů v aktuálním kontextu vyhodnocení.

Výpočet procentuálního podílu prodejů v hierarchii 

Hierarchie v tabulce 'Product' v použitém soboru obsahuje čtyři úrovně - kategorie, podkategorie, modely a produkty.

Výpočet procentuálního podílu v hierarchii v Power BI a DAX

Pokud takto vytvořenou hierarchii vložíme do řádků vizuálu Matice, spolu s měřítkem [Prodeje] v hodnotách, výchozí vizuál, se kterým budeme pracovat může vypadat následovně.  

Výpočet procentuálního podílu v hierarchii v Power BI a DAX 2

Protože použitá hierarchie obsahuje čtyři úrovně, budeme postupně tvořit čtyři samostatné výpočty pro každou jednotlivou úroveň. Začít můžeme nejvyšší úrovní, kde budeme chtít vydělit prodeje v aktuální kategorii vůči prodejům za všechny kategorie. Takovýto výpočet může vypadat například následovně.

Měřítko:

% Podíl na celku =
DIVIDE
(
    [Prodeje],
    CALCULATE
    (
        [Prodeje],
        REMOVEFILTERS('Product'[Category])
    )  
)

Měřítko [% Podíl na celku] obsahuje v prvním argumentu funkce DIVIDE() měřítko [Prodeje], které bude na úrovni kategorií vracet hodnotu prodejů za produkty v dané kategorii. Druhým argumentem funkce DIVIDE() je funkce CALCULATE(), ve které před vyhodnocením měřítka [Prodeje] odstraňujeme filtry ze sloupce s kategoriemi produktů, a dostaneme tak v děliteli u každé kategorie prodeje za všechny kategorie. Na úrovni kategorií je tak výsledkem tohoto nového měřítka procentuální podíl prodejů v dané kategorii vůči prodejům za všechny kategorie.

Výpočet procentuálního podílu v hierarchii v Power BI a DAX 3

Pokud se ale posuneme v hierarchii na nižší úroveň, měřítko [% Podíl na celku] již bude vracet vždy 100 %.

Výpočet procentuálního podílu v hierarchii v Power BI a DAX 4

Na nižší úrovni, nyní na úrovni podkategorií již musíme použít jiný výpočet, který bude počítat procentuální podíl prodejů v aktuální podkategorii vůči všem podkategoriím patřícím do nadřazené kategorie. V děliteli proto budeme odstraňovat filtry ze sloupce s podkategoriemi produktů.

Měřítko:

% Podíl v kategorii =
DIVIDE
(
    [Prodeje],
    CALCULATE
    (
        [Prodeje],
        REMOVEFILTERS('Product'[Subcategory])
    )
)

Obdobným způsobem si můžeme vytvořit také měřítka pro dvě zbývající úrovně.

Měřítka:

% Podíl v podkategorii =
DIVIDE
(
    [Prodeje],
    CALCULATE
    (
        [Prodeje],
        REMOVEFILTERS('Product'[Model])
    )
)

% Podíl v modelu =
DIVIDE
(
    [Prodeje],
    CALCULATE
    (
        [Prodeje],
        REMOVEFILTERS('Product'[Product])
    )
)

Pokud takto vytvořená měřítka vložíme do původního vizuálu Matice, můžeme vidět, že každé měřítko vrací očekávaný výsledek pouze v úrovni hierarchie, pro kterou bylo vytvořeno.

Výpočet procentuálního podílu v hierarchii v Power BI a DAX 5

Například měřítko [% Podíl v podkategorii] vrací očekávané výsledky pouze v úrovni obsahující v řádcích modely. Ve zvýrazněné buňce na obrázku výše odpovídá výsledek měřítka [% Podíl v podkategorii] procentuálnímu podílu prodejů modelu "Road - 150"  na celkových prodejích v podkategorii "Road-Bikes". V této buňce tedy dělíme hodnotu 7 913 701,93 hodnotnou 43 878 791,00. Stejný postup bychom mohli aplikovat při interpretaci měřítka [% Podíl v podkategorii] pro kterýkoliv model. 

Stejné měřítko již ale není relevantní pro ostatní úrovně v hierarchii, protože toto měřítko bylo vytvořeno pouze pro vybranou úroveň v hierarchii. V následující části příspěvku si proto ukážeme, jak zobrazovat výpočty procentuálních podílů v hierarchii pouze v úrovni, pro kterou je daný výpočet určen.

Výpočet podle aktuální úrovně v hierarchii

Zjistit aktuální úroveň v hierarchii můžeme více různými způsoby. Pro tento příklad bude nejspolehlivější použití funkce ISINSCOPE(). Funkce ISINSCOPE() vrací hodnotu TRUE(), pokud je sloupec, který vložíme do argumentu této funkce filtrovaný pomocí dotazu vygenerovaného na pozadí použitého vizuálu. Tuto informaci následně můžeme použít pro výběr výpočtu, který se má v aktuální úrovni v hierarchii použít. K tomu použijeme funkci SWITCH(), která vrací první hodnotu ze seznamu hodnot, která odpovídá podmínce v prvním argumentu funkce. Celý výpočet může být vytvořen pomocí jednoho měřítka. Výpočty, které jsme použili dříve pro vytvoření čtyř měřítek, můžeme použít pro definici proměnných, které následně použijeme podle toho, v jaké úrovni hierarchie se při výpočtu nacházíme. Celý takto popsaný výpočet může vypadat následovně.

Měřítko:

% Podíl v hierarchii =
VAR PodilVModelu =
    DIVIDE
    (
        [Prodeje],
        CALCULATE
        (
            [Prodeje],
            REMOVEFILTERS('Product'[Product])
        )
    )
VAR PodilVPodkategorii =
    DIVIDE
    (
        [Prodeje],
        CALCULATE
        (
            [Prodeje],
            REMOVEFILTERS('Product'[Model])
        )
    )
VAR PodilVKategorii =
    DIVIDE
    (
        [Prodeje],
        CALCULATE
        (
            [Prodeje],
            REMOVEFILTERS('Product'[Subcategory])
        )
    )
VAR PodilNaCelku =
    DIVIDE
    (
        [Prodeje],
        CALCULATE
        (
            [Prodeje],
            REMOVEFILTERS('Product'[Category])
        )  
    )
VAR Vypocet =
    SWITCH
    (
        TRUE(),
        ISINSCOPE('Product'[Product]), PodilVModelu,
        ISINSCOPE('Product'[Model]), PodilVPodkategorii,
        ISINSCOPE('Product'[Subcategory]), PodilVKategorii,
        ISINSCOPE('Product'[Category]), PodilNaCelku,
        BLANK()
    )
RETURN
    Vypocet

Výše uvedené měřítko můžeme vložit do původního vizuálu a odebrat čtyři dříve vytvořená měřítka. Výsledný vizuál nyní bude vypadat následovně.

Výpočet procentuálního podílu v hierarchii v Power BI a DAX 6

Měřítko [% Podíl v hierarchii] vrací procentuální podíl prodejů aktuální položky k nadřazené položce v hierarchii. V prvním řádku tabulky představuje hodnota měřítka, která je 1,16 %, procentuální velikost prodejů produktů z kategorie "Accessories" na celkových prodejích za všechny kategorie. Ve třetím řádku měřítko vrací procentuální podíl prodejů v podkategorii "Mountain Bikes" (38,52 %) na celkových prodejích kategorie "Bikes".  V pátém řádku počítáme procentuální prodeje modelů "Road - 150" vztažených k podkategorii "Road Bikes", a tak dále.

Další praktické příklady můžete najít na stránce DAX příklady nebo na stránce Power BI. Informace o základních principech jazyka DAX a o vybraných DAX funkcích můžete najít na stránce Jazyk DAX. Všechny video návody jsou k dispozici na Youtubovém kanále pod tímto odkazem.

č. 21

Komentáře

Okomentovat