Naše riešenie z prechádzajúceho článku teraz počíta to čo sme chceli, má však stále jednu drobnú chybičku krásy – funguje totiž len vtedy, keď užívateľ nemení oblasť riadkov alebo stĺpcov kontingenčky.

Keď teraz napr. v riadkoch vymeníme stĺpec ProductCategoryName z tabuľky Kategórie za stĺpec Rok z tabuľky Čas, bude výsledok vyzerať nasledovne:

Všimnite si, že kontingenčka zobrazuje teraz 100% pre merítko “% obratu za krajinu” pre každý rok, čo je nesprávne. Je to kvôli tomu, že naše merítko “Obrat za krajinu” odstraňuje pri výpočte daného políčka v kontingenčke filter nad kategóriou, ktorá v tejto kontingenčke teraz nie je použitá. Takže vo výsledku neodstraňuje nič, a ani nám nevyhlási o tom chybu. Tak ale programátori naprogramovali túto funkciu, takže z pohľadu PowerPivotu to funguje správne.

Na to, aby to fungovalo ale správne podľa nás, by sme ale potrebovali odstrániť všetky filtre okrem filtra nad krajinou. Čiže ak nad daným políčkom existujú nejaké filtre, chceli by sme ich všetky odstrániť, s výnimkou filtra nad krajinou (ak je nejaký použitý), ktorý chceme ponechať. A presne na tento účel máme funkciu ALLEXCEPT, ktorá z kontextu odstráni všetky filtre, okrem filtra nad zadaným stĺpcom. Vytvoríme si teda ďalšie merítko, ktoré bude počítať celkový obrat za krajinu, tentokrát už vždy správne:

Obrat za krajinu2 := CALCULATE([Obrat]; ALLEXCEPT(‘Objednávky’; ‘Regióny'[SalesTerritoryCountry]))

Všimnite si, že funkcia ALLEXCEPT vyžaduje minimálne 2 parametre:

1. tabuľku, nad ktorou chcete zrušiť filtre. Sem zvyčajne zadávate hlavnú tabuľku v dátovom modeli, resp. tú tabuľku, z ktorej počítate dané merítko,
2. stĺpec, nad ktorým sa majú ponechať filtre.

Takéto merítko už bude správne počítať obrat za krajinu.

Vytvoríme si teda ešte 2 merítka:

Obrat za krajinu neprázdny2 := IF(ISBLANK([Obrat]); BLANK(); [Obrat za krajinu2])

…ktoré bude ošetrenou verziou merítka “Obrat za krajinu2”, tak ako v predchádzajúcom prípade. A potom vytvoríme ešte merítko percentuálneho podielu (a dáme ho naformátovať Percentá):

% obratu za krajinu2 := [Obrat] / [Obrat za krajinu neprázdny2]

Keď teraz v kontingenčke nahradíme predchádzajúce 2 merítka – “Obrat za krajinu neprázdny” a “% obratu za krajinu” – týmito dvoma poslednými merítkami, výsledok bude vyzerať nasledovne:

Tentoraz je už výsledok správny, nezávisle od toho, čo dáme do riadkov kontingenčky.

Je tu ale ešte posledná “drobnosť”, a tentokrát naozaj posledná :), na ktorú väčšina powerpivoťákov nemyslí, keď vytvárajú model pre niekoho iného. A to je to, že niektorí kreatívnejší užívatelia raz za čas budú chcieť použiť takéto merítko – “% obratu za krajinu” – aj vtedy, keď nebudú mať žiadnu krajinu na riadkoch, ani v stĺpcoch kontingenčky. Logicky to síce nedáva zmysel, prečo by to mali chcieť. Ale keďže Excel im v tom nezabráni, budú to z času na čas používať, a potom budú mať “blbé otázky”, že “prečo im to počíta zle”:

Tým šikovnejším to po pár sekundách rozmýšľania dôjde aj samým, ale väčšine užívateľov to bohužiaľ nedôjde, resp. nebudú chcieť nad tým vôbec rozmýšľať. Preto aj my potrebujeme spraviť takéto merítka “blbuvzdorné” a ošetriť to tak, že keď medzi zadanými filtrami v kontexte výpočtu políčka nie je použitá krajina (stĺpec SalesTerritoryCountry z tabuľky Produkty), tak nech sa takéto merítko nepočíta. Resp., povedané technicky správne, nech vracia prázdnu hodnotu.

Na tento účel nám výborne poslúži funkcia ISFILTERED, ktorá nám vracia hodnotu True (logické áno), keď je kontext výpočtu políčka filtrovaný zadaným stĺpcom. A ak je aktuálny kontext filtrovaný krajinou, tak vrátime “Obrat za krajinu neprázdny2”, inak vrátime prázdnu hodnotu. Spravíme si teda nasledovné merítko:

Obrat za krajinu neprázdny3 := IF(ISFILTERED(‘Regióny'[SalesTerritoryCountry]); [Obrat za krajinu neprázdny2]; BLANK())

Všimnite si, akú náhľadovú hodnotu nám zobrazí políčko s definíciou merítka v PowerPivote:

Zobrazí prázdnu hodnotu, pretože, ako sme hovorili, kontext tohto políčka je vždy celý dátový model, a teda v tomto konkrétnom políčku nie sú aplikované žiadne filtre. A keďže naša podmienka v merítku hovorí o tom, že keď nie je aplikovaný filter nad krajinou, tak má vrátiť prázdnu hodnotu, tak aj merítko vracia prázdnu hodnotu.

Zadefinujeme si teda ešte posledné merítko, ktoré nám tentokrát už naozaj bude počítať správne percentá: 🙂

% obratu za krajinu3 := DIVIDE([Obrat]; [Obrat za krajinu neprázdny3]) (naformátujte ho ako Percentá)

Všimnite si použitie funkcie DIVIDE – táto predelí prvý parameter druhým parametrom, a ak sa budete pokúšať deliť nulou alebo prázdnou hodnotou, tak vráti prázdnu hodnotu. Takto nemusíme zbytočne ošetrovať všetky takéto situácie cez podmienky funkcie IF.

Keď prejdeme do kontingenčky, a nahradíme merítka “Obrat za krajinu neprázdny2” a “% obratu za krajinu2” týmito dvoma poslednými merítkami, výsledok bude vyzerať nasledovne:

Takto to vyzeralo, aj keď sme tam mali merítka v predchádzajúcom prípade. Rozdiel ale teraz bude v tom, keď odstránime stĺpec SalesTerritoryCountry z oblasti stĺpcov kontingenčky:

Vidíte, že teraz sa nám pre tieto 2 merítka správne zobrazia prázdne hodnoty, pretože ak v kontingenčke nie sú použité krajiny, tak počítanie merítok na nich závislých nemá zmysel.

A takto je dobré ošetriť každé jedno pokročilejšie merítko, ktoré používa niektorý z antifiltrov, resp. sa spolieha na niektoré (ne-)zadané filtre v kontingenčke. Výhodou toho bude, že takto vytvoríte naozaj blbuvzdorné riešenie, a takisto Vás užívatelia nebudú otravovať tými istými otázkami stále dokola. Toto je veľmi užitočná rada, ktorú Vám vie dať iba skúsený profesionál, ktorý toto verne pozná z praxe 🙂 a ktorá Vám ušetrí obrovské množstvo času, práce a nedorozumení s kolegami. A aj v tomto spočíva skrytá hodnota našich kurzov PowerPivotu. Pretože takéto rady Vás garantovane nenaučí nikto iný. Teda, ak si to práve včera neprečítal na našom blogu 🙂 Najcennejšie rady si ale vždy nechávame pre účastníkov našich kurzov, takže ak chcete vytvoriť a používať naozaj profesionálne riešenie v nástroji, ktorý na prvý pohľad vyzerá ako jednoduché klikátko, a ušetriť si tým kopec starostí a trápenia, prihláste sa na niektorý z našich kurzov. Určite sa Vám to vyplatí.

V ďalšom článku sa bližšie pozrieme na linkované tabuľky.

Michal Chmelár

Autor, tréner a expert na PowerPivot, Power BI a jazyk DAX. Založil som tento web, aby som pomohol dostať PowerPivot a Power BI do širšieho povedomia, a aby som ľuďom ukázal, že aj komplexné analytické problémy idú riešiť jednoducho. Po nociach vzývam Majstra Yodu a tajne plánujem ovládnutie vesmíru.