V predchádzajúcom článku sme si ukázali, ako viete vytvoriť základné merítka. Tu však celé využitie merítok ešte len začína. Takže si v tomto článku ukážeme, ako vieme používať filtre, a pomocou nich vypočítať pokročilejšie ukazovatele. Merítka totiž nemusia byť obmedzené len na jednoduché vzorce. Ako sme spomínali v predchádzajúcom článku, merítka majú prístup ku všetkým riadkom v dátovom modeli, a tým Vám poskytujú neobmedzenú flexibilitu vo výpočtoch. Ak teda viete, ako na to 🙂
Dnes si ukážeme, ako vieme používať filtre, a pomocou nich vypočítať ukazovateľ len za nejakú vybranú oblasť dát. Predstavte si napríklad, že potrebujete analyzovať obrat po rokoch, mesiacoch a dňoch, a potrebujete vedľa seba vidieť obrat za celú firmu, a obrat za USA – pretože USA je Váš najväčší trh a potrebujete vidieť tieto 2 veličiny vedľa seba. Ak by ste toto chceli spraviť klasickou kontingenčkou, museli by ste mať 2 kontingenčky, z ktorých by ste spájali výsledky do osobitnej tabuľky. A to je zbytočne prácne a neflexibilné, pretože ak budete chcieť pravidelne meniť USA napr. za Slovensko, tak Vás čaká “obľúbené” prerábanie reportov. Čo však nedokáže vypočítať kontingenčka, dokáže vypočítať PowerPivot ľavou zadnou.
Predtým ale než si povieme, ako vieme vypočítať obrat za USA, potrebujeme vedieť základy toho, ako funguje počítanie merítok. Dajme do kontingenčky napr. merítko Obrat, a do riadkov hierarchiu Rok-Mesiac-Deň, ktorá bude rozkliknutá len na úroveň rokov. Kontingenčka teraz spustí vzorec merítka Obrat pre každú bunku kontingenčky osobitne, t.j. pre každý rok zvlášť (na obrázku je názorne vyznačená bunka za rok 2002):
Každému z tých rokov prislúchajú nejaké konkrétne riadky v tabuľke Objednávky, a PowerPivot štandardne spočíta vzorec merítka Obrat za tieto riadky. Čiže PowerPivot spočíta vzorec SUM([Cena objednávky]) zo všetkých príslušných riadkov objednávok k danému roku (napr. na našom obrázku za rok 2002), a jeho výsledok zobrazí v príslušnej bunke kontingenčky.
Keby sme teraz rozklikli niektorý z tých rokov, rozklikne sa na príslušné mesiace k danému roku. Príslušná bunka s obratom pre daný mesiac bude teraz vidieť len na riadky v tabuľke Objednávky, ktoré prislúchajú danému mesiacu a roku. A z nich spočíta merítko Obrat. A keby sme teraz vymenili na riadkoch hierarchiu Rok-Mesiac-Deň napr. za stĺpec Produkt, tak každá bunka s Obratom v kontingenčke bude vidieť len na riadky Objednávok, ktoré prislúchajú danému produktu.
A tomu, na ktoré riadky vidí dané políčko kontingenčky, sa hovorí kontext výpočtu. A vy môžete tento kontext meniť rôznymi spôsobmi – filtrami, antifiltrami, dátumovými funkciami, funkciami Time Intelligence, a mnohými ďalšími. Máte na to totiž okolo stovky funkcií v jazyku DAX, ktoré slúžia presne na tento účel. A pomocou nich ide tento kontext naťahovať, zmenšovať, posúvať, premiestňovať, a pod.. Tajomstvo pokročilej analytiky potom spočíva vo vhodnom nakombinovaní týchto funkcií tak, aby to spočítalo to, čo chcete aby to spočítalo.
Ideme si teda ukázať, ako používať filtre, t.j. ako zmenšiť kontext výpočtu.
Kontext výpočtu viete zmeniť pomocou funkcie CALCULATE, ktorá vypočíta zadaný vzorec v zadanom kontexte. Ide vo svojej podstate o veľmi jednoduchú funkciu, ale zároveň o jednu z najsilnejších a najschopnejších funkcií v celom DAXe. Jej syntax je takáto:
CALCULATE(vzorec; filter1; …filter2; …filter3; …)
kde vzorec je vzorec alebo merítko v jazyku DAX, ktoré chcete vypočítať, a filter1 je niektorá z funkcií, ktorá špecifikuje, resp. mení kontext výpočtu. Čiže tým poviete, za akú oblasť dát chcete zadaný vzorec vypočítať. Vo funkcii som uviedol aj filter2 a filter3 a tri bodky za nimi. To sú dodatočné filtre, ktoré tam voliteľne môžete zadať tiež. Táto funkcia má totiž neobmedzený počet parametrov, a teda môžete v nej kombinovať ľubovoľný počet filtrov, resp. kontextových funkcií súčasne. To si ukážeme o pár odsekov nižšie.
Takže teraz naspäť k príkladu, ako vypočítať obrat za USA. Spravíme to tak, že pomocou funkcie CALCULATE dáme vypočítať merítko Obrat len za tie príslušné riadky v tabuľke Regióny, ktoré spĺňajú podmienku “krajina = USA”. Vzorec na jeho výpočet bude teda takýto:
Obrat za USA:=CALCULATE( [Obrat];
FILTER(‘Regióny’; ‘Regióny'[SalesTerritoryCountry]=”United States”))
Toto merítko vytvoríme v tabuľke Objednávky, a po jeho zadaní a naformátovaní ako Mena to bude vyzerať nasledovne:
V PowerPivote nám to ukáže o niečo menšie číslo ako pri merítku Obrat, čo predbežne znamená, že by to mohlo fungovať dobre. Je to kvôli tomu, že bunka s definíciou merítka v PowerPivote ukazuje merítko vypočítané pre kontext, resp. na úrovni celého dátového modelu. To znamená, že Obrat za USA, ktorý tam vidíme, je vypočítaný ako obrat za USA za celú históriu firmy.
Čo sa týka syntaxe funkcie FILTER, tak tá je nasledovná:
FILTER(filtrovacia_tabuľka; filtrovacia_podmienka)
kde filtrovacia_tabuľka je tabuľka, ktorú ideme filtrovať, a filtrovacia_podmienka je DAXová podmienka, ktorá hovorí o tom, ktoré riadky majú vo filtrovacej tabuľke ostať. V spojení s funkciou CALCULATE tým povieme, ktoré riadky majú v kontexte zostať, resp. že kontext výpočtu sa zužuje na príslušné riadky v dátovom modeli (príslušnosť riadkov sa zistí pomocou vzťahov v modeli). V našom prípade to znamená, že kontext výpočtu sa zmenší len na tie riadky z tabuľky Objednávky, ktoré prislúchajú riadkom vo filtri – čiže riadkom krajiny USA. Z týchto riadkov spočítame Obrat (čo je v našom príklade prvý parameter funkcie CALCULATE), a dostaneme tým obrat za USA 🙂
Keď sa teraz presunieme do kontingenčky, a pridáme do nej toto naše nové merítko “Obrat za USA” z tabuľky Objednávky do oblasti hodnôt, bude to vyzerať takto:
Pre overenie teraz vyhodíme z riadkov hierarchiu Rok-Mesiac-Deň, a dáme tam stĺpec SalesTerritoryCountry z tabuľky Regióny:
V kontingenčke teraz vidíte, že merítko “Obrat za USA” sa počíta naozaj len za USA, a pre ostatné krajiny je prázdne. Zároveň hodnota oboch merítok pre krajinu USA je rovnaká, čiže výsledok nám prešiel skúškou správnosti.
A takto si môžete vypočítať hocikoľko ďalších merítok, ktoré by ste pomocou klasických kontingenčiek museli lepiť z viacerých kontingenčiek do jedného reportu. Tu to máte pekne pokope v jedinej kontingenčke, a okrem iného Vám to ušetrí kopec práce. Jedine si dajte pozor na to, aby ste nezabudli vždy na otestovanie správnosti merítka, t.j. či počíta správne výsledky, napr. tak ako sme to spravili vyššie. Pretože sa kľudne môže stať, že napíšete vzorec na 2 strany, ktorý bude počítať “niečo”, a to niečo nemusí súhlasiť s Vašimi predstavami 🙂 Preto si vždy spravte aspoň 1-2 skúšky správnosti v kontingenčke.
Nakoniec si ešte ukážeme, ako vieme kombinovať viacero kontextových filtrov vo funkcii CALCULATE. Chceli by sme napr. vidieť, aký bol predaj červených produktov v USA, t.j. aký bol obrat za červené produkty v krajine USA. To vieme spraviť ako ďalšie merítko:
Obrat č.p. za USA:=CALCULATE( [Obrat];
FILTER(‘Regióny’; ‘Regióny'[SalesTerritoryCountry]=”United States”);
FILTER(‘Produkty’; Produkty[Color]=”Red”))
Keď merítko zadáme do PowerPivotu vedľa ostatných merítok v tabuľke Objednávky, a naformátujeme ho ako Mena, tak výsledok bude vyzerať v PowerPivote takto:
…a po pridaní tohto merítka do kontingenčky, bude kontingenčka vyzerať takto:
Následná skúška správnosti v kontingenčke – pridanie stĺpca Color z tabuľky Produkty do oblasti riadkov kontingenčky – bude vyzerať takto:
Vidíte, že merítko “Obrat č.p. za USA” sa počíta len vtedy, keď je v riadkoch krajina USA a vybraná červená farba produktu. Pre iné kombinácie krajín a farieb sa nepočíta. A tak to má byť.
Takto teda vieme zmeniť kontext počítania merítka, a vypočítať merítko pomocou filtrov. A keďže je toto ešte len začiatok analytiky, budeme tým pokračovať v ďalšom článku, kde si povieme o filtroch ešte viac. Pretože ešte sme len za úplnom začiatku cesty 🙂
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.
Super napisane.