Niekedy vás v analýzach nezaujíma to, čo je najväčšie alebo najmenšie. Ale v konkrétnych prípadoch potrebujete zistiť, aká je druhá najväčšia hodnota. Lebo napríklad užívateľ chce pri Paretovej analýze vidieť nielen obrat za najväčšieho zákazníka, ale aj za druhého najväčšieho zákazníka, pri porovnávaní v tej istej kontingenčke alebo grafe. Preto sa teraz pozrieme na to, ako ide vypočítať druhá najväčšia hodnota v jazyku DAX.

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 Obrat z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpec CalendarYear z tabuľky Čas.

Výsledná kontingenčka bude zobrazovať Obrat po rokoch, a bude vyzerať takto:

A teraz by sme chceli vidieť ďalšie 2 ukazovatele:

1. obrat za najväčšieho zákazníka
2. obrat za 2. najväčšieho zákazníka

V oboch prípadoch využijeme to, že pri každej objednávke je v stĺpci CustomerKey zaznamenané ID zákazníka, ktorý túto objednávku spravil.

Obrat za najväčšieho zákazníka

V tomto prípade stačí vytvoriť takéto jednoduché merítko:

Obrat za najväčšieho zákazníka := MAXX(VALUES('Objednávky'[CustomerKey]); [Obrat])

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

A keď spravíme skúšku správnosti – dosadíme stĺpec Objednávky[CustomerKey] do oblasti riadkov kontingenčky, rozbalíme rok 2001 a zoradíme údaje podľa nášho nového merítka, tak obrat za najväčšieho zákazníka bude naozaj 5 918,41 EUR, ako sme to videli aj na úrovni roku:

Vzorec funguje jednoducho. Najprv si funkciou VALUES vyberieme zoznam unikátnych zákazníkov v aktuálnom kontexte výpočtu. Čiže zoznam zákazníkov v aktuálnom roku. Je to to isté, ako keby sme si tento zoznam vytvorili zoskupením objednávok podľa zákazníka pomocou funkcie SUMMARIZE. Tento zoznam nám funkcia VALUES vráti ako jednostĺpcovú tabuľku. Následne zavoláme nad ňou funkciu MAXX, ktorá najprv pre každý riadok tejto tabuľky vypočíta hodnotu Obratu (čiže obrat za daného zákazníka v danom roku), a zo všetkých týchto obratov vráti najväčšiu hodnotu. Čo je najväčší obrat, resp. obrat za najväčšieho zákazníka v danom roku.

Obrat za 2. najväčšieho zákazníka – druhá najväčšia hodnota

Čo však v prípade, ak nás zaujíma druhá najväčšia hodnota? Čiže obrat za 2. najväčšieho zákazníka v danom roku? To spravíme týmto merítkom:

Obrat za 2. najväčšieho zákazníka :=
MINX (
    TOPN (
        2;
        ADDCOLUMNS (
            VALUES ( 'Objednávky'[CustomerKey] );
            "Obrat za zákazníka"; [Obrat]
        );
        [Obrat za zákazníka]; DESC
    );
    [Obrat za zákazníka]
)

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

A aj skúška správnosti, po rozkliknutí na jednotlivých zákazníkov, nám vyjde správne:

Vzorec funguje tak, že najprv si, rovnako ako v predchádzajúcom príklade, zoskupíme objednávky podľa zákazníkov, cez funkciu VALUES (alebo aj SUMMARIZE). Potom pomocou funkcie ADDCOLUMNS pridáme k takejto tabuľke stĺpec s názvom “Obrat za zákazníka”, v ktorom bude vypočítaný Obrat za každého zákazníka. Potom funkciou TOPN vyberieme z takejto tabuľky prvé 2 riadky s najväčším obratom, čiže s najväčšou hodnotou v stĺpci “Obrat za zákazníka”. A nakoniec z takejto 2-riadkovej tabuľky vyberieme minimum zo stĺpca “Obrat za zákazníka”, pomocou funkcie MINX. Pretože v tej 2-riadkovej tabuľke z funkcie TOPN je obrat za najväčšieho aj druhého najväčšieho zákazníka, a teda minimum z tých dvoch je obrat za druhého najväčšieho zákazníka. A takto elegantne sa dá zistiť druhá najväčšia hodnota.

A čo ak je druhá najväčšia hodnota rovnaká ako prvá?

Ak by náhodou tí 2 najväčší zákazníci mali rovnaký obrat (napr. po zaokrúhlení na milióny), tak funkcia TOPN vráti viac riadkov ako 2, tak ako som o tom písal už v staršom článku o záludnostiach funkcie TOPN. V tom prípade stačí upraviť parametre funkcie TOPN z “[Obrat za zákazníka]; DESC” na “[Obrat za zákazníka]; DESC; [CustomerKey]; ASC”:

Obrat za 2. najväčšieho zákazníka :=
MINX (
    TOPN (
        2;
        ADDCOLUMNS (
            VALUES ( 'Objednávky'[CustomerKey] );
            "Obrat za zákazníka"; [Obrat]
        );
        [Obrat za zákazníka]; DESC; [CustomerKey]; ASC
    );
    [Obrat za zákazníka]
)

Týmto vzorcom sa potom vypočíta už správna druhá najväčšia hodnota aj v tomto prípade, aj keď je to de facto rovnaká hodnota ako tá najväčšia.

Takto elegantne sa dá teda zistiť druhá najväčšia hodnota v jazyku DAX. A tento postup sa dá s miernymi úpravami aplikovať aj na iné prípady “druhých hodnôt”, tretích či N-tých hodnôt. A deň je hneď znova o niečo krajší 🙂