Souhrn v Power BI vizuálu neodpovídá součtu hodnot v řádcích

V tomto příspěvku se dozvíte, proč hodnota v řádcích souhrnů nebo v řádku "Celkem" v Power BI vizuálech Matice nebo Tabulka nemusí vždy odpovídat součtu hodnot zobrazených v jednotlivých řádcích. V případě neaditivních výpočtů jde pouze o správnou interpretaci výsledků. V situaci, kdy chceme z neaditivních výpočtů udělat aditivní, použijeme obvykle některou z iteračních funkcí.

K tomuto tématu je k dispozici také video:

Všechny příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je k dispozici ke stažení níže pod tímto příspěvkem.

V příkladech budeme pracovat také s měřítkem [Prodeje], které má následující definici.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Pokud měřítko [Prodeje] vložíme do vizuálu Tabulka, spolu s kategoriemi produktů v řádcích. Výchozí vizuál, se kterým budeme pracovat v prvním příkladu, může vypadat následovně.

Měřítko [Prodeje] vrací v každém řádku pro každou kategorii sumu za prodeje produktů v dané kategorii. Hodnota v řádku souhrnů pak odpovídá prodejům za všechny kategorie zobrazené v řádcích vizuálu. Měřítko [Prodeje] je ze své podstaty plně aditivní. To znamená že součet dílčích prodejů přes každý atribut bude vždy odpovídat celku. Jinak řečeno, pokud sečteme prodeje za jednotlivé roky, tato hodnota bude odpovídat sumě prodejů za všechny roky. Pokud sečteme částky za nákupy jednotlivých zákazníků, výsledkem bude opět suma za nákupy všech zákazníků. Tak bychom mohli uvažovat o každém atributu v modelu, protože měřítko [Prodeje] je plně aditivní - součet jednotlivých dílčích hodnot odpovídá celku.

Tento předpoklad ale neplatí pro každý výpočet. Některé výpočty mohou být také neaditivní - nelze je sčítat přes žádné atributy, nebo semiaditivní - lze sčítat pouze přes některé atributy. V následující části si ukážeme několik příkladů takovýchto měřítek a způsob, jak z neaditivního výpočtu udělat aditivní.

Pozn.: Atribut = sloupec.

Jak v Power BI udělat z neaditivního výpočtu aditivní

Začít můžeme tím že si vytvoříme typicky neaditivní měřítko. Typickým neaditivním výpočtem je počet jedinečných hodnot ve sloupci. Počet jedinečných hodnot můžeme v jazyku DAX získat například pomocí funkce DISTINCTCOUNT(). Následující měřítko bude vracet počet barev v aktuálním kontextu vyhodnocení.

 Měřítko:

Počet barev = DISTINCTCOUNT('Product'[Color])

Nové měřítko si můžeme vložit do dříve vytvořeného vizuálu s kategoriemi produktů a zobrazit si výsledek.

Měřítko [Počet barev] vrací v každém řádku pro každou kategorii počet barev, ve kterých se prodávají produkty v dané kategorii. V řádku "Celkem" je pak číslo 10, které, jak je na první pohled zřejmé, neodpovídá součtu jednotlivých hodnot v řádcích. Pokud bychom z měřítka [Počet barev] chtěli udělat měřítko aditivní ve vztahu ke kategoriím produktů, jinak řečeno, pokud bychom chtěli v řádku souhrnů součet jednotlivých hodnot zobrazených v řádcích, můžeme použít následující výpočet.

Měřítko:

Počet barev (2) =

SUMX

(

    VALUES('Product'[Category]),

    [Počet barev]

)

Měřítko [Počet barev (2)] obsahuje iterační funkci SUMX(), ve které je v prvním argumentu funkce VALUES() s kategoriemi produktů. Tato tabulka bude obsahovat všechny kategorie produktů, které jsou dostupné v aktuálním kontextu vyhodnocení. Jedná se tedy o stejné hodnoty ze sloupce 'Product'[Category], které jsou zobrazené v řádcích vizuálu. V každém řádku vizuálu bude dostupná v této tabulce s kategoriemi produktů vždy pouze jedna aktuální kategorie, díky filtru který je vytvořený aktuálním řádkem vizuálu. Výsledek měřítka [Počet barev (2)] proto bude v každém řádku s kategoriemi produktů stejný jako v případě původního měřítka [Počet barev].  V řádku souhrnů ale budou ve funkci VALUES() dostupné všechny kategorie. Pro každou kategorii tak bude vyhodnoceno měřítko [Počet barev] ve druhém argumentu funkce SUMX(), a tyto hodnoty pro každou jednu kategorii budou následně sečteny. Výsledek v řádku souhrnů proto bude odpovídat součtu hodnot z jednotlivých řádků.

Ačkoliv se může zdát že měřítko [Počet barev (2)] vrací nyní správný výsledek v řádku souhrnů, opak je pravdou. V použitém modelu jsou v tabulce 'Product' produkty pouze v deseti různých barvách.

Měřítko [Počet barev (2)] v řádku souhrnů započítává některé barvy vícekrát. Jde o barvy, které se vyskytují ve více kategoriích. Druhý problém měřítka [Počet barev (2)] je že jeho použití dává smysl pouze v kontextu jednotlivých kategorií. Pokud bychom měřítko vložili například do vizuálu s barvami produktů v řádcích, nebo s jakýmkoliv jiným atributem kromě kategorií, výsledek měřítka bude obtížně interpretovatelný.

Správný výsledek v řádku souhrnů vrací původní měřítko [Počet barev], které je ze své podstaty neaditivní a v tomto případě by byla chyba snažit se z tohoto typu výpočtu dělat výpočet aditivní vůči kategoriím, a tím pádem semiaditivní ve vztahu ke všem atributům v modelu.

V některých situacích je však tento postup, tedy změna neaditivního výpočtu na aditivní, žádoucí. Takovýto příklad si vytvoříme v následujícím části příspěvku.

Příklad neaditivního výpočtu který by měl být aditivní

Začneme opět přípravou vizuálu. Tentokrát použijeme vizuál Matice s roky a měsíci v řádcích a s měřítkem [Prodeje] v hodnotách.

Cílem příkladu bude vytvořit měřítko, které bude vracet počet měsíců, jejichž prodeje byly vyšší, než řekněme 2 500 000. Měřítko by tedy mělo započítávat pouze měsíce s prodeji nad touto pevně stanovenou hranicí. První pokus by mohl vypadat následovně.

Měřítko:

Měsíce nad 2 500 000 =

IF

(

    [Prodeje] > 2500000,

    1,

    BLANK()

)

Měřítko [Měsíce nad 2 500 000] v prvním argumentu funkce IF() ověří, zda jsou prodeje v aktuálním kontextu vyšší než hodnota 2 500 000. Pokud budou prodeje v aktuálním měsíci vyšší než 2 500 000, započítá se číslo 1, v opačném případě bude měřítko vracet prázdnou hodnotu BLANK.

Na úrovni jednotlivých měsíců vrací měřítko [Měsíce nad 2 500 000] hodnotu 1 pro všechny měsíce, jejichž prodeje jsou vyšší než požadovaná hodnota 2 500 000. Na úrovni let a v řádku "Celkem" ale nové měřítko také vrací číslo jedna, a ne požadovaný počet měsíců s prodeji nad stanovenou částku. Problém je opět to že měřítko je neaditivní a vrací jednoduše hodnotu 1 vždy, když jsou prodeje v aktuálním kontextu vyšší než částka 2 500 000. Požadovaného výsledku opět dosáhneme pomocí iterační funkce SUMX(), ve které použijeme v prvním argumentu tabulku s měsíci, a ve druhém argumentu funkci IF() obsahující stejnou logiku jako v původním výpočtu.

Měřítko:

Měsíce nad 2 500 000 (aditivní) =

SUMX

(

    VALUES('Date'[Měsíc rok]),

    IF

    (

        [Prodeje] > 2500000,

        1,

        BLANK()

    )

)

Nové měřítko si můžeme vložit do původního vizuálu a podívat se na výsledek.

Nová verze měřítka s názvem [Počet měsíců nad 2 500 000 (aditivní)] již nyní vrací očekávané výsledky. Na úrovni let vrací měřítko počet měsíců, které splňují stanovenou podmínku v tomto roce. V řádku "Celkem" pak měřítko vrací počet měsíců které splňují stanovenou podmínku za všechny roky. Měřítko [Počet měsíců nad 2 500 000 (aditivní)] je ale aditivní pouze vůči atributům z kalendářní tabulky, které jsou v pomyslné hierarchii nad úrovní měsíců. Toto měřítko tedy dává smysl použít na úrovni měsíců, čtvrtletí, pololetí a let. Vůči ostatním atributům v modelu je toto měřítko opět neaditivní. Technicky se tedy jedná o semiaditivní výpočet, protože měřítko je aditivní pouze vůči některým atributům v modelu.

Pro úplnost si ještě můžeme ukázat optimálnější verzi měřítka [Měsíce nad 2 500 000 (aditivní)]. V této "lepší" verzi stejného výpočtu nebude použita funkce IF() uvnitř iterační funkce. Použití funkce IF() uvnitř iterační funkce obecně není dobrá praxe, zejména v případě velkého množství záznamů v tabulce v prvním argumentu iterační funkce.

Měřítko:

Měsíce nad 2 500 000 (aditivní, lepší) =

COUNTROWS

(

    FILTER

    (

        VALUES('Date'[Měsíc rok]),

        [Prodeje] > 2500000

    )

)

Jak původní měřítko [Měsíce nad 2 500 000 (aditivní)], tak nové měřítko [Měsíce nad 2 500 000 (aditivní, lepší)] vrací obě stejné výsledky a v použitém cvičném modelu je rychlost vyhodnocení obou měřítek téměř totožná se zanedbatelným rozdílem jednotek ms. V případě většího počtu záznamů ve sloupci použitém ve funkci VALUES() již ale bude druhá verze měřítka optimálnější.

Jak je možné vidět na obrázku výše, obě měřítka vrací stejné hodnoty, které představují počet měsíců v daném období, ve kterých byly prodeje vyšší než 2 500 000.

Shrnutí

Některé typy měřítek jsou ze své podstaty neaditivní. Tato měřítka pak mohou vracet v řádcích souhrnů a v řádku "Celkem" hodnoty, které neodpovídají součtu jednotlivých hodnot zobrazených v řádcích vizuálů Matice a vizuálu Tabulka. Typickým příkladem neaditivního měřítka je měřítko vracející počet jedinečných hodnot ze sloupce. Jak jsme si vysvětlili v prvním příkladu v tomto příspěvku, snažit se udělat z neaditivního měřítka které vrací počet jedinečných hodnot měřítko aditivní není správná cesta.

Na druhou stranu existují typy výpočtů, které nejsou aditivní a aditivní by být měly. Obecně se dá říct, že tyto výpočty obvykle obsahují nějakou dodatečnou logiku, která není součástí modelu, a jejíž vyhodnocení je závislé na konkrétním kontextu. Tento kontext pak můžeme programově vytvořit pomocí některé z iteračních funkcí, například funkce SUMX(), jak bylo možné vidět ve druhém příkladu v tomto příspěvku.

Další příklady můžete najít na stránce DAX příklady nebo na stránce Power BI. Na stránce Jazyk DAX jsou pak k dispozici články popisující některé základních koncepty jazyka DAX a další články věnované vybraným DAX funkcím.

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