Jeden z najčastejších problémov začiatočníkov v jazyku DAX je ten, že nevedia, prečo im nejdú porovnávať stĺpce v merítkach, keď píšu vzorce. A tým pádom nevedia potom vypočítať ani základné ukazovatele, kde potrebujú porovnať údaje z dvoch stĺpcov, ktoré sú zvyčajne (ale nie nevyhnutne) z dvoch rôznych tabuliek. Preto sa teraz pozrieme na to, prečo to nejde, a najmä na to, ako to určite pôjde.
Ideme porovnávať aktuálny stav voči plánu
Na demonštráciu opäť využijeme náš vzorový súbor PowerPivotu, resp. Power BI. Vytvoríme si kontingenčku, kde:
- v oblasti hodnôt bude stĺpec „Cena objednávky“ z tabuľky Objednávky,
- v oblasti riadkov bude stĺpec CalendarYear z tabuľky Čas.
Výsledná kontingenčka bude vyzerať takto:
Teraz si do dátového modelu pridáme novú tabuľku, kde budeme mať plán na 4 roky – na tie isté, aké máme v dátovom modeli. Tabuľku nazveme „Plánovacia tabuľka“, a bude obsahovať takéto údaje:
Potom medzi sebou nepriamo prepojíme tabuľky „Objednávky“ a „Plánovacia tabuľka“, cez spoločnú číselníkovú tabuľku „Čas“. Pretože dátové tabuľky (tzn. tie, v ktorých máte čísla, ktoré idete analyzovať), sa nemajú prepájať na priamo, ale vždy cez spoločný číselník. Čo je v našom prípade tabuľka Čas.
Prepojíme teda tabuľku „Plánovacia tabuľka“ a stĺpec „Rok“ na tabuľku „Čas“ a stĺpec „CalendarYear“, obojsmerným prepojením:
A môžeme ísť veselo analyzovať
V kontingenčke už máme zosumovaný stĺpec „Cena objednávky“, čo nám zobrazuje obrat pre všetky roky. A teraz by sme ho chceli porovnať voči plánovanému obratu, čo je stĺpec v tabuľke „Plánovacia tabuľka“. Pridáme teda do kontingenčky stĺpec „Plánovaný obrat“ z tabuľky „Plánovacia tabuľka“:
Ako vidíte, tak sú vedľa seba zobrazené predaje vs. plán. A teraz by sme chceli vytvoriť merítko „% plnenia plánu“, aby sme vedeli vypočítať, na koľko percent sa podarilo splniť plán.
Skúsime vytvoriť teda takéto merítko:
Po odklepnutí vzorca dostaneme klasickú chybovú hlášku:
Chybová hláška je takáto: „Nie je možné určiť jednu hodnotu pre stĺpec Cena objednávky v tabuľke Objednávky. K tomuto môže dôjsť, keď vzorec mierky odkazuje bez určenia agregácie (napr. min, max, count alebo sum) na získanie jediného výsledku pre stĺpec, ktorý obsahuje veľa hodnôt.“
Chybová hláška hovorí o presnej príčine, ale nie každému to hneď dôjde. Z časti kvôli nie príliš dobrému prekladu do slovenčiny, ale najmä kvôli tomu, ako to funguje na pozadí.
Prečo nejdú porovnávať stĺpce v merítkach
Problém je v tom, že sme vo vzorci pre merítko použili priamo stĺpce. Merítko totiž vždy musí vracať nejakú hodnotu – či už číselnú, dátumovú, textovú, logickú a pod. – ale vždy je to hodnota. Naproti tomu, keď v merítku uvediete odkaz na stĺpec, tak PowerPivot ani Power BI nevedia, či v tom stĺpci v okamihu vyhodnocovania vzorca bude jedna hodnota, alebo viacero hodnôt. A keďže DAX je univerzálny jazyk, tak predpokladá, že keď ste uviedli vo vzorci stĺpec, tak v tom stĺpci môže byť buď žiadna hodnota, 1 hodnota, alebo viac hodnôt. A zároveň predpokladá najhorší scenár (aby to vždy fungovalo), a berie to tak, že tam vo všeobecnosti môže byť viacero hodnôt. Aj keď je tam v okamihu vyhodnocovania vzorca len jedna hodnota. On to však musí brať všeobecne – ako keby tam bolo viacero hodnôt.
V našom prípade si to vysvetlíme povedzme na tom, ako sa vyhodnocuje celý riadok v kontingenčke, napr. pre rok 2002. Stĺpec kontingenčky „Cena objednávky“ obsahuje zosumované hodnoty z tabuľky Objednávky a jej stĺpca „Cena objednávky“. Tá suma je vypočítaná zosumovaním niekoľkých tisíc hodnôt v tom pôvodnom stĺpci. A stĺpec kontingenčky „Plánovaný obrat“ obsahuje zosumované hodnoty z tabuľky „Plánovacia tabuľka“ zo stĺpca „Plánovaný obrat“. A táto suma je vypočítaná zosumovaním jednej hodnoty z toho pôvodného stĺpca. Ale vo všeobecnosti aj táto suma môže byť vypočítaná z viacerých hodnôt, napr. keď máte plánovaciu tabuľku na úrovni štvrťrokov, alebo rozdelenú ešte podľa krajín, oddelení a pod..
Vo vzorci pre merítko „% plnenia plánu“ sa snažíte predeliť tie pôvodné 2 stĺpce. Nedelíte však to, čo vidíte vypočítané v tej kontingenčke, ale delíte medzi sebou tie 2 stĺpce. Pretože merítka vždy vidia na podkladové dáta – čiže v tomto prípade na všetky príslušné hodnoty v oboch použitých stĺpcoch za rok 2002. A teda keď vo vzorci píšete, že „vypočítaj podiel stĺpec1 / stĺpec2“, tak mu hovoríte, že má predeliť tie 2 stĺpce medzi sebou. Problém však je v tom, že v tom prvom stĺpci („Cena objednávky“) je v tomto našom prípade niekoľko tisíc hodnôt, a v tom druhom stĺpci („Plánovaný obrat“) je v tomto prípade 1 hodnota. Prípadne v realite viacero hodnôt. A keď sa snažíte v akomkoľvek analytickom jazyku predeliť 2 veci, tak podiel je definovaný ako „hodnota1 / hodnota2“. Ak tam však namiesto hodnôt dáte stĺpce, tak ste v skutočnosti zapísali niečo ako „veľaHodnôt1 / veľaHodnôt2“. A jazyk DAX sa z toho nevie vysomáriť, pretože na oboch stranách podielu očakáva 1 hodnotu, a dostal ich viacero – a teda nevie, ktorú z tých viacero hodnôt má zobrať.
Riešenie, ako porovnávať stĺpce v merítkach
Riešením je teda z tých viacero hodnôt spraviť 1 hodnotu – či už zosumovaním, vybratím 1 hodnoty, alebo nejakým iným spôsobom. A najľahšie z tých spôsobov je tie viaceré hodnoty zosumovať. Pretože zosumovaním X hodnôt vždy vznikne 1 hodnota. A s tým DAX nebude mať už problém. To spravíte najjednoduchšie tak, že daný stĺpec – ktorý ako už viete reprezentuje pri použití v merítkach vždy viacero hodnôt – jednoducho zabalíte do nejakej agregačnej funkcie. Najčastejšie do funkcie SUM.
Náš vzorec teda rozchodíme tak, že oba stĺpce v ňom zabalíme osobitne do funkcie SUM:
% plnenia plánu := SUM(‚Objednávky'[Cena objednávky]) / SUM(‚Plánovacia tabuľka'[Plánovaný obrat])
Po dosadení do kontingenčky, a naformátovaní merítka ako percentá, už dostaneme presne to, čo sme chceli:
Prípadne, ak by vám výpočet nezbehol – napríklad ak máte niekde v pláne nuly alebo prázdne hodnoty – tak použite funkciu DIVIDE na bezpečné delenie nulou:
% plnenia plánu := DIVIDE ( SUM(‚Objednávky'[Cena objednávky]) ; SUM(‚Plánovacia tabuľka'[Plánovaný obrat]) )
Ide to ešte o trochu jednoduchšie
V súvislosti s týmto si zapamätajte jedno veľmi zjednodušené, o to však praktickejšie pravidlo pre začiatočníkov: „Ak porovnávate v merítkach údaje z 2 rôznych tabuliek alebo stĺpcov, tak si vždy vytvorte osobitné merítko pre každý porovnávaný stĺpec, kde ten stĺpec zosumujete. A potom v ďalšom merítku vykonajte výpočet porovnania.“
Čiže ak chcete porovnávať stĺpce v merítkach, tak si pre každý stĺpec vytvorte osobitné merítko. A tie merítka potom porovnávajte v treťom merítku.
V našom prípade máme už v tabuľke Objednávky vytvorené merítko Obrat, ktoré sumuje stĺpec „Cena objednávky“:
Obrat := SUM(‚Objednávky'[Cena objednávky])
A pre tabuľku „Plánovacia tabuľka“ a stĺpec „Plánovaný obrat“ si spravíme napr. takéto merítko:
Plán := SUM(‚Plánovacia tabuľka'[Plánovaný obrat])
Potom aj vzorec pre percento plnenia plánu bude o dosť jednoduchší:
% plnenia plánu := [Obrat] / [Plán]
Prípadne pri použití funkcie DIVIDE:
% plnenia plánu = DIVIDE ( [Obrat] ; [Plán] )
A výsledok, po nahradení stĺpcov v kontingenčke merítkami, bude identický:
Takže takto viete porovnávať 2 stĺpce v jazyku DAX, keď ich používate v merítkach. Pri každom použití stĺpca v merítku si len treba uvedomiť, že stĺpec reprezentuje vo všeobecnosti N hodnôt, a ak s ním chcete pracovať priamo, tak ho treba zabaliť do nejakej sumovacej/agregačnej funkcie. Výnimkou z tohto sú samozrejme funkcie, ktoré očakávajú na vstupe stĺpec – napr. funkcie Time Intelligence – ale vo všetkých ostatných prípadoch je potrebné sa držať tohto pravidla. A úplne ideálne je používať to pravidlo, ktoré je uvedené vyššie – baliť sumy do merítok. Pretože to vám potom nielen sprehľadní nasledujúce vzorce, ale pri pokročilejších výpočtoch umožní aj opätovne využiť ten istý vzorec v ďalších výpočtoch.
Podstatné však je, že si môžete odškrtnúť ďalšiu daxovú záhadu zo zoznamu, ktorý vám nedá spať, a môžete veselo počítať ďalej 🙂
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.