V Power BI můžeme relativně jednoduše vytvořit užitečné reporty bez nutnosti napsání jediného řádku DAX kódu. Pokud ovšem potřebujeme rozšířit datový model výpočty, které nemůžeme získat jednoduše pomocí implicitních měřítek (automaticky vytvořená měřítka nad sloupci s číselnými hodnotami), obvykle je součástí výpočtů také funkce CALCULATE (). Funkce CALCULATE () je velice užitečná funkce, která umožňuje přidávat do výpočtů filtry, přepisovat vnější filtry, odstranit vnější filtry nebo upravovat existující filtry v reportu. Tento příspěvek je zamýšlen jako úvod do práce s funkcí CALCULATE () v Power BI. Detailnější informace o této funkci můžete najít na stránce DAX - Průvodce, včetně mnoha dalších příkladů a ukázek použití této funkce a také příkladů použití funkce CALCULATE () v kombinaci s jinými funkcemi. Příklady v tomto příspěvku jsou vytvořeny ve cvičném 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.
Začínáme s funkcí CALCULATE()
Prvním argumentem funkce CALCULATE () musí být funkce vracející v aktuálním kontextu jednu hodnotu (skalární hodnotu). Jako první argument proto obvykle používáme některou ze sumarizačních funkcí, například funkci SUM(), MIN(), MAX() nebo AVERAGE(). Dalšími argumenty funkce CALCULATE () mohou být různé druhy filtrů nebo takzvané modifikátory filtrů. Druhý a další argumenty funkce CALCULATE () jsou nepovinné. Na druhou stranu můžeme vložit do funkce CALCULATE () libovolné množství filtrů.
Syntaxe funkce CALCULATE:
Jako filtry můžeme použít logické výrazy, tabulky nebo tzv. modifikátory filtrů. Více informací o jednotlivých druzích filtrů a způsobu jejich použití můžete najít v samostatném příspěvku: Jak používat filtry ve funkci CALCULATE, druhy filtrů a příklady jejich použití.
Jako první ukázku použití funkce CALCULATE () si můžeme ve cvičném souboru Adventure Works DW 2020.pbix vytvořit následující dvě měřítka.
Měřítka:
Měřítko [Prodeje] obsahuje sumarizační funkci SUM() s argumentem ve formě sloupce z tabulky prodejů 'Sales', který obsahuje konkrétní částky za prodané produkty. Výsledkem tohoto měřítka bude suma prodejů v aktuálním kontextu vyhodnocení.
Měřítko [Prodeje v roce 2020] obsahuje v prvním argumentu funkce CALCULATE () stejný výpočet jako první měřítko. K tomuto výpočtu navíc přidáváme dodatečný filtr, který bude obsahovat rok 2020. Měřítko [Prodeje v roce 2020] proto bude vracet nižší hodnotu, než měřítko [Prodeje], a to díky dodatečnému filtru nastavenému na rok 2020. Obě měřítka můžeme vložit do vizuálu tabulky a porovnat si výsledky.
Výsledek měřítka [Prodeje v roce 2020] je v každém řádku tabulky nižší, než výsledek měřítka [Prodeje]. Pokud se podíváme například na první řádek tabulky, tak měřítko [Prodeje] vrací hodnotu prodejů produktů v kategorii "Bikes" za všechny roky. Měřítko [Prodeje v roce 2020] vrací ve stejném řádku hodnotu prodejů také v kategorii "Bikes", ale pouze v roce 2020, který jsme přidali do filtru ve funkci CALCULATE ().
Pokud do reportu přidáme průřez s roky, a v tomto průřezu vybereme pouze rok 2019, výsledek obou měřítek bude opět jiný.
Měřítko [Prodeje] vrací v každém řádku tabulky sumu za prodané produkty v dané kategorii v roce 2019, díky filtru z průřezu. Měřítko [Prodeje v roce 2020] vrací v každém řádku tabulky sumu prodejů v dané kategorii produktů, ale stále v roce 2020, jak jsme definovali uvnitř funkce CALCULATE (). Logické filtry definované uvnitř měřítka přepisují vnější filtry nastavené na stejné sloupce. To je možné lépe vidět na následujícím obrázku, kde jsou v řádcích tabulky hodnoty ze stejného sloupce, který je použitý pro nastavení logického filtru uvnitř měřítka [Prodeje v roce 2020].
Pokud tedy vytvoříme logický filtry ve funkci CALCULATE (), tento filtr je vyhodnocen v logickém AND vztahu s ostatními filtry z jiných sloupců, ale případné vnější filtry nastavené na stejný sloupec jako sloupec použitý pro logický filtr jsou přepsány. Toto chování ovšem můžeme změnit. Pokud například vložíme celý logický výraz do funkce KEEPFILTERS(), nedojde k přepsání filtrů nastavených na stejný sloupec, ale k jejich průniku.
Měřítko:
Rozdíl mezi logickým filtrem vloženým do funkce KEEPFILTERS() a původním měřítkem s logickým filtrem bez funkce KEEPFILTERS() můžeme vidět na následujícím obrázku.
Zatímco standardní logický filtr přepíše případné vnější filtry aplikované na stejný sloupec, v tomto případě filtry z řádků tabulky, logický filtr vložený do funkce KEEPFILTERS() je vyhodnocen v logickém AND vztahu s vnějšími filtry nastavenými na stejný sloupec v řádcích tabulky.
Ve funkci CALCULATE () můžeme vnější filtry také odstraňovat. Podívejme se například na následující vizuál, kde jsou v řádcích roky a kategorie produktů, a v hodnotách měřítko [Prodeje], které vrací sumu prodejů v aktuálním kontextu vyhodnocení, bez jakýchkoliv explicitních filtru uvnitř funkce CALCULATE ().
Uvažujme nyní výpočet, který by vracel procentuální podíl prodejů v dané kategorii k celkovým prodejům ve všech kategoriích v daném roce. Jako dělence můžeme použít stejný výpočet, který vrací měřítko [Prodeje]. Jako dělitele budeme chtít použít sumu prodejů za všechny kategorie v daném roce. Tuto hodnotu již můžeme vidět na úrovni let.
Hodnoty prodejů v jednotlivých letech přes všechny kategorie potřebujeme mít k dispozici v řádcích z kategoriemi. Toho můžeme dosáhnout odstraněním filtru ze sloupce 'Product'[Category] následujícím způsobem.
Měřítko:
Funkce REMOVEFILTERS() odstraní všechny filtry které jsou aplikované na sloupec uvedený v argumentu této funkce. Funkci REMOVEFILTERS() můžeme v jiných situacích použít také s argumentem ve formě tabulky, nebo bez argumentu. Měřítko [Prodeje REMOVEFILERS] opravdu vrací v každém řádku hodnotu, kterou potřebujeme pro výpočet procentuálního podílu prodejů v kategorii vůči prodejům za všechny kategorie v daném roce.
Nyní, pokud vydělíme výsledek měřítka [Prodeje] hodnotami měřítka [Prodeje REMOVEFILTERS], dostaneme požadovaný výsledek. Samotný výpočet může vypadat následovně.
Měřítko:
Prvním argumentem funkce DIVIDE() je dělenec, který v tomto příkladu představuje výpočet sumy prodejů v aktuálním kontextu vyhodnocení. Druhým argumentem funkce DIVIDE() je dělitel, pro který je použitý výpočet, ve kterém odstraňujeme filtry ze sloupce 'Product'[Category], abychom v každém řádku vizuálu dostali hodnotu prodejů v aktuálním roce, bez ohledu na kategorii produktů. Výsledek si opět můžeme zobrazit v připraveném vizuálu.
Pro výpočet procentuálních podílů můžeme k odstranění filtrů pří výpočtu dělitele použít také jiné funkce, například funkci ALLSELECTED() nebo funkci ALLEXCEPT(). Rozdíl mezi použitím funkce REMOVEFILTERS() a funkce ALLSELECTED() při výpočtu procentuálních podílů je popsán v samostatném příspěvku.
Příklady použití funkce CALCULATE
Měřítko:
Ve výpočtu nejdříve pomocí funkce IF() ověřujeme, zda došlo v aktuálním období k nějakým prodejům, aby se samotný výpočet nezobrazoval ve dnech, pro které ještě nejsou prodeje k dispozici. Následuje samotný výpočet kumulativních prodejů, ve kterém chceme vrátit sumu prodejů za všechny dny, které jsou menší nebo rovny poslednímu dnu v aktuálním kontextu vyhodnocení.
Na obrázku výše vrací měřítko [Prodeje RT] v každém řádku sumu prodejů za aktuální měsíc a všechny předchozí měsíce. Například v řádku "srpen 2017" představuje vrací měřítko [Prodeje RT] sumu prodejů za měsíc "červen 2017" a měsíc "srpen 2017", atd.
Dalším příkladem použití funkce CALCULATE() může být porovnání aktuálních prodejů s prodeji v předchozím roce. Jednoduchý výpočet prodejů v předchozím roce může vypadat následovně.
Měřítko:
Funkce DATEADD() je funkce z kategorie Time intelligence, která vrací všechny dny z aktuálního kontextu vyhodnocení, ale posunuté o zadaný počet intervalů zpět nebo vpřed. Výsledkem funkce DATEADD() je tedy tabulka kalendářních dnů, která tvoří nový filtr pro výpočet v prvním argumentu funkce CALCULATE ().
Jak je možné vidět na obrázku výše, měřítko [Prodeje PY] vrací hodnotu prodejů za stejné období v předcházejícím roce. Detailnější popis výpočtu sumy prodejů za předcházející období je popsán v samostatném článku, včetně výpočtů s ošetřením otevřeného časového období.
Měřítko:
Pokud nově vytvořené měřítko vložíme do vizuálu spolu s kategoriemi produktů v řádcích, můžeme vidět počet produktů v každé kategorii.
To, že máme v dimenzní tabulce produkty ještě nemusí znamenat, že je o tyto produkty zájem a že se opravdu prodávají. Pokud bychom chtěli zjistit počet produktů, které již mají záznam ve faktové tabulce 'Sales' se záznamy o prodejích, můžeme tuto tabulku použít jako filtr ve funkci CALCULATE().
Měřítko:
Nové měřítko můžeme vložit do původního vizuálu a podívat, se kolik produktů se opravdu prodává.
Jak je možné na obrázku výše, v použitém modelu je 397 produktů, ale pouze 350 z nich se skutečně prodává. Následující měřítko bude vracet počet produktů, u kterých je rozdíl mezi náklady na výrobu a prodejní cenou vyšší než 500.
Měřítko:
V měřítku [Počet produktů zisk > 500] je již použitý poměrně komplexní filtr, který obsahuje dvě vnořené funkce vracející tabulku. Výsledný filtr obsahuje sloupce 'Product'[List Price] a 'Product'[Standart Cost], pro které platí podmínka uvedená ve vnější funkci FILTER(). Výsledkem měřítka je pak počet produktů v aktuálním kontextu vyhodnocení, které mají rozdíl mezi náklady a katalogovou cenou větší než 500.
Jak je možné vidět na obrázku výše, produkty vyšším hrubým ziskem než 500 jsou pouze v kategoriích "Bikes" a "Components".
V dalším příkladu si vytvoříme měřítko, které bude vracet opak toho, co vybere uživatel v průřezu. Do reportu si můžeme vložit průřez s barvami produktů. Uživatel si tak může zobrazit prodeje v jednotlivých kategoriích, pouze pro barvy vybrané v průřezu.
Hodnota měřítka [Prodeje] ve zvýrazněném řádku představuje sumu prodejů za produkty v kategorii "Bikes", ale pouze produktů v černé a modré barvě, díky filtru v průřezu. Filtr v průřezu přirozeně působí na výpočet měřítka [Prodeje] i v ostatních řádcích tabulky. Následující měřítko bude vracet sumu prodejů v aktuálním kontextu vyhodnocení, ale pouze pro barvy, které uživatel nevybere v průřezu. Jinak řečeno, výsledkem měřítka bude suma za prodané produkty v aktuálním kontextu vyhodnocení za produkty ve všech ostatních barvách než jsou barvy, které uživatel vybere v průřezu.
Měřítko:
Filtr ve funkci CALCULATE() bude obsahovat jeden sloupec s barvami produktů, které nejsou přímo filtrované v reportu. Výsledek můžeme vidět na následujícím obrázku.
Do tabulky si pro porovnání můžeme přidat měřítko, které bude úplně ignorovat filtry nastavené na sloupec s barvami produktů.
Měřítko:
Nyní se můžeme lépe pozorovat význam měřítka [Prodeje ostatní barvy].
Měřítko [Prodeje] vrací sumu prodejů za produkty v dané kategorii produktů a ve vybraných barvách v průřezu. Měřítko [Prodeje ostatní barvy] pak vrací sumu prodejů za produkty v dané kategorii, ale ve všech ostatních barvách, než které uživatel vybral v průřezu. Pokud v každém řádku tabulky sečteme hodnoty měřítek [Prodeje] a [Prodeje ostatní barvy], dostaneme stejnou hodnotu, která je výsledkem měřítka [Prodeje všechny barvy].
Shrnutí
Funkce CALCULATE () je jednou z nejdůležitějších funkcí v jazyku DAX. Pomocí funkce CALCULATE () můžeme tvořit filtry, přepisovat vnější filtry které jsou aktivní v reportu, odstraňovat vnější filtry, upravovat vztahy mezi jednotlivými filtry nebo manipulovat s relacemi mezi tabulkami v modelu. Všechny tyto přístupy můžeme kombinovat. Další užitečnou vlastností funkce CALCULATE () je změna kontextu řádku na kontext filtru. Více informací o funkci CALCULATE (), včetně informací o různých způsobech psaní filtrů a používání modifikátorů filtrů můžete najít na stránce DAX - Průvodce. Praktické příklady, včetně příkladů časových kalkulací, ve kterých se obvykle používají jako filtry ve funkci CALCULATE () tzv. funkce časového měřítka, můžete najít na stránce DAX - Příklady.
Komentáře
Okomentovat