Niekedy nepotrebujete v reportoch Power BI zobraziť komplet celú štatistiku. Niekedy vás zaujímajú iba prvé alebo posledné záznamy. V Power BI máme na to síce štandardnú možnosť odfiltrovania na TOP N riadkov, ale nie všetko funguje vždy tak, ako má. Preto sa teraz pozrieme na to, ako zobraziť presných TOP N riadkov v kontingenčke v Power BI.

Klasický filter pre TOP N riadkov

Začneme opäť s našim vzorovým súborom Power BI. Vytvoríme kontingenčku, kde:

1. do oblasti riadkov dáme stĺpec ProductKey z tabuľky Produkty,
2. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
3. kontingenčku dáme zoradiť podľa Obratu od najväčších hodnôt po najmenšie.

Výsledná kontingenčka bude zobrazovať obraty pre každý produkt, postupne od najpredávanejšieho po najmenej predávaný. Bude vyzerať takto:

A teraz by sme ju chceli odfiltrovať na TOP 3 najpredávanejšie produkty. Nie je nič ľahšieho, vo filtrovacom paneli rozbalíme filter pre pole ProductKey, a nastavíme:

1. typ filtra na “Horných N” (skvelý slovenský preklad z anglického “TOP N”, lepšie preložený ako “Prvých N”),
2. zobraziť položky na “Hore” a číslo 3 (opäť skvelé slovenské preklady),
3. do oblasti “Podľa hodnoty” dáme merítko “Obrat”,
4. klikneme na Použiť filter.

Výsledné nastavenia budú vyzerať takto:

…a aj výsledná kontingenčka bude správne odfiltrovaná len na TOP 3 najpredávanejšie produkty:

Potiaľto fajn. Zatiaľ.

Keď filtrovanie pre presných TOP N riadkov nefunguje

Skúsime si to ale sťažiť. Zmeníme teraz vzorec pre merítko Obrat tak, aby počítalo sumu cien objednávok (tak ako doteraz), ale tentokrát zaokrúhlenú na státisíce:

Obrat = ROUND(SUM('Objednávky'[Cena objednávky]); -5)

Po potvrdení sa kontingenčka prepočíta, a… zrazu zobrazí oveľa viac riadkov, ako očakávané 3:

A ako aj v mnohých ďalších prípadoch sme aj teraz narazili na funkcionalitu, ktorá nefunguje ako má. A samozrejme v oficiálnej dokumentácii o tom ani slovo. Čo však neznamená, že to nejde spraviť presne tak, ako chceme.

V čom je problém

Najprv sa musíme pozrieť na to, v čom je problém. Na zobrazenie tejto kontingenčky Power BI použilo tento daxový dotaz:

// DAX Query
DEFINE
  VAR __SQDS0Core = 
    SUMMARIZECOLUMNS('Produkty'[ProductKey], "Obrat", 'Objednávky'[Obrat])

  VAR __SQDS0BodyLimited = 
    TOPN(3, __SQDS0Core, [Obrat], 0)

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Produkty'[ProductKey], "IsGrandTotalRowTotal"),
      __SQDS0BodyLimited,
      "Obrat", 'Objednávky'[Obrat]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, [Obrat], 0, 'Produkty'[ProductKey], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, [Obrat] DESC, 'Produkty'[ProductKey]

V ňom vidno, že na odfiltrovanie TOP 3 záznamov sa použila funkcia TOPN. A ako som už písal v staršom článku, tak funkcia TOPN je aj v tomto prípade kameňom úrazu. Pretože táto funkcia nevracia vždy presných TOP N riadkov, ale niekedy je tých riadkov viac. V tom článku nájdete popísané to, že táto funkcia používa interne funkciu RANKX na oskórovanie záznamov, a potom vyberie všetky záznamy, ktorých skóre je menšie lebo rovné N. Čo nie je problém, ak sú všetky skórované hodnoty (v našom prípade Obraty) rôzne, tak ako je to v prípade prvej kontingenčky vyššie.

Ak však už máme rovnaké skórovacie hodnoty na viacerých riadkoch – v tomto prípade rovnaké obraty v státisícoch – tak funkcia TOPN priradí riadkom s rovnakou hodnotou rovnaké skóre. A ďalšiemu riadku s ďalšou rôznou hodnotou dá nie nasledujúce skóre, ale skóre “preskočí” toľko miest, koľko bolo duplicitne udelených miest predtým. Na demonštráciu si to môžeme zobraziť takýmto merítkom v kontingenčke, ktoré zobrazí toto skóre pre každý riadok:

Skóre = RANKX(ALL(Produkty); [Obrat]; [Obrat]; DESC; Skip)

Po dosadení tohto merítka do kontingenčky bude hneď jasné, kde je problém:

Vidíte, že k prvým dvom produktom to priradilo skóre 1, a ostatným zobrazeným produktom skóre 3, pretože miesto č. 1 bolo udelené duplicitne. Následný filter, ako som už písal vyššie, odfiltruje kontingenčku ako keby na takýto stĺpec, na podmienku “Skóre <= 3”. A preto ich zobrazí toľko veľa.

Ako zobraziť presných TOP N riadkov v kontingenčke

Keď teraz vieme, v čom je problém, tak sa môžeme pohnúť k riešeniu. Pretože výhovorka “tak to funguje v Power BI” častokrát užívateľov nezaujíma, a chcú zobraziť presných TOP N riadkov v kontingenčke.

Jednou z prvých možností by bolo zadať do funkcie RANKX viacero skórovacích kritérií, podobne ako to ide zadať do funkcie TOPN. Bohužiaľ funkcia RANKX umožňuje zadať iba 1 skórovacie kritérium. Takže budeme musieť ísť na to inak. A riešení je našťastie habadej.

Najjednoduchším riešením je vytvoriť ďalšie merítko, ktoré neskôr použijeme vo filtri TOP N v kontingenčke namiesto merítka Obrat. Napríklad takéto:

Obrat pre skórovanie = [Obrat] + SELECTEDVALUE(Produkty[ProductKey])

Riešením je k merítku Obrat pripočítať ID produktu zobrazeného na danom riadku kontingenčky, a tým z pôvodne duplicitných hodnôt spraviť odlišné, jednoznačné hodnoty pre skórovanie:

V tomto prípade vieme, že jednotlivé zaokrúhlené Obraty skáču po hodnote 100 000, takže pripočítanie príslušného ID produktu k nim, najmä keď v tomto prípade vieme že ID produktu je v tejto databáze od 1 do 606, nespôsobí pretečenie spočítanej hodnoty do inej skórovacej kategórie. Len spôsobí rozlíšenie pôvodne duplicitných hodnôt, na jednoznačné hodnoty. A keby ste mali iné hodnoty ID-čok, tak sa postarajte, aby boli všetky v rámci toho “skoku” pre pôvodné skórované hodnoty. Čiže ak máte skórované hodnoty v skokoch napr. po 10 000, tak to ID by malo tiež spadať do intervalu 0 – 9999. A ak nespadá, tak ho skúste nejakou vhodnou matematickou operáciou zmenšiť, resp. normalizovať. Napríklad ak má spadať do tých 10-tisícov, tak to ID predeľte číslom 10000 alebo väčším, tak aby to tam spadlo.

Ak by sa Vám nechcelo tie hodnoty normalizovať, alebo by to “proste nešlo”, tak môžete použiť ako skórovacie merítko napr. toto:

Obrat pre skórovanie 2 = 
VAR maxID = CALCULATE(MAX(Produkty[ProductKey]); ALL(Produkty))
RETURN
[Obrat] * maxID + SELECTEDVALUE(Produkty[ProductKey])

Výsledné čísla budú oveľa väčšie ako v predchádzajúcom prípade:

…ale splní to účel rovnako. S minimálnym rozmýšľaním.

Nakoniec stačí už iba zmeniť filter v kontingenčke. V nastavení filtra TOP N stačí vymeniť merítko Obrat za merítko “Obrat pre skórovanie” (či už prvá alebo druhá verzia):

…a po použití filtra nám to konečne zobrazí presných TOP N riadkov v kontingenčke:

Následne stačí už iba vyhodiť z kontingenčky všetko, čo tam nechceme – pretože skórovacie a pomocné merítka tam nemusia byť nutne zobrazené – a máme presne to, čo sme chceli:

Vo výsledku síce nesedí celkový súčet s tými troma sumami, ale to len preto, lebo upravené merítko Obrat sa vyhodnotilo pre tú bunku rovnako ako pre ostatné bunky – najprv spočítalo celkový súčet z nezaokrúhlených hodnôt (v tomto prípade 2,96 mil.), a potom ho zaokrúhlilo na státisíce – čo v tomto prípade spravilo číslo 3 milióny. Ak to chcete mať v poriadku, tak použite techniku vizuálneho súčtu, a to vyrieši aj tento problém 🙂

Na záver

Takže toto je relatívne jednoduchý spôsob, ako zobraziť presných TOP N riadkov v kontingenčke v Power BI. A ako by povedali v reklame, tak aj svokra by bola prekvapená, ako som to zvládla 😀 Každopádne, vidíte, že po troche pátrania a so solídnou znalosťou jazyka DAX nie je problém rýchlo vyriešiť aj takýto problém. Nemôžeme predsa chcieť od Power BI, aby bolo dokonalé, že? 🙂