DAX funkce SUM a SUMX

Funkce SUM() a funkce SUMX() jsou dvě jednoduché funkce, se kterými se můžeme velmi často setkat při psaní DAX výpočtů. Pokud si nejste jistí, jaký je rozdíl mezi těmito funkcemi a kdy ve výpočtu použít funkci SUM() a kdy funkci SUMX(), tento příspěvek by Vám mohl pomoci.

Rozdíl mezi funkcí SUM a funkcí SUMX v jazyku DAX

Funkce SUM() je agregační funkce, která vrací součet hodnot ze sloupce uvedeného v argumentu této funkce v aktuálním kontextu vyhodnocení. Funkce SUMX() je iterační funkce, která musí obsahovat tabulku nebo funkci vracející tabulku v prvním argumentu a výpočet ve druhém argumentu. Tento výpočet se vyhodnotí v každém řádku tabulky uvedené v prvním argumentu. Funkce SUMX() následně výsledky výpočtů z každého řádku sečte a vrátí sumu těchto dílčích hodnot.

Příklady uvedené 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.

Funkce SUM

Funkce SUM() je jednoduchá funkce s jedním argumentem ve formě odkazu na existující sloupec v modelu.

Syntaxe funkce SUM():

SUM(<sloupec>)

Funkce SUM(), nebo obecně sumarizační funkce, ignorují kontext řádku, ale výsledek funkce ovlivňuje v době vyhodnocení aktivní kontext filtru, který je přirozenou součástí Power BI reportů. Jako příklad si můžeme ve cvičném Power BI souboru vytvořit následující měřítko.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Nové měřítko můžeme vložit do vizuálu tabulky. Pokud v době vyhodnocení měřítka není v modelu aktivní žádný filtr, výsledkem měřítka je suma všech hodnot ze sloupce uvedeného v argumentu funkce SUM(). 

Pokud do vizuálu Tabulky přidáme do řádků hodnoty s kategoriemi produktů, bude měřítko [Prodeje] vyhodnoceno v kontextu filtru každé kategorie.

Podívejme se například na první řádek tabulky. V tomto řádku je při vyhodnocení měřítka tabulka 'Sales'  filtrovaná pouze na prodané produkty, které patří do kategorie Bikes. V prvním řádku tabulky  proto výsledek měřítka odpovídá sumě za prodané produkty v kategorii Bikes. Ve druhém řádku jde o sumu za prodané produkty v kategorii Components, a tak dále. V řádku souhrnů nepůsobí při vyhodnocení měřítka na model žádný filtr, a výsledkem je proto suma za všechny prodané produkty, bez ohledu na kategorii produktů. Pokud do reportu přidáme další filtr, například pomocí průřezu, tento filtr opět ovlivní výsledek měřítka [Prodeje]. 

Na výpočet měřítka nyní působí při jeho vyhodnocení další filtr, který filtruje tabulku 'Sales' pouze na prodeje uskutečněné v roce 2019. V prvním řádku tabulky tak měřítko [Prodeje] vrací sumu za prodané produkty v kategorii Bikes za rok 2019, a tak dále. 

Funkce SUM() je jednoduchá funkce, která sčítá hodnoty ze sloupce uvedeného v argumentu této funkce, s ohledem na aktuální kontext vyhodnocení. Naproti tomu funkce SUMX()  je trochu složitější na použití, nicméně umožňuje vytvářet daleko komplexnější výpočty.

Funkce SUMX

Funkce SUMX() má širší možnosti využití. Prvním argumentem funkce SUMX() může být tabulka nebo funkce vracející tabulku. Druhým argumentem funkce SUMX() je výraz, který je vyhodnocen řádek po řádku v tabulce uvedené v prvním argumentu. Výsledkem funkce je pak součet všech dílčích výsledků výrazu vyhodnoceného v každém řádku tabulky.

Syntaxe funkce SUMX():

SUMX(<tabulka>, <výraz>)

Před použitím funkce SUMX() bychom vědět, že:

• Tabulka v prvním argumentu funkce je vyhodnocena v originálním kontextu vyhodnocení, tedy v kontextu, ve kterém je vyhodnocena samotná funkce SUMX().
• Výraz ve druhém argumentu funkce je vyhodnocen v originálním kontextu vyhodnocení a současně v nově vytvořeném kontextu řádku, který je tvořen tabulkou v prvním argumentu funkce.

Jako první argument můžeme použít tabulku nebo funkci vracející tabulku. Ve druhém argumentu funkce se můžeme odkazovat na všechny sloupce, které jsou součástí tabulky a její rozšířené verze v prvním argumentu funkce. Dále můžeme ve druhém argumentu používat výrazy obsahující výpočty nebo měřítka, která dávají smysl při vyhodnocení v nově vytvořeném kontextu řádku a v aktuálním kontextu vyhodnocení. 

Začněme ale jednoduchým příkladem, který bude vracet stejnou hodnotu, jako dříve vytvořené měřítko [Prodeje], pouze místo funkce SUM() použijeme funkci SUMX().

Měřítko:

Prodeje SUMX =

SUMX

(

Sales,

Sales[Sales Amount]

)

Jako první argument je ve výše uvedeném měřítku použita tabulka 'Sales', která bude vyhodnocena v originálním kontextu, který bude obsahovat všechny případné filtry v reportu, ve kterém je měřítko vyhodnoceno. Druhým argumentem je odkaz na sloupec právě z tabulky 'Sales'. Tento sloupec obsahuje jednotlivé částky za prodané produkty. Výsledkem měřítka [Prodeje SUMX] tak bude suma všech částek za prodané produkty, nicméně pouze z těch řádků tabulky 'Sales', které budou dostupné v aktuálním kontextu vyhodnocení. Výsledek můžeme vidět na následujícím obrázku, i s porovnáním s dříve vytvořeným měřítkem [Prodeje], ve kterém jsme použili funkci SUM().

Jak je možné vidět na obrázku výše, výsledek obou měřítek je stejný. Všechny výpočty které zvládneme vytvořit pomocí funkce SUM(), můžeme vytvořit také pomocí funkce SUMX(). Na druhou stranu funkce SUMX() umožňuje vytvořit daleko komplexnější výpočty.  Uvažujme například opět sumu za prodané produkty. Tento sloupec je u každého záznamu v tabulce 'Sales' vypočítán jako násobek počtu prodaných produktů a ceny produktu. Cena produktu se ještě může lišit v závislosti na poskytnuté slevě. Pokud bychom neměli tento výpočet již k dispozici v tabulce a chtěli bychom ho získat pomocí měřítka, mohli bychom postupovat následujícím způsobem.

Měřítko:

Prodeje SUMX 2 =

SUMX

(

Sales,

(Sales[Unit Price] * (1-Sales[Unit Price Discount Pct])) * Sales[Order Quantity]

)

Výsledek můžeme vidět na následujícím obrázku.

Výsledek obou měřítek je opět stejný, pouze jsme logiku výpočtu, která je skrytá pod hodnotami ve sloupci 'Sales'[Sales Amount], aplikovali na výpočet v měřítku pomocí funkce SUMX(). Ve druhém argumentu funkce SUMX() můžeme přistupovat také k hodnotám ze sloupců z tabulek, které jsou s tabulkou použitou v prvním argumentu funkce ve vztahu MANY-TO-ONE na straně ONE. 

Pomocí funkce RELATED() tak například můžeme do výpočtu zahrnout sloupec z tabulky 'Product', který obsahuje náklady na výrobu produktu, a vypočítat tak hrubý zisk.

Měřítko:

Hrubý zisk =

SUMX

(

Sales,

Sales[Sales Amount] - (Sales[Order Quantity] * RELATED('Product'[Standard Cost]))

)

Nové měřítko si opět můžeme zobrazit ve vizuálu, spolu s měřítkem [Prodeje].

V další části si ještě můžeme ukázat příklad špatně definovaného měřítka, které bychom mohli s dobrým úmyslem vytvořit v případě, kdy nejsme dobře seznámeni s principem kontextu řádku a s principem kontextu filtru.

Nevhodné použití funkce SUM

Některé typy výpočtů dávají smysl pouze pokud jsou vyhodnoceny v určité granularitě. Zaměřme se v následujícím příkladu pouze na tabulku 'Sales', která obsahuje záznamy o prodaných produktech. V každém řádku této tabulky můžeme mimo jiné najít unikátní klíč produktu, který byl prodán, jeho cenu a množství. Pokud bychom pro zjednodušení vypustili z výpočtu sumy za prodané produkty slevu, která je na některé prodeje uplatňována, mohli bychom při výpočtu uvažovat následovně. Celková částka, kterou zákazník zaplatí za daný produkt, bude cena produktu vynásobená počtem zakoupených kusů konkrétního produktu. 

Vraťme se nyní k funkci SUM(), o které již víme, že jako argument v této funkci můžeme použít pouze jeden sloupec, ze kterého chceme sečíst všechny hodnoty v aktuálním kontextu vyhodnocení. Z výše slovně popsaného příkladu výpočtu sumy za prodané produkty bychom si tak mohli snadno myslet, že můžeme v měřítku vynásobit sumu jednotkových cen produktů sumou prodaného množství. Následující výpočet však bude vracet v naprosté většině případů nesmyslné výsledky.

Měřítko:

Prodeje špatně =

SUM(Sales[Unit Price]) * SUM(Sales[Order Quantity])

Než se podíváme na výsledek měřítka [Prodeje špatně] v reportu, vytvořme si ještě pro porovnání měřítko se správným výpočtem.

Měřítko:

Prodeje správně =

SUMX

(

Sales,

Sales[Unit Price] * Sales[Order Quantity]

)

Výsledek obou měřítek můžeme vidět na následujícím obrázku.

Měřítko [Prodeje špatně] vrací mnohonásobně vyšší částky, než které skutečně firma utržila za prodané produkty. Problém takto definovaného měřítka je v granularitě, ve které je výpočet vyhodnocen. Uvažujme například první řádek vizuálu na obrázku výše, tedy řádek s kategorií Bikes. V měřítku [Prodeje špatně] násobíme sumu všech jednotkových cen všech produktů z dané kategorie sumou všech množství opět všech produktů z dané kategorie, které byly prodány. Jinak řečeno, pokud násobíme sumu jinou sumou, tak je výsledek jiný než součty jednotlivých násobků na nejnižší úrovni detailu. Velmi zjednodušeně, 2 * 2 + 2 * 2 není totéž, jako 4 * 4. Měřítko [Prodeje špatně] by vracelo správné výsledky pouze v granularitě dané tabulkou 'Sales'. Tu si můžeme v reportu nasimulovat tak, že vložíme do vizuálu primární klíč z tabulky 'Sales', namísto kategorií produktů.

Na obrázku výše je v řádcích vizuálu použitý primární klíč z tabulky 'Sales'.  Pouze v takto vytvořeném pohledu vrací měřítko [Prodeje špatně] stejné hodnoty, jako měřítko [Prodeje správně], kromě řádku souhrnů. Naproti tomu měřítko [Prodeje správně] vrací správné výsledky i v situacích, kdy potřebujeme výsledek vyhodnotit na nižší úrovni. 

V měřítku [Prodeje správně] je granularita dána tabulkou, kterou jsme použili v prvním argumentu funkce SUMX(). Případné vnější filtry sice působí na tuto tabulku uvedenou v prvním argumentu, nicméně samotný výpočet je vyhodnocen vždy řádek po řádku, i když v tabulce, která je zafiltrovaná například pouze na konkrétní rok, kategorii, nebo kterýkoliv jiný atribut z dimenzních tabulek.

Funkce SUMX() má široké možnosti využití. Samostatnou kapitolou by mohly být příklady s použitím funkcí vracející tabulky v prvním argumentu funkce SUMX(). Další důležitou výhodou funkce SUMX() je možnost sčítat hodnoty ze sloupců a tabulek, které nejsou součástí modelu, a jsou k dispozici pouze v době vyhodnocení výpočtu. Kombinaci použití funkce vracející tabulku v prvním argumentu funkce SUMX() a vytvoření součtu hodnot ze sloupce, který není součástí modelu, může najít například v příspěvku s výpočtem Pareto analýzy pomocí jazyka DAX.

Shrnutí

Funkce SUM() a SUMX() jsou jedny ze základních a často používaných DAX funkcí. Pokud potřebujeme pouze sečíst všechny hodnoty z jednoho sloupce, které jsou dostupné v aktuálním kontextu vyhodnocení, první volbou bude zřejmě funkce SUM(). Omezením funkce SUM() je, že pro agregaci hodnot můžeme použít pouze jeden sloupec. Naproti tomu ve funkci SUMX() můžeme sčítat hodnoty, které jsou výsledkem námi definovaného výpočtu, ve kterém se lze odkazovat na hodnoty z více sloupců a tvořit tak komplexní výpočty. Funkce SUMX() vytvoří pro tabulku uvedenou v prvním argumentu této funkce nový kontext řádku. Výpočet uvedený v druhém argumentu funkce SUMX() je pak vyhodnocen řádek po řádku v granularitě dané právě iterovanou tabulkou. Pro výpočty, které dávají smysl pouze při vyhodnocení v určité granularitě, bychom tedy měli použít právě funkci SUMX().

Oficiální Microsoft dokumentace funkcí SUM a SUMX:
https://docs.microsoft.com/cs-cz/dax/sum-function-dax
https://docs.microsoft.com/cs-cz/dax/sumx-function-dax

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

č. 43