V predchádzajúcom článku sme si ukázali, ako vieme používať základný antifilter ALL. V tomto článku v tom budeme pokračovať, a ukážeme si jeho ďalšie využitie.

Funkcia ALL totižto funguje v dvoch režimoch:

1. v režime ALL(tabuľka) – v tomto prípade vráti celú zadanú tabuľku, resp. pri použití s funkciou CALCULATE rozšíri kontext výpočtu na celú túto tabuľku,
2. v režime ALL(stĺpec1, …) – odstráni z aktuálneho kontextu filter nad zadaným stĺpcom, resp. zadanými stĺpcami. Funkcia má nekonečný počet parametrov, takže môžete do nej zadať aj viacero stĺpcov.

A práve tomu druhému režimu sa budeme teraz venovať. Na nasledujúci príklad použijeme náš vzorový súbor, doplnený o tabuľky ProductCategory a ProductSubcategory, ktoré sme premenovali na Kategórie a Podkategórie a prepojili medzi sebou a potom s tabuľkou Produkty. Tento súbor si môžete stiahnuť na tomto odkaze.

Najprv si naskladáme nasledovnú kontingenčku – obrat po kategóriách produktov a krajinách:

1. do oblasti hodnôt dáme Obrat z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpec ProductCategoryName z tabuľky Kategórie.
3. do oblasti stĺpcov dáme stĺpec SalesTerritoryCountry z tabuľky Regióny,

Výsledok bude vyzerať takto:

A teraz by sme chceli vedieť, koľkými percentami sa podieľal obrat danej kategórie produktov na celkovom obrate za danú krajinu. Čiže nie na obrate za celú firmu, ale len v danej krajine. To budeme vedieť spraviť tak, že si vypočítame celkový obrat za danú krajinu. A potom aktuálne počítaný obrat predelíme týmto celkovým obratom, aby sme získali percentuálny podiel, ktorý nás zaujíma.

Obrat za krajinu spočítame nasledovným merítkom v tabuľke Objednávky:

Obrat za krajinu := CALCULATE([Obrat]; ALL(‚Kategórie'[ProductCategoryName]))

V tomto vzorci sme tiež použili funkciu ALL, rovnako ako v predchádzajúcom článku. Rozdiel je ale v tom, že teraz sme použili túto funkciu v 2. režime, t.j. pomocou zadania stĺpca do parametra, ktorý spôsobí to, že v aktuálnom kontexte sa odstráni filter nad zadaným stĺpcom, ak nejaký existuje.

A čo sú to vlastne tie filtre?

Každé políčko v kontingenčke má svoj kontext výpočtu. A práve ten je štandardne definovaný filtrami. Pod filtre spadajú nasledovné veci:

1. polia kontingenčky v oblasti riadkov a stĺpcov,
2. filtre kontingenčky,
3. slicery, resp. rýchle filtre.

Všetky tieto veci súčasne definujú kontext výpočtu políčka, t.j. ohraničujú riadky v dátovom modeli, na ktoré políčko „vidí“, keď ho PowerPivot počíta. A jednou z možností, ako tento kontext meniť, je odstraňovať filtre z neho, napr. funkciou ALL, či inými antifiltrami. Pozrite si napr. zvýraznené políčko na nasledovnom obrázku:

Je na ňom vypočítané políčko, v ktorom sa počíta merítko Obrat za krajinu Canada a za produktovú kategóriu Bikes. A práve tá krajina a tá produktová kategória sú filtrami pre toto políčko, a definujú jeho kontext výpočtu – čiže riadky, na ktoré toto políčko vidí, keď ho PowerPivot počíta. Takže toto políčko má 2 filtre – krajinu Canada a kategóriu Bikes – a preto spočítava obrat za danú krajinu a danú kategóriu.

A keď chceme vypočítať obrat za krajinu, stačí v kontexte výpočtu políčka zrušiť filter nad kategóriou. Tým pádom toto políčko uvidí na všetky kategórie, ale filter nad danou krajinou zostane. Čiže políčko uvidí na riadky, ktoré prislúchajú všetkým kategóriám v danej krajine. A to je presne to, čo sme chceli dosiahnuť – vypočítať celkový obrat za krajinu, aby sme pomocou neho vedeli vypočítať percentuálny podiel obratu za krajinu v danej kategórii.

Keď pridáme merítko „Obrat za krajinu„ do kontingenčky, bude to vyzerať takto:

Vídíte, že v rámci jednej krajiny (na obrázku vyznačená Australia), je „Celkový obrat za krajinu“ pri každej kategórii rovnaký, a zároveň sa rovná súčtu v stĺpci Obrat za všetky kategórie v danej krajine.

Upozornenie pre Power BI Desktop: ak napíšete presne tento istý vzorec v Power BI Desktope, tak niekedy Vám to bude fungovať, a niekedy nie. Viac informácií nájdete v tomto článku.

Je tam ešte ale rovnaká drobnosť, ktorú musíme opraviť tak isto, ako pri použití hociktorého iného antifiltra – a to, že sa toto merítko počíta pre všetky kombinácie krajín a kategórií, a nie len pre tie, kde existuje nejaký obrat. Opravíme to teda doplnením ďalšieho merítka a využítím funkcie ISBLANK, tak ako v predchádzajúcom článku:

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

Po výmene merítka „Obrat za krajinu“ za merítko „Obrat za krajinu neprázdny“ v kontingenčke to bude vyzerať takto:

Posledným krokom bude vytvorenie merítka, ktoré sme pôvodne chceli vypočítať (naformátujeme ho ako Percentá):

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

Keď ho pridáme do kontingenčky, výsledok bude vyzerať nasledovne:

Naše riešenie 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. To, ako ošetríme (nielen) toto, si ukážeme v ďalšom článku 🙂