Pareto analýza v jazyku DAX a Power BI

Pareto pravidlo, známe také jako pravidlo 80/20 říká, že obvykle 80 % příčin pramení z 20 % důsledků. Pareto princip můžeme aplikovat na různé oblasti zájmu. V tomto příkladu použijeme pravidlo 80/20 na rozdělení produktů do dvou kategorií s cílem identifikovat ty produkty, jejichž prodeje tvoří 80 % celkových tržeb. Druhá kategorie bude obsahovat produkty, jejichž prodeje tvoří zbylých 20 % tržeb. Počet produktů, které tvoří 80 % tržeb, by měl podle tohoto pravidla tvořit přibližně 20% podíl z celkového počtu produktů.

Pro vytvoření příkladu použijeme cvičnou databázi v Power BI souboru Adventure Works DW 2020.pbix. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.

V použitém modelu budeme pracovat pouze se dvěma tabulkami, tabulkou 'Sales' a tabulku 'Product'. 

Tabulka 'Sales' obsahuje záznamy o prodejích produktů. V tabulce 'Sales' nás bude zajímat především sloupec 'Sales'[Sales Amount], který obsahuje částky za prodeje produktů. V tabulce 'Product' jsou k dispozici všechny produkty, které chceme rozdělit do dvou kategorií právě podle sumy prodejů pro daný produkt. Nejdříve si tedy vytvoříme měřítko, které bude počítat sumu za prodané produkty v aktuálním kontextu vyhodnocení. 

Měřítko:

Suma prodejů = SUM(Sales[Sales Amount])

Měřítko [Suma prodejů] budeme používat ve většině výpočtů a bude představovat hlavní kritérium pro rozdělení produktů do kategorií podle Paretova pravidla.

Pareto analýza v jazyku DAX

Celý výpočet se bude skládat ze čtyř navazujících kroků, pro které si vytvoříme čtyři počítané sloupce v tabulce 'Product'. V prvním kroku budeme potřebovat vypočítat sumu prodejů pro každý produkt v tabulce 'Product'. Ve druhém kroku si vytvoříme pomocný sloupec, který bude obsahovat kumulativní součet prodejů z prvního sloupce, seřazený od nejvyšší částky po nejnižší.  Třetím krokem bude převod hodnot ze sloupce s kumulativními součty na hodnoty vyjádřené procentuálně, ve vztahu k celkovým prodejům. Ve čtvrtém kroku již pouze rozdělíme produkty do jednotlivých kategorií. Nejdříve si vytvoříme celou analýzu krok po kroku, z důvodu snadnějšího znázornění celého procesu. Později si můžeme ukázat, jak celý výpočet vytvořit v jednom kroku v jednom počítaném sloupci.

Pareto analýza s pomocnými sloupci

Pro všechny čtyři kroky si v tabulce 'Product'  postupně vytvoříme čtyři samostatné sloupce, abychom si mohli názorně ukázat výsledek každého pomocného výpočtu.

1. Vytvoření sloupce se sumou prodejů pro jednotlivé produkty

V prvním kroku si vytvoříme nový počítaný sloupec v tabulce 'Product', do kterého pouze vložíme dříve vytvořené měřítko [Suma prodejů]. V tomto kroku je třeba si uvědomit, že každé měřítko je na pozadí obalené do funkce CALCULATE(). Funkce CALCULATE() mimo jiné umožňuje změnu kontextu řádku na kontext filtru, čehož můžeme využít právě pro získání hodnot prodejů z tabulky 'Sales' pro jednotlivé produkty v tabulce 'Product'. Definice nového počítaného sloupce je díky změně kontextu řádku na kontext filtru velmi jednoduchá, a vypadá následovně.

Počítaný sloupec:

Prodeje produktu = [Suma prodejů]

Výsledkem je nový počítaný sloupce se sumou prodejů pro jednotlivé produkty.

Nový počítaný sloupec obsahuje sumu prodejů pro jednotlivé produkty z tabulky 'Sales'. Pokud pro daný produkt není v aktuálním řádku žádná hodnota, znamená to, že daný produkt nemá žádné záznamy o prodejích v tabulce 'Sales'. Druhým krokem je výpočet kumulativních prodejů, od nejvyšší částky po nejnižší.

2. Vytvoření sloupce s kumulativním součtem prodejů, s řazením od nejvyšší částky po nejnižší

Ve druhém kroku potřebujeme v každém řádku tabulky sečíst všechny hodnoty z nového sloupce 'Product'[Prodeje produktu], které obsahují větší nebo stejnou hodnotu, jako je hodnota prodejů v aktuálním řádku. Výpočet pro nový počítaný sloupec může vypadat následovně.

Počítaný sloupec:

Prodeje kumulativně =

SUMX

(

FILTER

(

'Product',

'Product'[Prodeje produktu] >= EARLIER('Product'[Prodeje produktu])

),

'Product'[Prodeje produktu]

)

Výše uvedený výpočet je nejsložitější z celého příkladu. Pro lepší představu, jak výpočet funguje, musíme oddělit první tabulku 'Product', ve které vytváříme nový počítaný sloupec, a druhou tabulku 'Product', kterou filtrujeme ve funkci FILTER(). Jelikož obě dvě tabulky obsahují sloupec 'Product'[Prodeje produktu], musíme použít funkci EARLIER() pro přístup k aktuální hodnotě ze sloupce 'Product'[Prodeje produktu] ve vnější tabulce, ve které tvoříme nový počítaný sloupec. Funkce SUMX() má v každém řádku první tabulky, ve které vytváříme nový počítaný sloupec, k dispozici stejnou tabulku 'Product', která je ale zafiltrovaná podle hodnoty prodejů pro jednotlivé produkty, které jsou větší nebo rovny než hodnota prodejů pro aktuální produkt v tabulce, ve které je výpočet vyhodnocen. V takto zafiltrované tabulce sečteme v každém řádku nového počítaného sloupce všechny dostupné hodnoty ze sloupce 'Product'[Prodeje produktu].

Výsledkem je druhý počítaný sloupec, obsahující kumulativní součet prodejů, seřazený od nejvyšší hodnoty prodejů po nejnižší.

Dalším krokem je převod sumy kumulativních prodejů na procentuální podíl ve vztahu k celkovým prodejům.

3. Procentuální podíl kumulativních prodejů ve vztahu k celkovým prodejům

Výpočet procentuálního podílu k celku je poměrně jednoduchý, stačí vydělit v každém řádku tabulky aktuální hodnotu ze sloupce 'Product'[Prodeje kumulativně] hodnotou celkových prodejů. Výpočet třetího počítaného sloupce může vypadat takto.

Počítaný sloupec:

Kumulativní prodeje % =

DIVIDE

(

'Product'[Prodeje kumulativně],

SUM('Product'[Prodeje produktu])

)

Funkce DIVIDE() vrací výsledek po dělení prvního argumentu funkce hodnotou ve druhém argumentu funkce. V případě dělení nulou vrací tato funkce hodnotu BLANK(), případně alternativní výsledek, pokud by byl zadaný. 

Prvním argumentem funkce je odkaz na sloupec 'Product'[Prodeje kumulativně], čímž v každém řádku tabulky načteme aktuální hodnotu z tohoto sloupce. Druhým argumentem funkce DIVIDE() je výraz SUM('Product'[Prodeje produktu]). V tomto případě nemůžeme použít měřítko [Suma prodejů], protože to by vracelo stejnou hodnotu, jako je hodnota ve sloupci 'Product'[Prodeje produktu], díky funkci CALCULATE() na pozadí každého měřítka. Tím, že použijeme přímo funkci SUM(), která ignoruje kontext řádku, získáme v každém řádku tabulky dělitele se sumou celkových prodejů pro všechny produkty. 

Posledním krokem je rozdělení produktů do dvou kategorií.

4. Rozdělení produktů do dvou kategorií

K rozdělení produktů do dvou kategorií můžeme použít funkci IF(). Pokud je hodnota sloupce 'Product'[Kumulativní prodeje %] menší než hodnota 0.8, zařadíme produkty do kategorie A. Ostatní produkty zařadíme do kategorie B.

Počítaný sloupec:

Kategorie 80/20 =

IF

(

'Product'[Kumulativní prodeje %] <= 0.8,

"A",

"B"

)

Výsledek je nový počítaný sloupec, díky kterému máme produkty rozdělené do kategorií podle Paretova principu.

Protože je výsledkem celého výpočtu nový počítaný sloupec, můžeme tento sloupec použít v reportech v řádcích tabulky, v grafech nebo v průřezech pro filtrování ostatních vizuálů. 

Před zobrazením výsledku si můžeme přidat do modelu dvě nová měřítka. První měřítko bude počítat počet produktů v aktuálním kontextu vyhodnocení. Druhé měřítko bude vracet procentuální počet produktů ve vztahu k celkovému počtu produktů.

Měřítka:

Počet produktů = COUNTROWS('Product')

% Počet produktů =

DIVIDE

(

COUNTROWS('Product'),

CALCULATE

(

COUNTROWS('Product'),

REMOVEFILTERS('Product')

)

)

Nyní můžeme vložit hodnoty ze sloupce 'Product'[Kategorie 80/20] do řádků tabulky, a měřítka [Suma prodejů], [Počet produktů] a [% Počet produktů] do vizuálu tabulky.

Na obrázku výše můžeme vidět jednoduchou tabulku, ze které vyplývá, že 87 produktů z celkového množství 397 produktů tvoří téměř 80 % všech tržeb. Do vizuálu si samozřejmě přidat také samotné produkty a podívat se tak, které produkty patří do které kategorie.

Pohledů bychom mohli vytvořit více, nicméně cílem příkladu je vytvoření samotné analýzy. Podívejme se proto ještě na to, jak vytvořit celý výpočet v jednom kroku v jednom počítaném sloupci.

Pareto analýza v jednom kroku

Výše uvedený příklad rozdělený do čtyř navazujících kroků můžeme celý vypočítat v jednom kroku v jednom počítaném sloupci. Pro zjednodušení můžeme použít proměnné, do kterých si jednotlivé mezivýpočty uložíme pro jejich další použití. Definice celého výpočtu v jednom počítaném sloupci může vypadat následovně.

Počítaný sloupec:

Kategorie 80/20 vše v jednom =

VAR ProdejeProduktu =

ADDCOLUMNS

(

'Product',

"@ProdejeProduktu",

[Suma prodejů]

)

VAR ProdejeAktualniProdukt = [Suma prodejů]

VAR ProdejeKumulativne =

SUMX

(

FILTER

(

ProdejeProduktu,

[@ProdejeProduktu] >= ProdejeAktualniProdukt

),

[@ProdejeProduktu]

)

VAR ProdejeVse =

SUMX

(

ProdejeProduktu,

[@ProdejeProduktu]

)

VAR ProdejeKumulativneProcento =

DIVIDE

(

ProdejeKumulativne,

ProdejeVse

)

VAR Vysledek =

IF

(

ProdejeKumulativneProcento <= 0.8,

"A",

"B"

)

RETURN

Vysledek

Pokud bychom chtěli stejný počítaný sloupec vytvořit bez použití proměnných, výpočet by mohl vypadat následovně.

Počítaný sloupec:

Kategorie 80/20 vše v jednom bez proměnných =

IF

(

DIVIDE

(

SUMX

(

FILTER

(

ADDCOLUMNS('Product', "@ProdejeProduktu", [Suma prodejů]),

[@ProdejeProduktu] >=

CALCULATE

(

[Suma prodejů],

ALL('Product'),

'Product'[ProductKey] = EARLIEST('Product'[ProductKey])

)

),

[@ProdejeProduktu]

),

SUMX

(

ADDCOLUMNS('Product', "@ProdejeProduktu", [Suma prodejů]),

[@ProdejeProduktu]

)

)

<= 0.8,

"A",

"B"

)

Princip výpočtu bez použití proměnných je pořád stejný, pouze musíme jednotlivé kroky vnořit do sebe. Za zmínku stojí použití funkce EARLIEST(), namísto funkce EARLIER(). Funkce EARLIER(), kterou jsme použili pro přístup k vnější tabulce v případě výpočtu rozděleného do jednotlivých sloupců, vrací hodnotu ze sloupce v předchozí iteraci, než ve které je použita. V počítaném sloupci [Kategorie 80/20 vše v jednom bez proměnných] máme více vnořených iteračních funkcí, které obsahují sloupec 'Product'[ProductKey]. Pokud chceme přeskočit všechny iterační funkce k té nejvíce vnější, můžeme použít právě funkci EARLIEST(). Stejného výsledku bychom dosáhly s funkcí EARLIER(), pouze bychom museli definovat druhý argument této funkce, který by obsahoval počet přeskočených iteračních funkcí. V tomto případě by se jednalo o číslo 2 ve druhém argumentu funkce EARLIER().

Shrnutí

Celý výpočet můžeme použít univerzálně pro různé druhy kritérií. Pokud máme k dispozici potřebná data, můžeme díky Pareto analýze dostat rychlý přehled o konkrétní situaci. S malou úpravou můžeme stejný výpočet použít také pro tzv. ABC analýzu produktů. Současně může být celý příklad dobrým cvičením, jak je možné pracovat s jazykem DAX a jak můžeme stejného výsledku dosáhnout různými způsoby. Zejména technika přístupu  k hodnotám ze sloupců v různých úrovních ve vnořených iteracích může být užitečná také v mnoha jiných výpočtech.

Stáhnout soubor s řešením.

č. 33