Porovnávat průměrné denní prodeje v různých dnech týdne může mít smysl z celé řady provozních důvodů. Při výpočtu průměrných denních prodejů v jazyku DAX ale musíme být obezřetní, protože kalendářní tabulka nahraná v modelu může obsahovat více dnů, než pro které máme k dispozici data v ostatních tabulkách. V tomto příspěvku si proto iterativní cestou ukážeme, na jaké problémy můžeme při výpočtu průměrných denní prodejů narazit.
K tomuto tématu je k dispozici také video:
Pracovat budeme jako obvykle se cvičným Power BI souborem Adwenture Works DW 2020.pbix, ve kterém je navíc měřítko prodeje, které má následující definici.
Měřítko:
Pokud si do řádků vizuálu matice vložíme roky a měsíce z kalendářní tabulky, a do hodnot měřítko [Prodeje], výchozí vizuál, se kterým budeme pracovat, může vypadat například následovně.
Při výpočtu průměrných denních prodejů budeme rozlišovat mezi prodeji v pracovní a nepracovní dny. Pro zjednodušení budeme jako pracovní dny uvažovat všechny dny od pondělí do pátku, a jako nepracovní dny víkendy.
Pro tento účel si můžeme v tabulce 'Date' vytvořit pomocný počítaný sloupec, který bude vracet hodnotu TRUE pokud je aktuální den v aktuálním řádku tabulky 'Date' pracovní den, a hodnotu FALSE pokud je aktuální den nepracovní den, v našem případě víkend.
Počítaný sloupec v tabulce 'Date':
Nový pomocný sloupec bude obsahovat pouze hodnoty TRUE nebo FALSE. Pokud bychom chtěli vytvořit například měřítko, které bude vracet pouze prodeje uskutečněné v pracovní dny, můžeme použít následující výpočet.
Měřítko:
Nové měřítko [Prodeje (prac. dny)] bude přirozeně vracet nižší hodnoty než měřítko [Prodeje], protože měřítko [Prodeje (prac. dny)] nevrací částky za prodeje uskutečněné o víkendech.
Rozlišovat mezi prodeji v pracovní a nepracovní dny není nic složitého. Problémem je že nemůžeme spravedlivě porovnávat prodeje v pracovní a nepracovní dny v absolutních hodnotách, protože pracovních dnů je v každém měsíci výrazně více než nepracovních dnů. Proto budeme při porovnání pracovat s denními průměry.
Ve výpočtu průměrných denních prodejů budeme dělit sumu za prodeje produktů v aktuálním období počtem dnů v daném období. Tento výpočet zní jednoduše, nicméně jak si postupně vysvětlíme, problémem bude správně určit počet dnů pro hodnotu v děliteli.
V následující části na chvíli opustíme problematiku pracovních a nepracovních dnů, a zaměříme se pouze na zjištění počtu všech dnů v aktuálním období, které budeme používat pro výpočet průměrných denních prodejů.
Průměrné denní prodeje v jazyku DAX
V první verzi výpočtu dělitele budeme jednoduše sčítat počet řádků z kalendářní tabulky, které jsou dostupné v aktuálním kontextu vyhodnocení.
Měřítko:
Pokud nové měřítko vložíme do našeho původního vizuálu s roky a měsíci v řádcích, výsledek bude vypadat následovně.
Nové měřítko [Počet dnů v1] vrací počet dnů v aktuálním měsíci nebo roce, což se může na první pohled zdát jako požadovaná hodnota pro výpočet denních průměrů.
Můžeme si tak vytvořit další měřítko, které již bude vracet průměrné denní prodeje. V tomto měřítku jednoduše vydělíme hodnoty měřítek [Prodeje] a [Počet dnů v1].
Měřítko:
Nové měřítko si vložíme do vizuálu matice, a můžeme se podívat na jeho výsledky, například pro rok 2020.
V použitém cvičném souboru máme k dispozici data pouze do 15. června 2020. Rok 2020 proto můžeme považovat za aktuální rok. To že nemáme k dispozici prodeje za všechny dny v otevřeném roce je totiž přirozený jev i ve většině reálných modelů.
Při pohledu na vizuál na obrázku výše je pak zřejmé, že měřítko [Průměrné denní prodeje v1] vrací nižší než očekávané hodnoty v řádku s rokem 2020 a v řádku souhrnů Celkem. Pokud se totiž podíváme na průměrné denní prodeje v jednotlivých měsících roku 2020, tak v žádném měsíci nebyly průměrné denní prodeje nižší než 100 000. Přesto v řádku s rokem 2020 vidíme hodnotu 66 854,42.
Důvody pro tyto nižší hodnoty v řádku s rokem 2020 a v řádku souhrnů Celkem najdeme přímo v zobrazeném vizuálu, protože v tomto vizuálu máme také obě měřítka, která používáme pro výpočet průměru.
Pokud se zaměříme například na měsíc leden 2020, tak v tomto měsíci při výpočtu průměru dělíme sumu prodejů za aktuální měsíc počtem dnů v tomto měsíci. V řádku s rokem 2020 ale dělíme sumu prodejů za první polovinu roku, přesněji řečeno sumu za prodeje produktů do 15. června 2020, počtem všech dnů v roce 2020. Proto je hodnota v řádku s roky výrazně nižší než v jednotlivých měsících.
Problémem tedy není měřítko [Prodeje], ale měřítko [Počet dnů v1], které vrací počet všech dnů, které jsou dostupné v aktuálním kontextu vyhodnocení, bez ohledu na to zda se jedná o dny z budoucnosti nebo naopak o dny, pro které ještě nemáme k dispozici data.
Podobný problém totiž můžeme vidět také v roce 2017, ve kterém máme k dispozici data teprve od 1. července.
To že měřítko [Průměrné denní prodeje v1] vrací jiné hodnoty v určitých řádcích vizuálu, než bychom očekávali ještě neznamená, že je celá logika výpočtu v tomto měřítku obecně špatně. Při výpočtu průměru vždy záleží na významu jednotlivých sloupců použitých ve výpočtu a na obsahu dat v tabulkách. V jiném modelu s jinými atributy by stejný výpočet mohl vracet požadované výsledky. V našem případě ale potřebujeme v děliteli pouze ty dny, pro které máme k dispozici data za prodeje.
Při zjišťování počtu dnů pro výpočet průměru tedy budeme muset nějakým způsobem ověřovat, zda v započítáváme dny, pro které již máme k dispozici záznamy o prodejích.
Zkusit bychom mohli například následující výpočet, ve kterém si nejdříve ověříme, zda měřítko [Prodeje] vrací v aktuálním kontextu nějakou hodnotu, a až následně spočítáme počet řádku z tabulky 'Date', které jsou dostupné v aktuálním kontextu vyhodnocení.
Měřítko:
Novou variantu výpočtu si můžeme porovnat s hodnotami, které vrací měřítko [Počet dnů v1].
Měřítko [Počet dnů v2] nyní, na rozdíl od měřítka [Počet dnů v1], nevrací hodnoty v měsících, které považujeme za budoucnost. V řádku s rokem 2020 a v řádku souhrnů Celkem ale obě měřítka vrací stále stejné hodnoty, a to počet dnů v roce, respektive počet dnů ve všech letech v řádku Celkem.
Problémem je že v měřítku [Počet dnů v2] ověřujeme, zda měřítko [Prodeje] vrací hodnoty v aktuálním kontextu vyhodnocení. Ten je dán vnějšími filtry, které na měřítko působí. Například v řádku s rokem 2020 tvoří vnější kontext pouze filtr nastavený na rok 2020. Pro rok 2020 pak měřítko [Prodeje] vrací hodnotu, i když jde o prodeje pouze za první polovinu roku. Proto v řádku s rokem 2020 dojde k vyhodnocení druhého argumentu funkce IF(), jehož výsledkem bude opět počet všech dnů v roce 2020.
Co tedy budeme potřebovat je změnit granularitu, v jaké bude podmínka ve funkci IF() vyhodnocena. Toho můžeme dosáhnout například pomocí iterační funkce SUMX(). Ve funkci SUMX() si můžeme v prvním argumentu načíst měsíce dostupné v aktuálním kontextu vyhodnocení, a v rámci každého měsíce následně ověřit, zda v daném měsíci došlo k prodejům produktů. Pokud ano, připočítáme počet dnů v tomto měsíci do finálního výsledku.
Měřítko:
Pokud novou verzi výpočtu vložíme do původního vizuálu, výsledek bude vypadat následovně.
Měřítko [Počet dnů v3] již vrací v řádcích souhrnů hodnoty, které odpovídají součtu dnů pouze v těch měsících, ve kterých došlo k prodejům v daném roce, nebo ve všech letech v případě řádku Celkem.
Ačkoliv se tedy může zdát, že máme správnou hodnotu pro dělitele ve výpočtu průměrných denních prodejů, vyhráno ještě nemáme. Stále totiž vidíme nesprávné hodnoty v neúplných měsících.
Jak už jsme si řekli dříve, v použitém modelu máme k dispozici data za prodeje pouze do 15. června 2020. V měsíci červen 2020 proto nemůžeme dělit prodeje číslem 30, ale číslem 15, protože v tomto měsíci máme informace o prodejích pouze za prvních 15 dnů měsíce. Těchto patnáct dnů, které máme navíc v měsíci červen 2020, máme navíc také v řádku souhrnů Celkem a v řádku s rokem 2020.
V měřítku [Počet dnů v3] jsme pracovali s granularitou na úrovni měsíců. Abychom tedy byli přesní i v neúplných měsících, může se nabízet pracovat s granularitou na úrovni jednotlivých dnů, a zjišťovat pro každý den, jestli měřítko [Prodeje] vrací hodnotu větší než 0, a pouze tyto dny následně sčítat.
Práce s granularitou na úrovni jednotlivých dnů by ale mohla přinést jiné problémy. Uvažujme například následující report, ve kterém je v průřezu vybraný pouze jeden model.
Dne 4. 4. 2019 se neprodal žádný produkt z modelové řady "Cycling Cap". Pro tento den a vybranou modelovou řadu tedy měřítko [Prodeje] vrací prázdnou hodnotu BLANK. Pokud bychom ověřovali, zda měřítko [Prodeje] vrací hodnoty na úrovni jednotlivých dnů, takovýto den by byl z výpočtu průměrných denních prodejů chybně vyloučen, protože dané produkty se jistě nabízely, pouze si je nikdo nekoupil.
Pro výpočet průměrných denních prodejů tedy budeme chtít započítat všechny dny v aktuálním kontextu vyhodnocení, které ale zafiltrujeme pouze na ty dny, které jsou větší nebo rovno prvnímu dnu s prodeji a menší nebo rovno poslednímu dnu s prodeji.
Měřítko:
Dny pro které ještě nemáme k dispozici data a dny které považujeme za budoucnost jsou jednoduše odfiltrovány pomocí funkce FILTER(). Nedojde ale k odfiltrování těch dnů, pro které měřítko [Prodeje] nevrací hodnoty, přestože máme otevřeno a nabízíme produkty, ale zkrátka si je nikdo nekoupil. To, jak pravděpodobná může tato situace být, záleží vždy na konkrétním modelu. Jak jsme si ale ukázali na příkladu prodejů produktů v modelové řadě "Cycling Cap", v našem cvičném souboru tento stav přivodil jeden filtr navíc působící na výpočet z průřezu.
Do funkce FILTER() bychom mohli přidat také další filtry. Pokud bychom například měli v kalendářní tabulce označené dny, ve kterých byly zavřené prodejny, můžeme jednoduše tyto dny z výpočtu průměru vyloučit.
Nové měřítko již nyní bude vracet správné hodnoty pro dělitele také v neúplném měsíci červen 2020 a v řádcích souhrnů.
Měřítko [Počet dnů v4] tedy použijeme pro výpočet průměrných denních prodejů.
Měřítko:
Výpočet průměrných denních prodejů v pracovní dny bude vypadat následovně.
Měřítko:
Podobným způsobem si můžeme vytvořit také další měřítko, které bude vracet průměrné denní prodeje v nepracovní dny, v našem zjednodušeném příkladu půjde o prodeje o víkendech.
Měřítko:
Všechna tři nová měřítka si můžeme vložit do vizuálu matice a zobrazit si výsledky.
Jak můžeme vidět na obrázku výše v řádku souhrnů Celkem, tak průměrné denní prodeje jsou celkově vyšší v pracovní dny. V některých měsících, například v únoru nebo v březnu 2020, byly ale průměrné denní prodeje vyšší o víkendech.
Ve všech dosavadních příkladech jsme používali pro výpočet průměru funkci DIVIDE(), ve které jsme si sami vytvořili hodnoty pro dělence i dělitele. V jazyku DAX ale máme k dispozici speciální funkce pro výpočet průměru, a to funkce AVERAGE() a AVERAGEX().
Funkci AVERAGE() nemůžeme použít pro výpočet průměrných denních prodejů, protože tato funkce přijímá pouze jeden argument ve formě sloupce z některé z tabulek nahraných v modelu.
Pro výpočet průměrných denních prodejů se ale může nabízet funkce AVERAGEX(). Proto si ještě pro úplnost můžeme ukázat jednu variantu výpočtu průměrných denních prodejů, a to pomocí funkce AVERAGEX().
Průměrné denní prodeje s funkcí AVERAGEX
Výpočet průměrných denních prodejů pomocí funkce AVERAGEX() by mohl vypadat například následovně.
Měřítko:
Hodnoty měřítka [Průměrné denní prodeje (AVERAGEX)] si můžeme porovnat s naší finální verzí výpočtů průměrných denní prodejů, tedy s hodnotami měřítka [Průměrné denní prodeje].
Měřítko [Průměrné denní prodeje (AVERAGEX)] vrací na obrázku výše stejné hodnoty jako měřítko [Průměrné denní prodeje]. Nabízí se tedy otázka, proč všechna ta práce s měřítkem [Průměrné denní prodeje], když stejného výsledku můžeme dosáhnout jednodušeji pomocí funkce AVERAGEX().
Zde je důležité si uvědomit, že významově je výpočet s použitím funkce AVERAGEX() jiný než náš vlastní algoritmus v měřítku [Průměrné denní prodeje]. Funkce AVERAGEX() totiž z průměru automaticky vyloučí ty dny, pro které vrací měřítko [Prodeje] ve druhém argumentu této funkce prázdnou hodnotu BLANK.
Tento efekt vyřešil celý náš problém s odfiltrováním budoucích dnů, pro které ještě nemáme k dispozici prodeje a také s odfiltrováním dnů, které předcházejí prvnímu dnu s prodeji.
Funkce AVERAGEX() ale odfiltruje všechny dny, pro které měřítko [Prodeje] nevrací žádné hodnoty. Abychom si vysvětlili, proč to může být problém, vrátíme se k naší modelové řadě "Cycling Cap".
Ne všechny produkty se museli prodávat ve všech dnech, ve kterých tyto produkty nabízíme. Pokud tedy uživatel reportu vytvoří takovou kombinaci filtrů, která bude znamenat, že v určitých dnech bude vracet měřítko [Prodeje] prázdnou hodnotu BLANK, jako je tomu pro den 4. 4. 2019 a modelovou řadu "Cycling Cap", funkce AVERAGEX() tento den automaticky z průměru vyloučí. V těchto situacích budou vracet měřítka [Průměrné denní prodeje (AVERAGEX)] a [Průměrné denní prodeje] jiné výsledky.
V řádcích, ve kterých vrací obě varianty výpočtu průměrných denní prodejů jiné výsledky jsou dny, ve kterých se produkty z modelové řady "Cycling Cap" neprodávaly. Tyto dny ale nemůžeme z výpočtu průměru automaticky vylučovat, protože dané produkty se jistě nabízeli, viz situace ze 4. 4. 2019, kde před i po tomto dnu došlo k prodejům produktů z vybrané modelové řady.
Funkce AVERAGEX() by tedy byla v našem příkladu použitelná pouze v situaci, kdy bychom měli jistotu, že máme pro každou kombinaci filtrů k dispozici prodeje pro každý den, který chceme použít pro výpočet průměru.
To samozřejmě neznamená, že bychom měli automaticky považovat funkci AVERAGEX() za funkci nevhodnou pro výpočet průměrů. Naopak, ve většině situací bude pravděpodobně funkce AVERAGEX() preferovanou a jednodušší variantou pro výpočet průměru. Při jejím použití ale musíme vědět, jak tato funkce funguje a zda je vhodná pro aktuálně zamýšlený výpočet.
Shrnutí
Při výpočtu průměru není možné obecně říct, že jeden typ výpočtu bude fungovat pro všechny situace. Vždy záleží na významu použitých atributů nebo měřítek. Proto bychom měli být při výpočtech průměrů velmi obezřetní. Jistě budou existovat měřítka, kde bude stačit pro výpočet průměru použít funkci AVAREGE() nebo AVERAGEX(). Tyto funkce ale automaticky vylučují z výpočtu průměru prázdné hodnoty BLANK. V situacích, kdy chceme zahrnout do výpočtu průměru také řádky, které obsahují prázdné hodnoty, můžeme použít vlastní algoritmus, ve kterém máme úplnou kontrolu nad hodnotami v dělenci a v děliteli, podobně jako jsme postupovali v příkladech zobrazených v tomto příspěvku.
Komentáře
Okomentovat