Prečo mi nejdú porovnávať stĺpce v merítkach v jazyku DAX? Ani v PowerPivote, ani v Power BI?

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:

  1. v oblasti hodnôt bude stĺpec “Cena objednávky” z tabuľky Objednávky,
  2. 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:

% plnenia plánu := ‘Objednávky'[Cena objednávky] / ‘Plánovacia tabuľka'[Plánovaný obrat]

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 🙂

Pridaj komentár

Vaša e-mailová adresa nebude zverejnená.