Dnes sa pozrieme na jeden konkrétny problém, ktorý som nedávno riešil u jedného klienta. Išlo o to, ako v skupine dát vypočítať merítko, ktoré zobrazí najlepšiu hodnotu spomedzi podhodnôt v skupine. Aby ste si to vedeli lepšie predstaviť, ukážeme si riešenie tohto problému na našich dátach.

Na začiatok si stiahnite vzorový Excel s dátovým modelom. Následne si spravte takúto kontingenčku, ktorá bude zobrazovať obrat podľa jednotlivých krajín:

1. do oblasti hodnôt dajte Obrat z tabuľky Objednávky
2. do oblasti riadkov dajte stĺpec Krajina z tabuľky Regióny

Kontingenčka bude vyzerať takto:

V tomto prehľade máme zobrazený obrat pre každú krajinu v našom dátovom modeli. Ten je počítaný z tabuľky objednávok, kde každá objednávka má pri sebe zaznamenané, v akej mene je suma objednávky (stĺpec Cena objednávky v tabuľke Objednávky). Keď si teraz do riadkov pridáte stĺpec Mena z tabuľky Objednávky, tak kontingenčka bude vyzerať takto:

Z nej vidno, že objednávky vo všetkých krajinách boli urobené vo viacerých menách. Čiže napr. v United Kingdom boli niektoré objednávky urobené v librách, iné v dolároch. Z pohľadu jednotlivých objednávok však bola každá objednávka spravená len v jednej mene.

A teraz zadanie, ktoré bolo treba vyriešiť: Vytvorte merítko, ktoré bude zobrazovať obrat v najpredávanejšej mene v krajine. Merítko má zobrazovať sumu aj menu.

Kvôli jednoduchosti riešenia som problém rozdelil na 2 časti:

1. merítko, ktoré vypočíta sumu v najpredávanejšej mene v aktuálnom rozkliku (kontexte)
2. merítko, ktoré vypočíta najpredávanejšiu menu v aktuálnom rozkliku (kontexte)

Po vyriešení týchto 2 merítok stačí spraviť finálne merítko, ktoré zlepí hodnoty z týchto 2 merítok dokopy, a potom skryjeme tieto 2 pomocné merítka z ponuky.

Prvé merítko má nasledovný vzorec:

Obrat v naj mene :=
MINX (
TOPN (
1;
SUMMARIZE ( ‚Objednávky‘; [Mena]; „Obrat za menu“; [Obrat] );
[Obrat za menu]; 0
);
[Obrat za menu]
)

Postup výpočtu funguje takto:

1. Najprv si funkciou SUMMARIZE zoskupíme objednávky v aktuálnom kontexte/rozkliku podľa stĺpca Mena, a pre každú menu dáme vypočítať Obrat. To nám vyrobí v pamäti tabuľku, ktorá pre danú krajinu bude obsahovať obrat podľa všetkých použitých mien. Napr. pre krajinu United Kingdom bude vyzerať takto:
   
2. Z tejto tabuľky si funkciou TOPN dáme vrátiť riadok s najvyššou hodnotou v stĺpci Obrat za menu. Tento stĺpec sme si vyrobili v predchádzajúcom kroku. Funkcia TOPN má nasledujúce parametre:
   1. Prvý parameter hovorí o tom, koľko riadkov chceme vrátiť zo zadanej tabuľky
   2. Druhý parameter je tabuľka, z ktorej chceme vrátiť daný počet riadkov (toľko, koľko je uvedených v prvom parametri funkcie TOPN)
   3. Tretí parameter je stĺpec, podľa ktorého sa zoradí tabuľka zadaná v druhom parametri funkcie TOPN
   4. Štvrtý parameter hovorí o smere zoradenia: 0 = zostupne, 1 = vzostupne
3. Keďže funkcia TOPN vráti v našom prípade 1 riadok, musíme z neho dostať hodnotu obratu. To spravíme funkciou MINX (alebo MAXX, SUMX, AVERAGEX, je to zajedno), kde:
   1. Prvý parameter hovorí o tabuľke, nad ktorou ideme spustiť funkciu MIN
   2. Druhý parameter hovorí o tom, z ktorého stĺpca sa má vybrať minimálna hodnota. A keďže sme tejto funkcii dali len 1 riadok, tak týmto trikom vyberieme len tú 1 hodnotu z riadku 🙂

Druhé merítko, ktoré vypočíta názov najpredávanejšej meny, bol už trošku tvrdší oriešok. Má nasledovný vzorec:

Naj mena :=
CALCULATE (
VALUES ( Meny[Kod meny] );
TOPN (
1;
SUMMARIZE ( ‚Objednávky‘; Meny[Kod meny]; „Obrat za menu“; [Obrat] );
[Obrat za menu]; 0
)
)

Postup výpočtu je nasledovný:

1. Prvé dva kroky výpočtu sú rovnaké ako pri predchádzajúcom merítku – dáme si zoskupiť objednávky v aktuálnom kontexte/rozkliku podľa meny, vypočítame pre každú menu hodnotu obratu, a funkciou TOPN vyberieme riadok s najvyšším obratom
2. Ak by sme ale chceli použiť ďalšie kroky z predchádzajúceho výpočtu, tak narazíme na interné obmedzenie DAXu, a to, že funkcia MINX (a spolu s ňou aj všetky X-kové funkcie) nevedia vybrať hodnotu zo stĺpca, ktorý bol vytvorený funkciou SUMMARIZE. A problém číslo 2 je ten, že tieto funkcie vedia pracovať len s číslami… Použijeme teda fintu z tohto článku na výber hodnoty z riadku, zabalíme funkciu TOPN do vzorca CALCULATE(VALUES(stĺpec); tabuľka). Takto to už potom prejde 🙂

Výsledné merítko potom spravíme tak, že zlepíme predchádzajúce 2 merítka dokopy operátorom „&“, a zároveň naformátujeme prvé z nich ako číslo DAX-ovou funkciou FORMAT:

Obrat v najpredávanejšej mene :=
    IF (
        NOT ( ISBLANK ( [Obrat] ) );
        FORMAT ( [Obrat v naj mene]; „# ### ### ##0.00“ ) & “ „ & [Naj mena]
    )

Ako vidíte, je tam starý známy test funkciami NOT a ISBLANK tak, aby sa nám merítko zobrazovalo len vtedy, keď existuje v danej krajine nejaký obrat. Je to kvôli tomu, že v našich vzorových dátach máme aj krajiny, pre ktoré neexistuje obrat, a bez tohto testovania by takéto merítko spôsobilo zobrazenie aj takýchto krajín (niečo podobné je popísané aj v tomto článku).

Keď toto merítko pridáme do kontingenčky, bude výsledok vyzerať nasledovne:

Ak si to chcete skontrolovať, či to máte správne, tak si prihoďte do kontingenčky stĺpec Mena z tabuľky Objednávky, ak ho tam ešte nemáte. Pomocou toho si za pár sekúnd viete overiť, či to máte 100% správne. Lebo niekedy Vám DAX vypočíta aj to, čo nechcete, a dokým si to neskontrolujete krížovo nejakou inou metódou, tak neviete, či to nie je len nejaký náhodný blud. Zároveň uspokojíte aj svoju paranoju, a deň bude zas o niečo krajší 🙂

Nakoniec nezabudnite ešte skryť prvé 2 merítka z ponuky kontingenčky, kliknutím na ne pravým tlačítkom myši a vybratím „Skryť v klientských nástrojoch„. Tým po sebe upracete, a nebudete mať v ponuke kontingenčky na výber 2 merítka, ktoré samé o sebe nemajú z analytického hľadiska pre užívateľa (a v podstate ani pre Vás) žiadnu hodnotu. Lebo, ako sme si hovorili už v jednom z predchádzajúcich článkov, tak v ponuke kontingenčky by mali byť len tie veci, ktoré užívateľ naozaj potrebuje vidieť.

Tak, to je na dnes všetko, a dúfam, že Vám to pomohlo byť zasa o krok ďalej k svetlým analytickým zajtrajškom 🙂 Ak máte nejaký konkrétny problém v PowerPivote, ktorý potrebujete vyriešiť, tak píšte dole do komentárov. Po čase sa tu magicky vyskytne jeho riešenie 🙂