Výpočet průměru v jazyku DAX a Power BI

Výpočet průměru v jazyku DAX je jednoduchá úloha, a to především díky funkcím AVERAGE() a AVERAGEX(). Vypočítat průměrné hodnoty však můžeme také prostým dělením, kdy v dělenci bude suma jednotlivých hodnot, a v děliteli počet hodnot určených pro výpočet průměru. V tomto příspěvku si ukážeme různé způsoby výpočtu průměrů s použitím funkcí AVERAGE() a AVERAGEX(). Dále si ukážeme alternativní výpočet průměru s použitím jednoduchých DAX funkcí, ve kterém má autor DAX kódu větší svobodu, a to především v definici dělitele, což může být ve specifických typech výpočtů důležité pro dosažení očekávaných výsledků.

Výpočet průměru v jazyku DAX

Příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix. Powe BI soubor s řešenými příklady můžete najít 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 prodané produkty v aktuálním kontextu vyhodnocení. Pokud měřítko vložíme do vizuálu Matrix, spolu s hodnotami ze sloupce 'Reseller'[Reseller], výchozí report může vypadat následovně.

Na obrázku výše jsou v řádcích vizuálu názvy obchodů, a v hodnotách suma za prodané produkty přes jednotlivé obchody. Následují příklady výpočtu průměrných hodnot podle různých kritérií a s použitím různých DAX funkcí, s cílem vysvětlit rozdíl mezi jednotlivými přístupy. V prvním příkladu si ukážeme výpočet průměrné hodnoty z hodnot uvedených ve sloupci 'Sales'[Sales Amount] pomocí funkce AVERAGE(). Následovat budou příklady výpočtu průměrné objednávky, výpočet průměrných ročních tržeb a jeden specifický typ příkladu s výpočtem průměru, ve kterém musíme k dosažení správného výsledku použít vlastní logiku výpočtu, bez použití funkcí AVERAGE() a AVERAGEX().

Výpočet průměru pomocí funkce AVERAGE

Funkce AVERAGE() přijímá jeden argument ve formě odkazu na sloupec, ze kterého chceme spočítat průměr. Jedná se o nejjednodušší způsob, jak spočítat průměrné hodnoty. Následující měřítko bude vracet průměrnou hodnotu vypočítanou ze všech hodnot ze sloupce 'Sales'[Sales Amount], které jsou dostupné v aktuálním kontextu vyhodnocení.

Měřítko:

Průměrné částky (Average) = AVERAGE(Sales[Sales Amount])

Pokud si nové měřítko vložíme do původního vizuálu s obchody v řádcích, výsledek bude vypadat následovně.

Pokud se podíváme například na první řádek ve vizuálu na obrázku výše, tak prodejce "A Bike Store" inkasoval za prodeje produktů částku 85 177,08. Průměrná hodnota jednotlivých částek ze sloupce 'Sales'[Sales Amount] byla ve výši 1 166,81. Následující pomocné měřítko nám umožní lépe pochopit, z jakých hodnot byla vypočítána průměrná částka v měřítku [Průměrné částky (Average)].

Měřítko:

Počet řádků s uvedenou částkou =

COUNTROWS

(

    FILTER

    (

        Sales,

        NOT ISBLANK(Sales[Sales Amount])

    )

)

Měřítko [Počet řádků s uvedenou částkou] vrací počet řádků z tabulky 'Sales', které jsou dostupné v aktuálním kontextu vyhodnocení a ve kterých hodnota ve sloupci 'Sales'[Sales Amount] není BLANK. Přesně toto číslo je použito jako dělitel při výpočtu průměru v měřítku [Průměrné částky (Average)]. Nové měřítko si můžeme vložit do původního vizuálu a zobrazit si výsledek.

Na obrázku výše můžeme vidět jakým způsobem můžeme alternativně získat hodnotu, která je výsledkem funkce AVERAGE(). Funkce AVERAGE() sečte všechny hodnoty ze sloupce uvedeného v argumentu této funkce (tuto hodnotu představuje na obrázku výše měřítko [Prodeje]) a vydělí tuto sumu počtem hodnot ze stejného sloupce (měřítko [Počet řádků s uvedenou částkou]). Při použití funkce AVERAGE() tedy dostaneme průměr hodnot ze sloupce použitého v argumentu této funkce.

Co nám ale vlastně říká měřítko [Průměrné částky (Average)]? Při výpočtu průměrných hodnot musíme vždy pečlivě zvažovat strukturu modelu. Pro správnou interpretaci musíme přesně vědět, co znamená jeden záznam v tabulce, ze které počítáme průměrné hodnoty pomocí funkce AVERAGE(). V použitém modelu obsahuje jeden záznam v tabulce 'Sales' informaci o jednom řádku na fyzicky vystavené objednávce. Každá objednávka pak může mít přirozeně více řádků, podle toho, kolik si zákazník zakoupí různých druhů produktů. Pokud si zákazník zakoupí jeden produkt, bez ohledu na množství zakoupených kusů tohoto produktu, jedná se o jeden záznam na objednávce, a také o jeden záznam v tabulce 'Sales'. Pokud si zákazník zakoupí více produktů, bez ohledu na množství u každého z nich, objednávka bude obsahovat pro každý produkt jeden řádek, s uvedeným množstvím kusů a celkovou částkou, která se skládá z ceny jednoho kusu vynásobené celkovým množstvím. Tato hodnota je uložena ve sloupci 'Sales'[Sales Amount], ze kterého jsme počítali průměr pomocí funkce AVERAGE(). Pro znázornění se můžeme podívat na granularitu tabulky 'Sales'.

Zvýrazněné oblasti na obrázku výše představují řádky na společných objednávkách. Informace, ke které objednávce daný záznam patří, je uložena v tabulce 'Sales Order'. Čísla objednávek z této tabulky jsou vidět na obrázku výše v prvním sloupci.

Pokud se tedy vrátíme k měřítku [Průměrné částky (Average)], které počítá průměr z jednotlivých hodnot ze sloupce 'Sales'[Sales Amount], můžeme říci, že toto měřítko vrací průměrnou částku vypočítanou z jednotlivých řádků na objednávkách.  Tato hodnota sice může být zajímavá, nicméně pro analýzu chování zákazníků bude pravděpodobně zajímavější znát průměrnou hodnotu celé objednávky, a ne pouze jednotlivých řádků objednávky.

Jelikož jsou čísla objednávek v jiné tabulce a v jiné granularitě, než hodnoty ze kterých chceme počítat průměr, ke zjištění průměrné částky z objednávek si již nevystačíme s funkcí AVERAGE(). K výpočtu průměrné částky na objednávce použijeme funkci AVERAGEX(), díky které můžeme vytvářet měřítka, ve kterých máme větší svobodu při definici dělence i dělitele, tedy hodnot pro výpočet průměru.

Výpočet průměru pomocí funkce AVERAGEX

Funkce AVERAGEX() je iterační funkce se dvěma argumenty. V prvním argumentu funkce AVERAGEX() můžeme použít, stejně jako jakékoliv jiné iterační funkci, buď přímý odkaz na tabulku, která je součástí modelu, nebo jakoukoliv funkci vracející tabulku. Druhým argumentem funkce AVERAGEX() může být odkaz na kterýkoliv sloupec z tabulky uvedené v prvním argumentu funkce, nebo jakýkoliv výpočet, který bude vyhodnocen v kontextu řádku tabulky uvedené v prvním argumentu.

 Vrátíme-li se k výpočtu průměrné částky na objednávce, tak v prvním argumentu funkce AVERAGEX() použijeme tabulku, která bude obsahovat jeden sloupec s kódy objednávek. Ve druhém argumentu funkce AVERAGEX() pak použijeme výpočet, který bude pro každou objednávku vracet sumu ze všech řádků na dané objednávce. Takovýto výpočet už máme v modelu k dispozici, a jedná se o měřítko [Prodeje]. Pokud bude toto měřítko vyhodnoceno v kontextu řádku každé objednávky, bude pro každou jednu objednávku vracet sumu všech hodnot ze sloupce 'Sales'[Sales Amount], které patří k právě iterované objednávce, a to díky změně kontextu řádku na kontext filtru. Celý výpočet průměrné částky na objednávkách může vypadat následovně.

Měřítko:

Průměrné objednávky (Averagex) =

AVERAGEX

(

    VALUES('Sales Order'[Sales Order]),

    [Prodeje]

)

Nové měřítko si můžeme vložit do původního vizuálu a zobrazit si výsledky výpočtu.

Jak můžeme vidět na obrázku výše, měřítko [Průměrné objednávky (Averagex)] vrací výrazně vyšší hodnoty, než původní měřítko [Průměrné částky (Average)]. Rozdíl mezi těmito výpočty by již nyní měl být zřejmý. Měřítko [Průměrné částky (Average)] vrací průměrnou hodnotu jednoho řádku na objednávce. Měřítko [Průměrné objednávky (Averagex)] vrací průměrnou částku vypočítanou na základě hodnot z celých objednávek.

Hodnotu dělitele pro výpočet průměru v měřítku [Průměrné objednávky (Averagex)] si opět můžeme zobrazit přímo ve vizuálu, například pomocí následujícího měřítka.

Měřítko:

Počet objednávek =

COUNTROWS

(

    FILTER

    (

        VALUES('Sales Order'[Sales Order]),

        NOT ISBLANK([Prodeje])

    )

)

Měřítko [Počet objednávek] na obrázku níže představuje dělitele, který je použit pro výpočet průměrných objednávek, zatímco měřítko [Prodeje] představuje v tomto výpočtu dělence.

Pokud tedy používáme funkci AVERAGEX(), tak dělenec pro výpočet průměru představuje sumu všech jednotlivých hodnot, které vrací výraz uvedený ve druhém argumentu funkce, vyhodnocený v kontextu řádku tabulky v prvním argumentu. Dělitel je pak počet těchto hodnot, tedy počet řádků z tabulky v první argumentu, pro které platí, že výraz v druhém argumentu funkce vrací hodnotu a není BLANK.

Princip výpočtů průměrné sumy na objednávkách můžeme aplikovat také na jiné atributy v modelu. Obdobným způsobem si můžeme vytvořit například měřítko, které bude počítat průměrné roční tržby.

Měřítko:

Průměrné roční tržby (Averagex) =

AVERAGEX

(

    VALUES('Date'[Rok]),

    [Prodeje]

)

Logika výpočtu je stejná jako při výpočtu průměrných objednávek, pouze nyní v prvním argumentu funkce AVERAGEX() používáme tabulku se sloupcem, který obsahuje roky z kalendářní tabulky. Pro názornost si opět můžeme vytvořit pomocné měřítko s počtem roků, ve kterých došlo v aktuálním kontextu vyhodnocení k prodejům, v našem příkladu tedy s počtem roků, ve kterých měl daný obchod otevřeno a prodával produkty.

Měřítko:

Počet roků s prodeji =

COUNTROWS

(

    FILTER

    (

        VALUES('Date'[Rok]),

        NOT ISBLANK([Prodeje])

    )

)

Před zobrazením výsledků výpočtů, které se vtahují průměrným ročním tržbám, si ještě můžeme do modelu přidat jedno měřítko, které bude vracet počet všech roků dostupných v aktuálním kontextu vyhodnocení.

Měřítko:

Počet roků = COUNTROWS(VALUES('Date'[Rok]))

V měřítku [Počet roků] již není žádný filtr. Výsledkem proto budou všechny roky dostupné v aktuálním kontextu vyhodnocení. Všechna tři měřítka si můžeme vložit do původního vizuálu s jednotlivými obchody v řádcích, a zobrazit si výsledky.

Na obrázku výše můžeme na první pohled vidět, že k výpočtu průměrných tržeb pomocí funkce AVERAGEX() je v děliteli použita hodnota, kterou vrací měřítko [Počet roků s prodeji]. Toto měřítko vrací počet let, pro které měl daný obchod v aktuálním řádku otevřeno, jinak řečeno pro ty roky, ve kterých měřítko [Prodeje] vrací jakoukoliv hodnotu, která není BLANK. Měřítko [Počet roků] vrací na obrázku výše v každém řádku stejnou hodnotu, která představuje počet všech let z kalendářní tabulky.

Rozdíl souvisí s tím, že v průběhu času došlo k zavření některých obchodů, jiné obchody měly otevřeno pouze v některých letech, některé nové obchody vznikaly, atd. Dělitel ve funkci AVERAGEX() ale obsahuje počet let, ve kterých vrací měřítko [Prodeje] hodnoty, to znamená počet let, ve kterých měl daný obchod otevřeno.

Toto chování funkce AVERAGEX() je naprosto přirozené  a správné. Zaměříme-li se například na obchod "A Bike Store" v prvním řádku vizuálu na obrázku výše, tak je zřejmé, že pokud měl daný obchod otevřeno pouze dva roky, tak průměrné roční prodeje v tomto obchodě budeme počítat pouze na základě dvou let.

V některých specifických typech výpočtů ale může být tato přirozená vlastnost funkcí AVERAGE() a AVERAGEX() nežádoucí, jak si ukážeme na následujícím příkladu.

Vlastní výpočet průměru

Pro zjednodušení se nyní přesuneme od původního modelu v použitém souboru, který obsahuje data o činnosti společnosti Adwenture Works, k jednoduché tabulce, která má následující strukturu.

Na obrázku výše můžeme vidět jednoduchou tabulku, která obsahuje fiktivní záznamy o jízdách dvou autobusů. Každý autobus urazí během dne čtyři cesty, dvakrát z jednoho místa do druhého a zpět. V posledním sloupci můžeme vidět počet cestujících, kteří danou cestu autobusem absolvovali. Ve čtvrtém řádku můžeme v posledním sloupci vidět, že v rámci této cesty nejel autobusem žádný cestující. Měřítko, které bude vracet průměrný počet cestujících, může vypadat například následovně.

Měřítko:

Průměrný počet cestujících (Average) = AVERAGE('Počty cestujících'[Počet cestujících])

Než si zobrazíme výsledek měřítka [Průměrný počet cestujících (Average)], přidejme si do modelu ještě dvě pomocná měřítka. První měřítko bude vracet součet přepravených cestujících.

Měřítko:

Součet počtu cestujících = SUM('Počty cestujících'[Počet cestujících])

Druhé měřítko bude vracet počet jízd.

Měřítko:

Počet jízd = COUNTROWS('Počty cestujících')

Nyní si můžeme vložit do řádků vizuálu Matrix názvy autobusů, a do hodnot všechna tři nová měřítka.

Pokud budeme vycházet z předpokladu, že "Autobus 1" absolvoval všechny čtyři cesty, a že v jedné z cest nenastoupil do autobusu žádný cestující, výsledek měřítka [Průměrný počet cestujících (Average)] není správný. Na první pohled můžeme vidět, že 44 / 4 není 14,67. Průměrný počet cestujících pro "Autobus 1" by v tomto případě měl být 11. Správný výpočet průměrného počtu cestujících by v tomto příkladu mohl vypadat následovně.

Měřítko:

Průměrný počet cestujících (Divide) =

DIVIDE

(

    SUM('Počty cestujících'[Počet cestujících]),

    COUNTROWS('Počty cestujících')

)

Nové měřítko si můžeme pro porovnání vložit do původního vizuálu.

Jak můžeme vidět na obrázku výše, měřítko [Průměrný počet cestujících (Divide)] nyní vrací správné výsledky.  Na tomto specifickém příkladu tak můžeme vidět, že v některých situacích je nutné použít pro výpočet průměru vlastní výpočet, namísto funkcí AVERAGE() nebo AVERAGEX(). Funkce AVERAGE() a AVERAGEX() totiž nezařadí do výpočtu průměru prázdné hodnoty BLANK. V situaci, kdy chceme do výpočtu průměru zahrnout také řádky s prázdnými hodnotami BLANK, musíme vytvořit alternativní výpočet s vlastní logikou.

Před opuštěním tohoto posledního příkladu je třeba dodat, že tabulka se záznamy jízd není z pohledu datového modelování vytvořená správně. Ve správně vytvořené tabulce by měla být pro záznam, ve kterém nejeli autobusem žádní cestující, uvedena nula, namísto prázdné hodnoty BLANK. V takovém případě již by vracela funkce AVERAGE() správné výsledky, protože nula je hodnota jako kterákoliv jiná, a celkový počet cestujících by byl pro výpočet průměru vydělen číslem 4, stejně jako tomu bylo v případě vlastního výpočtu pomocí funkce DIVIDE().

Shrnutí

Výpočet průměru z hodnot v jednom sloupci je v jazyku DAX relativně jednoduchá úloha, především díky funkci AVERAGE(), která přijímá argument ve formě odkazu na sloupec, ze kterého chceme průměr vypočítat. Pomocí funkce AVERAGEX() pak můžeme vytvářet pokročilejší výpočty. Ve funkci AVERAGEX() totiž můžeme v prvním argumentu použít jakoukoliv tabulku, a v druhém argumentu jakýkoliv výraz, který bude vyhodnocen v kontextu řádku tabulky v prvním argumentu funkce. Funkce AVERAGE() a AVERAGEX() nepočítají s prázdnými hodnotami BLANK. Tato vlastnost je přirozená a správná. Pokud ovšem potřebujeme započítat do průměru také prázdné hodnoty BLANK, například z důvodu špatně vytvořených záznamů, můžeme použít k výpočtu průměru vlastní výpočet.

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