Niekedy nie je až tak dôležitý daný ukazovateľ samotný, ale skôr to, v akom stave je skupina ukazovateľov. V konkrétnych prípadoch je totiž stav KPI na vyššej úrovni závislý od stavov KPI na nižšej úrovni. A preto sa teraz pozrieme, ako sa s tým popasovať.

Začneme znova s našim vzorovým súborom PowerPivotu, resp. Power BI.

Spravíme si kontingenčku, kde:

1. do oblasti hodnôt dáme merítko Marža z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpce CalendarYear a MonthName z tabuľky Čas.

Výsledná kontingenčka bude zobrazovať Maržu po rokoch a mesiacoch, a bude vyzerať takto:

V tejto kontingenčke potrebujeme vyhodnotiť stav Marže, ktorá je v tomto našom prípade podkladom pre KPI. Potrebujeme ju však vyhodnotiť inak na úrovni mesiacov, a inak na úrovni rokov. Konkrétne, na úrovni mesiacov, ak je Marža vyššia ako 40%, tak je OK, inak je NOT OK. Ak sme však na úrovni rokov, tak to potrebujeme vyhodnotiť inak – Marža na úrovni roku je OK, ak sú všetky marže na úrovni mesiacov OK. A ak je aspoň jedna z marží na úrovni príslušných mesiacov NOT OK, tak je aj Marža na úrovni rokov NOT OK.

Úrovne v jazyku DAX

Úroveň si pre jednoduchosť článku odvodíme pomocou detekcie filtrov v kontexte výpočtu merítka. Ak sme na úrovni roku, tak je v kontexte výpočtu nastavený filter nad stĺpcom CalendarYear. Ak sme však na úrovni mesiacov, tak je nastavený filter aj nad stĺpcom MonthName. A o ten sa neskôr oprieme.

Ak sme teda na úrovni mesiacov, tak merítko počítajúce stav Marže bude:

Stav marže := IF([Marža] > 0,4; “OK”; “NOT OK”)

Po dosadení do kontingenčky to bude vyzerať takto:

Tam si všimnite 2 veci v rámci roku 2001:

1) v mesiaci September je Stav marže “NOT OK”, a preto by aj stav za celý rok mal byť “NOT OK”
2) v mesiaci June nie je vypočítaná žiadna Marža, ale Stav marže má hodnotu “NOT OK”, pretože prázdna hodnota sa pri porovnávaní voči 0,4 berie ako 0. A ten istý problém máme aj pri posledných mesiacoch v poslednom roku.

Bod 2) vyriešime ľahko podľa návodu v tomto článku, ktorý hovorí o tom, ako počítať merítko na základe existencie alebo neexistencie hodnoty v inom merítku. Vzorec bude:

Stav marže := IF([Marža] <> BLANK(); 
                 IF([Marža] > 0,4; "OK"; "NOT OK"); 
                 BLANK())

A po dosadení to kontingenčky sa hore uvedená drobnosť z bodu 2) vyrieši:

Čo však s prvým bodom, pre stav KPI na vyššej úrovni?

Tam na to musíme ísť trochu inak.

Najprv si potrebujeme detekovať, na ktorej úrovni sme. Jazyk DAX nemá koncept úrovní, a namiesto toho musíme rozmýšľať vo filtroch. Ako som už písal vyššie, tak ak sme na úrovni roku, tak je v kontexte výpočtu nastavený filter nad stĺpcom CalendarYear. Ak sme však na úrovni mesiacov, tak je nastavený filter aj nad stĺpcom MonthName. Pre jednoduchosť článku sa teraz nebudeme zaoberať inými úrovňami.

Vytvoríme si teda takéto, aj keď nedokončené merítko, ktoré bude na základe úrovne robiť jeden alebo druhý výpočet:

Stav marže final := IF(HASONEFILTER(‘Čas'[MonthName]); [Stav marže]; vypocitajStavNaUrovniRoka)

Namiesto slova “vypocitajStavNaUrovniRoka” pôjde výpočet stavu KPI na úrovni roka.

Toto merítko si funkciou HASONEFILTER otestuje, či je nastavený jednohodnotový filter nad stĺpcom MonthName. Viac o tom v tomto článku. Ak je nastavený, tak sme v tomto prípade na úrovni mesiaca, inak sme na úrovni roka. Na úrovni mesiaca nám stačí vrátiť ako výsledok hodnotu z merítka “Stav marže”, ale na úrovni roka to musíme napísať inak. Preto tá dočasná značka “vypocitajStavNaUrovniRoka”.

Ako sa “ponoriť” nižšie

Na úrovni roka sa musíme “ponoriť” na úroveň mesiacov, aby sme na základe toho vedeli určiť stav KPI na vyššej úrovni. Musíme prejsť všetky príslušné mesiace, a za každý z nich vypočítať hodnotu merítka “Stav marže”. Potom takýto zoznam potrebujeme prehľadať a zistiť, či sa tam nenachádza aspoň jeden zo stavov “NOT OK”.

Vieme to spraviť napr. takto:

Stav marže final :=
IF (
    HASONEFILTER ( 'Čas'[MonthName] );
    [Stav marže];
    IF (
        COUNTX (
            VALUES ( 'Čas'[MonthName] );
            IF ( [Stav marže] = "NOT OK"; 1; BLANK () )
        ) > 0;
        "NOT OK";
        "OK"
    )
)

Značku “vypocitajStavNaUrovniRoka” sme nahradili IF-om, v ktorom pomocou funkcie COUNTX iterujeme po jednotlivých mesiacoch príslušného roka, ktorých zoznam si zistíme funkciou VALUES. Pre každý mesiac otestujeme, či má “Stav marže” hodnotu “NOT OK”. Ak ju má, tak pre ten mesiac vrátime jednotku, inak vrátime prázdnu hodnotu. Následne funkcia COUNTX v takomto testovacom “stĺpci” spočíta počet neprázdnych hodnôt – čiže v našom prípade počet jednotiek. Ak je väčší ako nula, tak vráti výsledný stav “NOT OK”, inak vráti stav “OK”.

Po dosadení tohto merítka do kontingenčky to bude vyzerať takto:

V rokoch 2001 a 2002 bol vždy aspoň jeden mesiac, kde bol “Stav marže” s hodnotou “NOT OK”. Preto aj na úrovni týchto rokov je “Stav marže final” v stave “NOT OK”. Ak sa však pozrieme na roky 2003 a 2004, tak tam sú všetky mesačné marže nad 40%. Preto je aj “Stav marže final” za tieto roky v stave “OK”. Čiže presne tak, ako to malo byť podľa zadania.

A máme stav KPI na vyššej úrovni

Takto sa dá teda jednoducho určiť stav KPI na vyššej úrovni, podľa KPI na nižšej úrovni, v PowerPivote aj v Power BI. Stačí vedieť trochu lepšie jazyk DAX, predstaviť si kontext výpočtu, jeho filtre a “viditeľné” riadky v ňom, a hneď je riešenie na svete. Nejde síce o zrovna najefektívnejší vzorec z výpočtového hľadiska, ale pre univerzálnu názornosť postačuje. A ak nebudete robiť dashboardy s 3000 ukazovateľmi, tak by to ani nemalo byť potrebné riešiť. Podstatné je, že to viete vypočítať, a na základe toho potom zobraziť semafóriky a smajlíky v reporte 🙂