Princip používání measures aneb proč DAX není Excel

Nejdřív musím varovat, že tohle bude trochu delší text, takže pokud jste roztěkaní ze sociálních sítí, ani to nezkoušejte 🙂

Na druhou stranu – věřím, že lidem, kteří začínají pracovat s Power BI (a že takových je hodně) může přečtení pomoci – protože co se osvědčuje v Excelu, nemusí se osvědčovat v Power BI. A dost chápu, že je těžké změnit logiku výpočtů, přeci jen – Excel je železná košile.

Takže v tomto článku bych chtěl popsat, proč byste měli v datových modelech Power BI (a Power Pivotu) používat measures, na které z Excelu nejste zvyklí.

Jako první příklad si vezmu tuhle tabulku. Sleduji v ní objednávky, kde si různí zákazníci kupovali různé počty kusů za nějakou celkovou cenu.

Mým úkolem bude sledovat průměrné ceny za jednu položku – podle dodavatelů a podle času.

Jak bych na to šel v Excelu?

Nejprve si uvědomím, že NESMÍM nejprve do nového sloupce vydělit celkovou cenu počtem položek a pak zprůměrovat tyto podíly pro jednotlivé zákazníky – viz povídání o váženém průměru. Takže takhle tedy ne:

A jak tedy ano? Mohu za každého zákazníka sečíst všechny ceny, sečíst všechny ceny položek a pak tyto součty vzájemně vydělit. V Excelu to můžete udělat několika způsoby. Třeba pomocí dvou funkcí SUMIFS:

Nevýhoda by byla, že by to bylo celkem zdlouhavé, že by se automaticky nepřidávali další dodavatelé a že byste museli dělat jedny výpočty pro průměrné ceny podle dodavatelů, jiné pro průměrné ceny podle roků, jiné podle měsíců…

Další možností by bylo použít počítaná pole v kontingenční tabulce.

Tady jsou zase jiné problémy – počítaná pole jsou velmi omezená co se týká funkcí. Umí víceméně základní počítací operátory a pár dalších základních funkcí, nicméně pro jakékoliv složitější výpočty se prostě použít nedají.

Mnohem lepší je, a jsme u toho, použít míru – measure. Budu to ukazovat v Power BI, ale téměř stejně to funguje v Power Pivotu.

Measure zapíšeme v DAXu jednoduše a předvídatelně, jedná se o součet cen dělený součtem kusů:

  • Average price per item = sum(Table1[Total price])/sum(Table1[Number of pieces])

Při zapsání measure občas začátečníky mate, že hned nevidí nějaké číslo, ale to je právě ono. Measure sama o sobě totiž nemá žádný výsledek.

Abychom z ní dostali nějaké číslo, musíme ji zobrazit (teď teda maličko zjednodušuji, ale budiž) ve vizuálu Power BI nebo v kontingenční tabulce Power Pivotu. A v tom vizuálu už tu measure obvykle rozdělujeme “podle něčeho” a můžeme vidět její hodnotu.

Trik je v tom, že jednou spočítanou measure mohu použít do mnoha vizuálů a seskupovat ji podle čehokoliv. Podle zákazníků:

podle kvartálů:

prostě podle čehokoliv.

Jaké jsou tedy výhody počítání pomocí measures v porovnání s klasickým excelovským počítáním do sloupečků?

Zrychlení tvorby výpočtů.

  • Jednou spočítaná measure se dá používat v různých vizuálech, jak jsme viděli výše.

Širší možnosti

Recyklace measures

  • Jednou vytvořenou measure je možné používat v dalších measurech.

Pokud bychom např. spočítali (a teď už opouštíme původní tabulku) počet nějakých hodnot za minulý rok:

  • Last year = calculate(sum(history[Items]);SAMEPERIODLASTYEAR(history[Date]))
Pak můžeme tuto hodnotu vzít, zkombinovat s funkcí TOTALYTD, která bere data od začátku roku do teď:
  • Last year YTD = totalytd([Last year];history[Date];all(history))
a vyjde nám logicky kolik to bylo vloni od začátku roku do dnešního data. Elegantní, ne? 🙂 

Ještě pár poznámek

  • DAX neznamená measures. I v DAXu je možné tvořit sloupce (to je víceméně excelovská logika), také vytvářet tabulky (tady je jeden z malých rozdílů mezi Power BI a Power Pivot – v Power Pivotu to nejde) nebo vytvářet role v DAXu. Troufnu si ale tvrdit, že s measures v DAXu strávíme asi mnohem více času než se sloupci, tabulkami nebo rolemi.
  • Measures se dá používat, pokud chceme data z Powerbi.com cloudu analyzovat online v Excelu. Pro hodnoty ze sloupců to nefunguje.
  • V measures můžete používat parametry – ve sloupcích ne.

Tak nevím. Dává to smysl?