V jednom zo starších článkov sme si ukázali, ako ide robiť dynamická kategorizácia dát podľa číselníka. Čiže ako si vytvoriť vlastné kategórie napr. produktov podľa predajnosti, kde názvy, hranice a poradie kategórií sú uložené v samostatnom číselníku, ktorý si užívateľ môže hocikedy zmeniť, a podľa toho sa mu prepočíta štatistika. Čo ale v tom prípade, keby ste chceli spraviť takúto kategorizáciu, s ohľadom napr. na rôzne časové obdobia? Napr. keby ste mali kategorizovať, resp. segmentovať produkty či zákazníkov do kategórií, kde príslušnosť do danej kategórie môže byť iná v každom roku, napr. podľa obratu ktorý produkt/zákazník v danom roku spravil? Poďme sa na to pozrieť, ako sa to dá elegantne spraviť.

V takomto prípade musíte použiť techniku, ktorá sa nazýva dynamická kategorizácia dát pre rôzne časové obdobia. S tým, že táto technika je oveľa univerzálnejšia, a bude fungovať nielen pre rôzne časové obdobia, ale aj pre ľubovoľné iné zoskupenia či rezy údajov.

Poznámka: Toto je článok z knihy o Power BI, PowerPivote a jazyku DAX. Článok bol pre potreby blogu mierne upravený.

Pre tento príklad znova použijeme náš vzorový súbor PowerPivotu, resp. vzorový súbor Power BI.

Základom je vytvoriť si číselník, podobne ako pri klasickej dynamickej kategorizácii dát, v rovnakom tvare ako pri nej. Čiže ak chceme mať kategórie predajnosti produktov podľa obratu, tak si vytvoríme napr. takýto číselník:

Podstatné je, aby sa jednotlivé pásma neprekrývali. Hraničné hodnoty môžu byť rovnaké, pretože to vyriešime jednoducho vo vzorci nižšie. Takýto číselník samozrejme naimportujte do dátového modelu, a skontrolujte, že nie je prepojený na ostatné tabuľky. Filtrovať podľa neho nepotrebujeme, a asi ho aj tak na nič v dátovom modeli neprepojíme. Takže po naimportovaní overte, že nie je prepojený na žiadnu inú tabuľku v dátovom modeli, rovnako ako je to aj pri ostatných parametrických tabuľkách. V našom prípade sme túto tabuľku naimportovali pod názvom Kategórie predajnosti.

A teraz by sme chceli vedieť pre každý rok v histórii dát, koľko bolo produktov, ktoré v danom roku patrili do danej kategórie predajnosti, podľa obratu za daný produkt. Alternatívne, keby ste to robili za zákazníkov, tak si len v celom tomto príklade vymeňte produkty za zákazníkov. Či akúkoľvek inú entitu, ktorú potrebujete.

Najprv si vytvoríme takúto kontingenčku, resp. maticu:

1. do oblasti riadkov dáme stĺpec Názov z tabuľky Kategórie predajnosti,
2. do oblasti stĺpcov dáme stĺpec CalendarYear z tabuľky Čas,
3. do oblasti hodnôt dáme merítko Obrat.

Výsledná kontingenčka, resp. matica bude vyzerať takto:

Tu vidíme, že obrat je v každej kategórii rovnaký, pretože v dátovom modeli neexistuje prepojenie medzi tabuľkou Kategórie predajnosti, a tabuľkou Objednávky, odkiaľ sa počíta merítko Obrat. To nám však teraz nevadí. Potrebovali sme len zobraziť nejakú maticu.

Teraz by sme chceli vidieť na priesečníku kategórií a rokov, koľko bolo produktov, ktoré v danom roku patrili do danej kategórie predajnosti, podľa obratu za daný produkt.

To spravíme týmto merítkom:

Pocet produktov = IF(HASONEVALUE(‚Kategórie predajnosti'[Názov]); SUMX(SUMMARIZE(‚Objednávky‘; Produkty[ProductKey]; „Obrat za produkt“; [Obrat]); IF([Obrat za produkt] >= MAX(‚Kategórie predajnosti'[Hodnota od]) && [Obrat za produkt] < MAX(‚Kategórie predajnosti'[Hodnota do]); 1; 0)))

…alebo vypeknené cez DAX Formatter:

Následne vymeňte v kontingenčke/matici, merítko Obrat za toto nové merítko Pocet produktov. Výsledok bude vyzerať takto:

Tu vidíte, že na každom priesečníku vidíte, koľko produktov sa predalo v danom roku v rámci danej kategórie.

A ako to vlastne funguje? Veľmi jednoducho:

1. pre každú bunku v reporte sa spustí výpočet merítka samostatne, tak ako aj pri všetkých predchádzajúcich merítkach,
2. najprv si na začiatku vzorca otestujeme, že je pre danú bunku vybraná iba jedna hodnota zo stĺpca s názvami kategórií, čiže ‚Kategórie predajnosti'[Názov]; ak nie je, nepočítame nič,
3. ak je vybraná iba jedna kategória, tak si najprv funkciou SUMMARIZE, pre aktuálne počítanú bunku v reporte, zosumarizujeme príslušné riadky z tabuľky Objednávky, podľa stĺpca ProductKey (unikátny identifikátor produktu), a pre každý z nich vypočítame hodnotu obratu za daný produkt pomocou merítka Obrat,
4. potom zavoláme nad takouto tabuľkou funkciu SUMX, kde pre každý riadok otestujeme, či daný produkt podľa jeho obratu, patrí do rozsahu hodnôt Od-Do pre aktuálne počítanú kategóriu; ak patrí, vrátime pre daný riadok hodnotu 1, inak hodnotu 0,
5. funkcia SUMX následne zosumuje takýto stĺpec, čím dostaneme počet produktov patriacich do aktuálne počítanej kategórie.

Všimnite si, že vo vzorci používam funkciu MAX. Je to kvôli tomu, aby som zistil hodnotu hraníc kategórie z daných stĺpcov. A keďže v merítkach pracujeme na úrovni viacerých riadkov, tak tie príslušné stĺpce musíme zabaliť do nejakej agregačnej funkcie. Tu je viac-menej jedno akú použijete – či už SUM, MIN, MAX, AVERAGE alebo MEDIAN – pretože viete, že aktuálnemu kontextu výpočtu prislúcha len 1 riadok v tabuľke Kategórie predajnosti (to sme si otestovali už funkciou IF na začiatku vzorca). A teda agregáciou hodnôt z jedného riadka získame tú hodnotu z toho riadka.

Ak by ste chceli potom vypočítať napr. obrat za produkty v daných kategóriách a obdobiach, tak to spravíte malou úpravou predchádzajúceho vzorca. Vytvoríte napr. takéto merítko:

Obrat za kat. produktov = IF(HASONEVALUE(‚Kategórie predajnosti'[Názov]); SUMX(SUMMARIZE(‚Objednávky‘; Produkty[ProductKey]; „Obrat za produkt“; [Obrat]); IF([Obrat za produkt] >= MAX(‚Kategórie predajnosti'[Hodnota od]) && [Obrat za produkt] < MAX(‚Kategórie predajnosti'[Hodnota do]); [Obrat za produkt]; 0)))

…alebo vypeknené cez DAX Formatter:

Po pridaní do kontingenčky/matice to bude vyzerať takto (zobrazené iba pre prvé 2 roky):

A celé toto riešenie má výhodu v tom, že je dynamické. Čiže ak to nebudete chcieť po rokoch, ale napr. po krajinách, tak len vymeníte v oblasti stĺpcov roky za krajiny. Alebo akýkoľvek iný stĺpec z dátového modelu z prepojených tabuliek. A aj preto sa to nazýva dynamická kategorizácia dát. Možno by sa to vďaka tomu dalo pomenovať aj ako univerzálna dynamická kategorizácia dát.

A samozrejme to bude fungovať, aj keď zmeníme počet kategórií, alebo ich rozsahy. Keď napr. zmeníme hore uvedený číselník tak, že pridáme 4. kategóriu, a jednu z tých pôvodných rozdelíme na dve:

…tak po aplikovaní zmien v číselníku, resp. aktualizácii číselníkovej tabuľky, bude výsledok v reporte vyzerať takto:

A to je na tom to nádherné. Nie je to závislé od natvrdo nakódovaných kategórií, ale samé sa to automaticky prepočítava podľa toho číselníka. Preto sa to aj nazýva dynamický kategorizácia dát. Lebo nie je statická. Len si dajte pozor, aby sa vám v ňom neprekrývali jednotlivé rozsahy kategórii. A ako som už spomínal, tak hranice kategórii sa môžu prekrývať, pretože prvá hodnota hranice (stĺpec Hodnota od) patrí do daného pásma, a druhá hodnota hranice (stĺpec Hodnota do) už doňho nepatrí.

A takisto ako to umožňuje aj klasická dynamická kategorizácia dát, tak aj tu si môžete meniť poradie kategórií, v akom sa budú zobrazovať v reporte. Na to máte stĺpec Poradie v tomto číselníku. Potom stačí už len použiť pre stĺpec Názov funkciu „Zoradiť podľa stĺpca„, a nastaviť zoradenie podľa stĺpca Poradie. A máte vystaráno 🙂

Takže takto sa robí dynamická kategorizácia dát v rôznych časových obdobiach, či cez rôzne dátové rezy a zoskupenia. Na prvý pohľad to možno vyzerá trochu komplikovane, ale keď získate trochu zručnosť v jazyku DAX, tak takéto vzorce vysypete z rukáva za pár minút. A aj preto stojí za to naučiť sa jazyk DAX. Pretože ním viete potom veľmi ľahko riešiť nielen takéto veci, ale aj hromadu oveľa ťažších problémov, na ktoré z času na čas narazíte. A takýchto riešení nájdete v mojej novej knihe ešte oveľa viac 🙂