Pri niektorých analýzach v jazyku DAX, či už v PowerPivote, Power BI alebo SSAS Tabulare, sa stane, že celkové súčty v niektorých stĺpcoch nesedia so súčtom tých detailných čísel nad nimi. A užívateľ by chcel, aby sedeli, aj keď to nie vždy dáva zmysel. Pozrieme sa, ako sa s tým jednoducho popasovať, a ako spraviť visual totals – súčet zobrazených hodnôt – v jazyku DAX.

Na čo sú potrebné visual totals

Tento prípad sa môže stať z viacerých dôvodov. Niekedy ste v danom stĺpci použili distinct count – počet jedinečných položiek – a vtedy väčšinou celkový súčet nesedí. Takisto napr. pri prepojeniach typu M:N, o ktorých píšem v mojej knihe o Power BI, a pri ktorých je súčet jednotlivých detailných hodnôt vždy vyšší ako číslo v celkovom súčte. Alebo aj keď zobrazujete rôzne čiastkové štatistiky, ako napr. príklad nižšie pre dynamických TOP N položiek. V tom prípade chce užívateľ oprávnenie vidieť správny súčet detailných hodnôt. Preto na našu záchranu prichádzajú visual totals – súčty zobrazených hodnôt – v našom milovanom jazyku DAX.

Začneme znova našim vzorovým súborom PowerPivotu, resp. Power BI. Spravíme si takúto kontingenčku, kde:

1. na riadkoch bude stĺpec ProductName z tabuľky Produkty,
2. v hodnotách bude merítko Obrat z tabuľky Objednávky,
3. dáme zoradiť kontingenčku od najväčšieho obratu po najmenší.

Kontingenčka bude vyzerať takto:

Zobrazia sa nám všetky produkty aj s ich obratmi, zoradené od najpredávanejšieho po najmenej predávaný. Povedzme však, že užívateľovi reportu sa nepáči taká dlhá tabuľka, a chcel by z nej vidieť iba prvých 5 riadkov. To nie je problém nastaviť vo filtroch pre kontingenčku, ako filter pre TOP 5 položiek podľa Obratu.

Ak by ale užívateľ chcel dynamicky meniť počet položiek v TOP N za iné čísla tlačítkami v reporte (viď technika odpojených slicerov v mojej knihe o Power BI), tak na to musíme ísť inak. Podobne ako v staršom článku o dynamických TOP N položkách. A namiesto merítka Obrat musíme v kontingenčke použiť iné merítko, ktoré sa bude počítať iba pre prvých TOP N produktov. A to N musí byť ako číslo vo vzorci, aby sme to potom vedeli napojiť na voľbu užívateľa, napr. cez slicer.

Dynamických TOP N položiek v reporte

Preto si vytvoríme takéto merítko:

Obrat pre TOP N produktov := IF(RANKX(ALL(Produkty[ProductName]); [Obrat]; [Obrat]) <= 5; [Obrat])

Toto merítko sa vypočíta iba pre prvých 5 najpredávanejších produktov. Keď dáme do kontingenčky, tak bude vyzerať takto:

A po odstránení merítka Obrat z kontingenčky nám táto zobrazí už iba tých 5 produktov. Problém však bude s celkovým súčtom, ktorý teraz nesedí so súčtom čísel nad ním:

Je tam zobrazených stále celkových 26 miliónov, namiesto očakávaných cca. 6 miliónov. A v tomto prípade by mal užívateľ vidieť správny súčet. Môžete síce schovať celkový súčet, či už cez nastavenia kontingenčky alebo cez DAX, ale to problém iba zamaskuje. A my ho potrebujeme vyriešiť.

Technika visual totals

Na záchranu k nám prichádza technika visual totals – čiže súčtu zo zobrazených čísel. Problém však je, že sa na bunky v kontingenčke nemôžeme adresovať priamo. Preto to treba vyriešiť správnym daxovým myslením a využitím toho, čo máme dostupné v kontexte výpočtu.

Vzorec pre visual totals v jazyku DAX je vo všeobecnosti takýto:

funkciaX(VALUES(stĺpec); merítko)

…kde stĺpec je stĺpec použitý na riadkoch/stĺpcoch kontingenčky (v našom prípade ProductName), merítko je merítko zobrazené v kontingenčke (v našom prípade “Obrat pre TOP N produktov”), a funkciaX je X-ková verzia funkcie, ktorá sa použila pre agregáciu pôvodného merítka (v našom prípade pre merítko Obrat sme použili funkciu SUM).

Takže vzorec pre visual totals v našom prípade bude:

Obrat pre TOP N produktov s visual totals := SUMX(VALUES(Produkty[ProductName]); [Obrat pre TOP N produktov])

A po dosadení merítka do kontingenčky už bude zobrazovať správny celkový súčet:

Predchádzajúce merítko z kontingenčky vyhodíme (ale ponecháme v dátovom modeli, lebo ho používame do výpočtu), a práca je hotová:

A keď v tom pôvodnom merítku zmeníme číslo 5 povedzme na číslo 3 – lebo chceme vidieť iba prvé TOP 3 produkty – tak sa nám to všetko zobrazí a spočíta správne:

A ako to funguje?

Funkcia SUMX je iterátor, tak ako všetky X-kové funkcie. Zoberie tabuľku z prvého parametra, a prechádza/iteruje cez ňu riadok po riadku. Na každom riadku vypočíta vzorec, ktorý sme zadali do jej druhého parametra, v kontexte aktuálne prechádzaného riadka. A výsledky si ako keby “odkladá” do ďalšieho, skrytého stĺpca v tej tabuľke. Ktorý nakoniec zosumuje funkciou SUM. Prípadne si to môžete predstaviť tak, že x-ková funkcia ako keby pridala k tej tabuľke ďalší vypočítaný stĺpec na jej koniec, a potom ho zosumovala. Na pozadí to síce funguje inak, ale pre jednoduchosť sa to dá predstaviť aj takto.

V našom prípade sme do funkcie SUMX zadali tú tabuľku funkciou VALUES. Tá vráti jednostĺpcovú tabuľku s unikátnymi hodnotami zo stĺpca ProductName, viditeľné v aktuálnom kontexte výpočtu. A voči nim postupne spočíta merítko “Obrat pre TOP N produktov”, a výsledné hodnoty potom zosumuje.

Ak sme v kontingenčke na úrovni produktu, tak je v kontexte výpočtu viditeľný iba daný jeden produkt. Preto daný SUMX vypočíta sumu obratu iba za tento produkt. Keď sme však na úrovni celkového súčtu, tak tam už sú v kontexte výpočtu viditeľné všetky produkty. Funkcia SUMX v tomto prípade teda prejde po všetkých produktoch, a vypočíta dané merítko. Ktoré sa však počíta iba za prvých N naj produktov. Preto sa v tomto prípade vypočíta pre prvých 5, resp. 3 produkty (podľa toho, na ktorú verziu merítka sa pozeráte). Pre ostatné produkty to merítko vypočíta prázdnu hodnotu. Následné zosumovanie všetkých takýchto hodnôt nám vráti visual totals za prvých N produktov. A de facto visual totals pre zobrazené položky. Čo je presne to, čo sme chceli.

---

Takto sa teda počítajú visual totals v jazyku DAX, v PowerPivote, v Power BI aj v SSAS Tabulare. Je to až geniálne jednoduchá technika, keď viete pracovať s kontextom výpočtu, X-kovými funkciami, a využívate ich základné vlastnosti. Najmä však užívateľovi zobrazíte presne to, čo mu dáva zmysel. Viete ešte, čo všetko sa dá robiť v DAX-e, keď takéto problémy idú vyriešiť takto jednoducho? 🙂