Pri reportingu buď cez PowerPivot alebo cez Power BI sa niekedy stane to, že máte zobrazených veľmi veľa položiek v kontingenčke alebo v grafe. Pričom väčšina z nich Vás nezaujíma, pretože sú príliš malé v porovnaní s ostatnými. Typickým príkladom je analýza TOP klientov alebo TOP produktov, kde Vás zvyčajne zaujímajú len čísla za najlepších klientov alebo produkty, a ostatné položky by ste chceli mať zobrazené napr. v sumárnom riadku „ostatné„, prípadne len v jednom súhrnnom výreze koláčového grafu, tiež označenom ako „ostatné“. Toto sa dá celkom efektne vyriešiť pomocou techniky TOPN+1, ktorá umožňuje zoskupiť malé položky do jednej kategórie, a na ktorú sa teraz pozrieme.

Povedzme, že na začiatok si spravíme kontingenčku z nášho vzorového súboru PowerPivotu, kde na riadky dáme názov produktu (stĺpec ProductName z tabuľky Produkty), a do stĺpcov merítko Obrat z tabuľky Objednávky, a dáme zotriediť kontingenčku od najväčšieho obratu po najmenší:

Tu vidíte, že prvých pár produktov má vysoký obrat, a pri ďalších produktoch je už oveľa nižší. Kontingenčka má ale niekoľko strán, a nás zaujíma len TOP 10 produktov. To by sme pohodlne vedeli zobraziť cez funkcionalitu kontingenčky, ale povedzme, že Váš šéf Vám dal zadanie, že chce všetky nepodstatné položky – čiže tie čo nie sú v TOP 10 – zobraziť jediným súhrnným riadkom s názvom „ostatné“, v ktorom bude kumulatívny obrat za všetky takéto položky. Masochisti okamžite začnú čarovať s funkciami kontingenčky a zliepaním viacerých kontingenčiek do jedného reportu, ale my si radšej pomôžeme DAX-om a zopár vypočítanými stĺpcami, aby sme mohli zoskupiť malé položky do jednej kategórie priamo v pôvodnej kontingenčke 🙂

Prvým krokom bude vytvorenie nového vypočítaného stĺpca v PowerPivote, v tabuľke Produkty, ktorý bude obsahovať obrat za každý produkt. Ten už v našom vzorovom súbore máme vytvorený pod názvom „Obrat za produkt„. Ak neviete, ako ho vypočítať, pozrite si podrobný návod v tomto článku.

Druhým krokom bude oskórovať si každý produkt podľa obratu, pričom najpredávanejší produkt dostane skóre 1, druhý najpredávanejší 2, atď.. To docielime ďalším vypočítaným stĺpcom, ktorý nazveme „Poradie predajnosti„, a ktorého vzorec bude:

=RANK.EQ([Obrat za produkt]; [Obrat za produkt])

Funkcia RANK.EQ jednoducho skóruje číslo zadané v prvom parametri voči stĺpcu zadanému v druhom parametri funkcie. Pričom najväčšia hodnota v stĺpci dostane skóre 1, druhá najväčšia skóre 2, atď.. V najjednoduchšom prípade teda jednoducho vymenujte do oboch parametrov ten istý stĺpec, ak nepotrebujete nič špeciálne. Ak by ste ale chceli mať väčšiu kontrolu nad výpočtom skóre, môžete použiť napr. funkciu RANKX, alebo vlastný DAX-ový výraz.

Potom už len stačí vytvoriť ďalší stĺpec, ktorým dosiahneme to, že všetky položky so skóre 1 až 10 budú zobrazené svojim menom, a všetky ostatné spadnú pod položku s názvom „ostatné„. Ten stĺpec pomenujeme „TOP10+1 produktov„, a vzorec preňho bude:

=IF([Poradie predajnosti] <= 10 && [Poradie predajnosti]<>BLANK(); [ProductName]; „ostatné“)

Vzorec robí to, že keď je v stĺpci „Poradie predajnosti“ číslo menšie alebo rovné číslo 10, a zároveň je ten stĺpec neprázdny (lebo prázdna hodnota sa v DAX-e berie ako nula), tak v tomto stĺpci zobrazíme názov produktu, a vo všetkých ostatných prípadoch tam zobrazíme názov „ostatné„. Toto spôsobí to, že keď dáte takýto stĺpec do riadkov kontingenčky namiesto názvu produktu, tak všetky položky, ktoré neboli v TOP10, budú zoskupené do položky „ostatné“. Je to preto, lebo keď kontingenčka generuje riadky, tak si od PowerPivotu vypýta najprv zoznam všetkých jedinečných hodnôt z toho stĺpca, a až potom robí výpočty. A keďže je v tomto stĺpci buď názov produktu (ak je v TOP10) alebo je tam napísané „ostatné“, tak to spôsobí to, že to zobrazí v kontingenčke TOP10 produktov, plus jeden riadok „ostatné“, do ktorého spadne štatistika za všetky ostatné produkty. Čo nám vo výsledku pomôže zoskupiť malé položky do jednej kategórie. Odstráňte teda z kontingenčky stĺpec ProductName, a dajte tam namiesto neho stĺpec „TOP10+1 produktov„:

Má to však ešte jednu kozmetickú vadu – tá položka „ostatné“ nie je zobrazená ako posledná. Môžete to síce spraviť jej pretiahnutím na koniec, ale to by ste museli robiť zakaždým v každej takejto kontingenčke. A keby ste to chceli zobraziť ako posledný výrez v koláčovom grafe, tak v Exceli aj Power BI asi máte smolu… Ak to teda nespravíte radšej inteligentnejšie – vytvoríte si ďalší vypočítaný stĺpec, ktorý bude obsahovať správne poradie položiek, a využijete funkciu „Zoradiť podľa stĺpca“ na zoradenie položiek pôvodného stĺpca do správneho poradia. Vytvoríme teda vypočítaný stĺpec s názvom „TOP10+1 produktov poradie„, so vzorcom podobným tomu predchádzajúcemu:

=IF([Poradie predajnosti] <= 10 && [Poradie predajnosti]<>BLANK(); [Poradie predajnosti]; 1000)

Tento stĺpec priradí položkám z TOP10 ich skóre ako poradovú hodnotu, a položke „ostatné“ dá poradové číslo 1000. Namiesto tej tisícky môžete použiť ľubovoľné číslo – stačí, aby bolo väčšie ako N vo Vašom TOPN.

Následne dajte zoradiť stĺpec „TOP10+1 produktov“ podľa tohto nového stĺpca „TOP10+1 produktov poradie„, a výsledná kontingenčka bude vyzerať takto:

Fajnšmekri si môžu vytvoriť aj koláčový graf z tých istých stĺpcov ako kontingenčka, kde budú položky automaticky usporiadané rovnakým spôsobom:

Takto si teda môžete zjednodušiť reporting, a veľmi jednoduchým spôsobom zoskupiť malé položky do jednej položky. A aj to je dôvod, prečo ovládať jazyk DAX. Pretože po troche zamyslenia s ním dokážete naozajstné divy 🙂