Power BI - Řazení hodnot podle jiného sloupce a na co si dát pozor

V Power BI je k dispozici funkce "Seřadit podle sloupce", která umožňuje autorům reportů seřadit hodnoty v jednom sloupci podle hodnot v jiném sloupci ze stejné tabulky. Typickým příkladem použití této funkce mohou být názvy měsíců v kalendářní tabulce. Pokud vložíme měsíce z kalendářní tabulky do vizuálu, měsíce jsou v základním nastavení seřazeny abecedně. Přirozenější řazení měsíců je ale určitě řazení podle pořadí měsíce v roce. Toho můžeme dosáhnout právě seřazením sloupce s názvy měsíců podle sloupce s pořadovými čísly měsíců.

Použití funkce "Seřadit podle sloupce" je v mnoha situacích velmi užitečné nebo přímo nezbytné, nicméně toto nastavení může mít také negativní dopad na měřítka, která mohou za určitých okolností přestat vracet očekávané výsledky, především pokud jsou vyhodnocena v kontextu sloupce seřazeného podle hodnot v jiném sloupci a pokud současně v měřítku manipulujeme s filtry.

V tomto příspěvku si ukážeme jeden z příkladů, kdy správně definované měřítko přestane fungovat právě z důvodu seřazení sloupce podle hodnot v jiném sloupci. Vysvětlíme si proč tomu tak je a jak měřítko opravit tak, aby opět vracelo správné výsledky.

Řazení hodnot ve sloupci podle hodnot v jiném sloupci a dopad na výpočty v měřítku

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 si můžete případně stáhnout pod odkazem uvedeným níže pod tímto příspěvkem.

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

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Začít můžeme jednoduchým reportem, ve kterém si vytvoříme průřez s roky z kalendářní tabulky, a dále vizuál Matrix s názvy měsíců v řádcích a s měřítkem [Prodeje] v hodnotách.

Jak je možné vidět na obrázku výše, měsíce v řádcích vizuálu jsou seřazeny abecedně, což není pro uživatele příliš přehledné. Před tím, než si hodnoty ve sloupci s názvy měsíců seřadíme podle sloupce s pořadovými čísly měsíců, můžeme si přidat do modelu měřítko, které bude vracet procentuální podíl prodejů v aktuálním měsíci vůči prodejům v celém roce.

Měřítko:

% Podíl prodejů =

VAR ProdejeVCelemRoce =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'[Měsíc název])

    )

VAR ProdejeVAktualnimMesici =

    [Prodeje]

VAR Vypocet = DIVIDE(ProdejeVAktualnimMesici, ProdejeVCelemRoce)

RETURN

    Vypocet

Měřítko [% Podíl prodejů] si můžeme vložit do vizuálu Matrix ve dříve vytvořeném reportu.

Nové měřítko [% Podíl prodejů] vrací podle očekávání procentuální podíl prodejů v aktuálním měsíci a vybraném roce vůči prodejům v celém roce.

Nyní, když máme vytovřené měřítko ve kterém manipulujeme s filtry, můžeme si seřadit hodnoty ve sloupci 'Date'[Měsíc název] podle sloupce 'Date'[Měsíc číslo], který obsahuje pořadové číslo měsíců v roce. K seřazení sloupce s názvy měsíců stačí tento sloupec označit, a dále na kartě "Nástroje tabulky" otevřít seznam u tlačítka "Seřadit podle sloupce", kde vybereme název sloupce s pořadovým číslem měsíců.

Pokud se nyní vrátíme do reportu, můžeme si všimnout, že měsíce jsou nyní opravdu seřazeny podle pořadí v roce, a ne podle abecedního pořadí, jak tomu bylo v původním reportu před seřazením hodnot ve sloupci s názvy měsíců podle sloupce s pořadovým číslem měsíců.

Názvy měsíců jsou nyní seřazeny podle pořadí v roce, nicméně současně přestalo fungovat měřítko [% Podíl prodejů], které nyní vrací pro každý měsíc stejnou hodnotu 100 %.

Vysvětlení, proč měřítko [% Podíl prodejů] přestalo fungovat, je skryté na pozadí vizuálu Matrix. Pro popis problému si můžeme zobrazit DAX dotaz, který je vygenerovaný na pozadí vizuálu Matrix a který načítá data z Tabulárního modelu.

Důležité části dotazu pro vyřešení problému s měřítkem [% Podíl prodejů] jsou na obrázku výše zvýrazněny.  V posledním řádku dotazu můžeme vidět, že celý dotaz je mimo jiné seřazen také podle sloupce s pořadovým číslem měsíců. Aby tento sloupec mohl být použitý pro řazení, musí být také součástí načtených dat z modelu, což je možné vidět uvnitř funkce SUMMARIZECOLUMNS(). Šipky pak znázorňují kontext, ve kterém je měřítko [% Podíl prodejů] vyhodnoceno.

Pozn.: Načítání hodnot ze sloupce určeného pro řazení jiného sloupce je vlastnost vztahující se pouze k nástrojům, které pro dotazování do datového modelu generují dotazy v jazyku DAX. Zatímco v Power BI dojde na pozadí také k načtení sloupce použitého k řazení sloupce použitého ve vizuálu, tak například v Excelu, kde jsou dotazy generovány v jazyku MDX, nedochází k načítaní hodnot ze sloupců použitých pro řazení, protože sloupce jsou v Excelu, na rozdíl od Power BI, řazeny podle metadat v modelu.

Měřítko [% Podíl prodejů] přestalo fungovat po seřazení sloupce 'Date'[Měsíc název] podle sloupce 'Date'[Měsíc číslo], protože sloupec použitý pro řazení se stal součástí dotazu na pozadí vizuálu, a měřítko [% Podíl prodejů] je nově vyhodnoceno také v kontextu tohoto sloupce použitého pro řazení.

Pokud tedy v měřítku [% Podíl prodejů] odstraňujeme filtry ze sloupce s názvy měsíců, sloupec s pořadovými čísly měsíců stále na měřítko působí, a to je ten důvod proč měřítko [% Podíl prodejů] přestalo vracet správné výsledky. S touto znalostí je oprava měřítka jednoduchá. Stačí do funkce REMOVEFILTERS() v definici dělitele přidat také sloupec použitý pro řazení.

 Měřítko:

% Podíl prodejů (Opraveno) =

VAR ProdejeVCelemRoce =

    CALCULATE

    (

        [Prodeje],

        REMOVEFILTERS('Date'[Měsíc název], 'Date'[Měsíc číslo])

    )

VAR ProdejeVAktualnimMesici =

    [Prodeje]

VAR Vypocet = DIVIDE(ProdejeVAktualnimMesici, ProdejeVCelemRoce)

RETURN

    Vypocet

Pokud novou verzi měřítka vložíme do původního vizuálu, můžeme vidět, že výpočet opět začal fungovat, a to i v kontextu sloupce seřazeného podle hodnot v jiném sloupci.

Jak je nyní možné vidět na obrázku výše, nové měřítko [% Podíl prodejů (Opraveno)] nyní funguje a vrací opět správné výsledky.

Shrnutí

Pokud využíváme možnost řazení hodnot ve sloupci podle hodnot v jiném sloupci, můžeme při výpočtech v měřítku dostávat neočekávané výsledky. Z tohoto důvodu je dobré vědět, že vizuály na pozadí generují dotazy. Tyto dotazy obsahují i případné sloupce určené pro řazení hodnot ve sloupcích, které jsou použity ve vizuálech. V případě že ve výpočtech manipulujeme s filtry, musíme se sloupci určenými pro řazení jiných sloupců počítat a zahrnout je do výpočtů pro dosažení očekávaných výsledků. 

Další příklady můžete najít na stránce Power BI nebo na stránce DAX příklady.

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