V predchádzajúcom článku sme si ukázali, ako vieme zmeniť kontext počítania merítka, a vypočítať merítko pomocou filtrov. V tomto článku budeme v tejto téme pokračovať, a povieme si o tzv. antifiltroch.

Antifiltre patria medzi filtrovacie funkcie jazyka DAX, a názov väčšiny týchto funkcií začína na “ALL”. Ich účel je presne opačný ako pri filtroch – zväčšujú totiž kontext počítania merítka. Používajú sa najmä vtedy, keď chcete porovnať nejakú veličinu voči širšej alebo väčšej množine z tých istých údajov. Napríklad ak chcete porovnať obrat v danom roku voči celkovému historickému obratu, aby ste vedeli, koľkými percentami sa podieľa obrat za daný rok (alebo mesiac, deň, krajinu, produkt, …) na obrate za celú históriu. Alebo koľkými percentami sa podieľa predaj danej farby produktu na predajoch v jeho kategórii produktov. A dobrá správa je to, že môžete vzájomne kombinovať nielen viacero antifiltrov, ale aj antifiltre s filtrami. Opäť, PowerPivot vo väčšine prípadov neprotestuje, a vypočíta, čo mu zadáte 🙂

Ideme si teda ukázať na príklade, ako vypočítať celkový obrat firmy, aby sme potom na jeho základe vedeli spraviť percentuálne porovnanie obratu za aktuálne rozkliknutú kategóriu (rok, mesiac, kategória, krajina, …) voči celkovému obratu.

Prejdite do okna PowerPivotu, do tabuľky Objednávky, vytvorte nasledujúce merítko, a nastavte mu opäť formátovanie ako Mena:

Celkový obrat := CALCULATE([Obrat]; ALL(‘Objednávky’))

Ako vidíte, znova sme použili funkciu CALCULATE, pomocou ktorej sme povedali, že chceme vypočítať merítko Obrat v zmenenom kontexte. Tentokrát sme ale použili funkciu ALL, a do parametra sme dali názov tabuľky Objednávky, na ktorej celú veľkosť sa má rozšíriť kontext. Táto funkcia spôsobí, že kontext výpočtu merítka sa natiahne tak, aby merítko videlo na celú tabuľku, t.j. v našom prípade na celú tabuľku Objednávky. Merítko Obrat je definované ako:

Obrat := SUM([Cena objednávky])

Čo znamená, že keď funkciou CALCULATE zväčšíme kontext počítania tohto vzorca na celú tabuľku Objednávky, tak nám to spočíta súčet cien všetkých objednávok, čo je vlastne celkový obrat firmy.

Prejdite naspäť do kontingenčky, a vyskladajte ju takto:

1. do oblasti riadkov dajte hierarchiu Rok-Mesiac-Deň z tabuľky Čas,
2. do oblasti hodnôt dajte merítka “Obrat” a “Celkový obrat”.

Výsledok bude vyzerať takto:

Ako vidíte, celkový obrat vždy ukazuje sumu 26 miliónov, čo je súčet cien všetkých objednávok, nezávisle od toho, čím sa ho snažíme rozrezať na riadkoch. A to je dobre, pretože sme chceli vypočítať pomer “Obratu” k “Celkovému obratu”, a na to sme naprv potrebovali tento “Celkový obrat”.

Prejdeme teda naspäť do PowerPivotu, vytvoríme tam ďalšie merítko “% obratu”, a naformátujeme ho ako Percentá:

% obratu := [Obrat] / [Celkový obrat]

Percento obratu nám v PowerPivote ukazuje 100%, a je to opäť len kvôli tomu, že kontext počítania merítka v tej bunke, ktorá ho zobrazuje v PowerPivote, je vždy celá tabuľka. Preto tie čísla treba brať len ako orientačné, a dôležité je, čo nám zobrazí kontingenčka.

Ideme teda naspäť do kontingenčky, a pridáme do oblasti riadkov toto naše nové merítko “% obratu”:

Tu vidíme, že nám to percento obratu vypočítalo správne. Môžete sa s tým teraz pohrať a rozkliknúť si to na úroveň mesiacov, dní, prípadne dať do riadkov úplne iné stĺpce, a ono to stále bude fungovať.

Ukážeme si ale ešte ďalšiu vec, ktorá sa Vám môže vyskytnúť, keď máte v niektorej z číselníkových tabuliek riadky, ku ktorým neexistujú žiadne objednávky.

Upravte si kontingenčku tak, aby:

1. v oblasti hodnôt boli merítka “Obrat”, “Celkový obrat” a “% obratu”,
2. v oblasti riadkov bol stĺpec SalesTerritoryCounty z tabuľky Regióny.

Všimnite si, že medzi krajinami sa nám teraz začala objavovať krajina “NA” (skratka z anglického “Not Available”), pre ktorú neexistuje žiaden obrat, a ktorú sme v predchádzajúcich príkladoch nevideli. Keď ale odstránite merítko “Celkový obrat” z kontingenčky, tak tá krajina zo zoznamu zmizne:

A keď tam to merítko znova pridáme, tak sa tam tá krajina znova objaví:

Čím to je? V podstate tým, ako funguje kontingenčka, a zároveň tým, ako funguje výpočet merítok. Prvý dôvod je ten, že keď používate nejaký číselník hodnôt v kontingenčke na rezanie údajov (v našom prípade používame zoznam krajín na rezanie 3 merítok), tak kontingenčka zobrazí len tie hodnoty z číselníka (v našom prípade krajiny), pre ktoré je celý príslušný riadok s merítkami neprázdny. Ak je prázdny, tak celý takýto riadok kontingenčka skryje, lebo pre užívateľa nemá analytický význam. Toto správanie sa dá zmeniť v nastaveniach kontingenčky, ale zvyčajne nám to takto vyhovuje. A všimnite si, že keď máme v kontingenčke merítko “Celkový obrat”, tak to sa vypočíta pre každú krajinu z číselníka, a dokonca aj pre krajinu “NA”, pre ktorú neexistujú žiadne obraty. A keďže je takýto riadok v kontingenčke neprázdny, tak ho kontingenčka zobrazí. Keď ale to merítko odtiaľ odstránime, tak riadok pre krajinu “NA” zostane prázdny, a preto ho kontingenčka skryje.

Druhý dôvod je ten, že merítka sa počítajú pre všetky kombinácie hodnôt stĺpcov, ktoré sú použité v kontingenčke na jej riadkoch alebo stĺpcoch. Čiže v našom prípade pre všetky krajiny z tabuľky Regióny, ktoré sme použili na riadkoch v kontingenčke. Pre krajinu “NA” neexistujú žiadne riadky, a teda aj merítko Obrat vráti prázdnu hodnotu ako výsledok. Avšak merítko “Celkový obrat” mení kontext výpočtu na celú tabuľku Objednávky, a preto vždy vidí na všetky riadky tej tabuľky, a vypočíta celkový obrat aj za krajinu, pre ktorú neexistujú žiadne predaje.

S takýmito situáciami však vždy musíte rátať, keď rozširujete kontext výpočtu. A preto si ideme ukázať, ako to napraviť.

Vytvoríme si pomocné merítko “Celkový obrat neprázdny”, ktoré bude počítať celkový obrat len vtedy, keď merítko Obrat bude obsahovať nejakú hodnotu. To vieme elegantne otestovať funkciou ISBLANK, ktorá vracia hodnotu True (resp. áno), ak je zadaná hodnota prázdna, a hodnotu False (resp. nie), ak je zadaná hodnota neprázdna. Vzorec pre toto merítko bude teda takýto (nezabudnite mu dať formát Mena):

Celkový obrat neprázdny:=IF(ISBLANK([Obrat]); BLANK(); [Celkový obrat])

Vo vzorci sme otestovali, či je v danom kontexte merítko Obrat prázdne alebo nie, funkciou ISBLANK. Ak áno, vrátime ako výsledok prázdnu hodnotu pomocou funkcie BLANK, a ak nie, vrátime hodnotu merítka “Celkový obrat”. To nám zabezpečí, že toto merítko bude vracať prázdnu hodnotu, ak je prázdny aj Obrat. Následne zmeníme vzorec aj pre merítko “% obratu” tak, že nahradíme v jeho vzorci merítko “Celkový obrat” merítkom “Celkový obrat neprázdny”:

% obratu := [Obrat] / [Celkový obrat neprázdny]

Keď sa vrátime naspäť do kontingenčky, a v oblasti hodnôt nahradíme merítko “Celkový obrat” merítkom “Celkový obrat neprázdny”, tak krajina “NA” sa už nebude zobrazovať v zozname:

Posledným krokom je už len po sebe upratať, a skryť užívateľovi nepotrebné merítka. Merítka “Celkový obrat” a “Celkový obrat neprázdny” sme totiž použili ako pomocné merítka, ktoré pre užívateľa nemajú zrovna zmysel, a preto ich skryjeme z ponuky kontingenčky. Tak zabezpečíme, aby ponuka kontingenčky bola vždy čo najkratšia, resp. aby vždy obsahovala len zmysluplné voľby pre užívateľa.

Takto teda vieme používať antifiltre, a riešiť problémy s nimi spojené. Tu sa ich využitie však ani zďaleka nekončí. A preto si v ďalšom článku ukážeme, čo nimi ešte ide vyriešiť 🙂