Pri prvom učení sa o používaní doplnku Power Pivot väčšina používateľov zistí, že skutočná sila spočíva v agregácii alebo výpočte výsledku nejakým spôsobom. Ak údaje obsahujú stĺpec s číselnými hodnotami, môžete ho jednoducho agregovať tak, že ho vyberiete v kontingenčnej tabuľke alebo zozname polí funkcie Power View. Vzhľadom na to, že ide o číselnú hodnotu, bude sa automaticky sčítavať, spárovať, počítať alebo akokoľvek typ agregácie, ktorú vyberiete. Toto je známe ako implicitná mierka. Implicitné mierky sú skvelé na rýchlu a jednoduchú agregáciu, ale majú limity a tieto limity možno takmer vždy prekonať explicitnými mierkami a vypočítanými stĺpcami.
Pozrime sa najprv na príklad, v ktorom použijeme vypočítaný stĺpec na pridanie novej textovej hodnoty pre každý riadok v tabuľke s názvom Produkt. Každý riadok v tabuľke Produkt obsahuje všetky druhy informácií o každom predávanom produkte. Máme stĺpce pre názov produktu, farbu, veľkosť, cenu predajcu atď. Máme ďalšiu súvisiacu tabuľku s názvom Kategória produktov, ktorá obsahuje stĺpec NázovKategórieProduktov. Chceme, aby každý produkt v tabuľke Produkt obsahoval názov kategórie produktov z tabuľky Kategória produktov. V tabuľke Produkt môžeme vytvoriť vypočítaný stĺpec s názvom Kategória produktov takto:
Náš nový vzorec Kategória produktov používa funkciu RELATED DAX na získanie hodnôt zo stĺpca NázovKategórieProduktov v súvisiacej tabuľke Kategória produktov a potom zadá tieto hodnoty pre každý produkt (každý riadok) do tabuľky Produkt.
Toto je skvelý príklad toho, ako môžeme použiť vypočítaný stĺpec na pridanie pevnej hodnoty pre každý riadok, ktorý môžeme neskôr použiť v oblasti RIADKY, STĹPCE alebo FILTRE kontingenčnej tabuľky alebo v zostave Power View.
Poďme vytvoriť ďalší príklad, v ktorom chceme vypočítať ziskové rozpätie pre naše kategórie produktov. Toto je bežný scenár, dokonca aj v mnohých kurzoch. V dátovom modeli máme tabuľku Predaj, ktorá obsahuje údaje o transakciách a medzi tabuľkou Predaj a tabuľkou Kategória produktov existuje vzťah. V tabuľke Predaj máme stĺpec, ktorý obsahuje objemy predaja, a ďalší stĺpec s nákladmi.
Môžeme vytvoriť vypočítaný stĺpec, ktorý vypočíta čiastku zisku pre každý riadok odčítaním hodnôt v stĺpci COGS od hodnôt v stĺpci ObjemPredaja, napríklad takto:
Teraz môžeme vytvoriť kontingenčnú tabuľku a presunúť pole Kategória produktov do stĺpcov a naše nové pole Zisk do oblasti HODNOTY (stĺpec v tabuľke v doplnku PowerPivot je pole v zozname polí kontingenčnej tabuľky). Výsledkom je implicitná mierka s názvom Súčet zisku. Je to agregované množstvo hodnôt zo stĺpca zisku pre každú z rôznych kategórií produktov. Výsledok vyzerá takto:
V tomto prípade má zisk zmysel len ako pole v časti HODNOTY. Ak by sme do oblasti STĹPCE vložili profit, naša kontingenčná tabuľka by vyzerala takto:
Naše pole Profit (Zisk) neposkytuje žiadne užitočné informácie, keď je umiestnené v oblastiach STĹPCE, RIADKY alebo FILTRE. Má zmysel len ako agregovaná hodnota v oblasti VALUES.
To, čo sme urobili, je vytvoriť stĺpec s názvom Zisk, ktorý vypočíta ziskové rozpätie pre každý riadok v tabuľke Predaj. Potom sme pridali zisk do oblasti HODNOTY našej kontingenčnej tabuľky, čím sa automaticky vytvorí implicitná mierka, kde sa výsledok vypočíta pre každú kategóriu produktov. Ak si myslíte, že sme dvakrát vypočítali zisk pre naše kategórie produktov, máte pravdu. Najprv sme vypočítali zisk pre každý riadok v tabuľke Predaj a potom sme pridali zisk do oblasti HODNOTY, kde bol agregovaný pre každú kategóriu produktov. Ak si tiež myslíte, že sme naozaj nemuseli vytvárať vypočítaný stĺpec Profit (Zisk), máte tiež pravdu. Ale ako potom vypočítame náš zisk bez vytvorenia vypočítaného stĺpca Profit (Zisk)?
Zisk, by sa naozaj lepšie vypočítal ako explicitná mierka.
Nateraz ponecháme vypočítaný stĺpec Profit (Zisk) v tabuľke Sales (Predaj) a Product Category (Kategória produktov) v stĺpcoch a Profit (Zisk) v hodnotách našej kontingenčnej tabuľky (VALUES), aby sme porovnali naše výsledky.
V oblasti výpočtov v tabuľke Predaj vytvoríme mierku s názvom Celkový zisk (aby sme sa vyhli konfliktom pomenovania). Nakoniec to prinesie rovnaké výsledky ako predtým, ale bez vypočítaného stĺpca Profit (Zisk).
Najprv v tabuľke Sales (Predaj) vyberieme stĺpec SalesAmount (ObjemPredaja) a potom kliknutím na položku Automatický súčet vytvoríme explicitnú mierku Sum of SalesAmount(ObjemPredaja). Nezabúdajte, že explicitná mierka je mierka, ktorú vytvoríme v oblasti výpočtov tabuľky v doplnku Power Pivot. To isté robíme aj v stĺpci COGS. Tieto celkové objemy predaja a celkové predaný tovar premenujeme, aby sa dali ľahšie identifikovať.
Potom vytvoríme ďalšiu mierku pomocou tohto vzorca:
Celkový zisk:=[ Total SalesAmount] – [Total COGS]
Poznámka: Môžeme tiež napísať vzorec ako Total Profit:=SUM([SalesAmount]) - SUM([COGS]), ale vytvorením samostatných mierok Celkový objemPredaja a Celkový objem cogs ich môžeme použiť aj v kontingenčnej tabuľke a môžeme ich použiť ako argumenty vo všetkých druhoch iných vzorcov mierok.
Po zmene formátu našej novej mierky Celkový zisk na menu ho môžeme pridať do kontingenčnej tabuľky.
Môžete vidieť, že naša nová mierka Celkový zisk vráti rovnaké výsledky ako vytvorenie vypočítaného stĺpca Profit (Zisk) a jeho následné umiestnenie do časti VALUES (HODNOTY). Rozdiel je v tom, že mierka Celkový zisk je oveľa efektívnejšia a zefektívňuje a zefektívňuje dátový model, pretože vypočítavame v danom čase a len pre polia, ktoré vyberieme pre našu kontingenčnú tabuľku. Tento vypočítaný stĺpec Profit (Zisk) ani nepotrebujeme.
Prečo je táto posledná časť dôležitá? Vypočítané stĺpce pridajú údaje do dátového modelu a údaje zaberajú pamäť. Ak obnovíme dátový model, na prepočítanie všetkých hodnôt v stĺpci Zisk sú potrebné aj zdroje na spracovanie. Takéto zdroje naozaj nepotrebujeme prijať, pretože naozaj chceme vypočítať náš zisk, keď vyberieme polia, pre ktoré chceme mať v kontingenčnej tabuľke zisk, ako sú napríklad kategórie produktov, oblasť alebo dátumy.
Pozrime sa na ďalší príklad. Jeden z miest, kde vypočítaný stĺpec vytvára výsledky, ktoré na prvý pohľad vyzerajú správne, ale....
V tomto príklade chceme vypočítať objemy predaja ako percento z celkového predaja. V našej tabuľke Predaj vytvoríme vypočítaný stĺpec s názvom % predaja takto:
Náš vzorec uvádza: Pre každý riadok v tabuľke Sales (Predaj) vydelte čiastku v stĺpci SalesAmount (ObjemPredaja) celkovým súčtom sum všetkých čiastok v stĺpci ObjemPredaja.
Ak vytvoríme kontingenčnú tabuľku a pridáme kategóriu produktov do stĺpcov a vyberieme náš nový stĺpec % predaja a vložíme ho do časti HODNOTY, získame celkový súčet % predaja pre každú z našich kategórií produktov.
Ok. Zatiaľ to vyzerá dobre. Pridajme však rýchly filter. Pridáme kalendárny rok a potom vyberieme rok. V tomto prípade vyberieme rok 2007. To je to, čo dostaneme.
Na prvý pohľad sa to môže stále zdať správne. Naše percentá by však mali v skutočnosti spolu 100 %, pretože chceme poznať percento celkového predaja pre každú z našich kategórií produktov za rok 2007. Tak čo sa stalo?
Náš stĺpec % predaja vypočítal percento pre každý riadok, ktorý je hodnotou v stĺpci ObjemPredaja vydelený súčtom všetkých hodnôt v stĺpci ObjemPredaja. Hodnoty vo vypočítanom stĺpci sú pevné. Sú nemenným výsledkom pre každý riadok v tabuľke. Keď sme do kontingenčnej tabuľky pridali % predaja , bola agregovaná ako súčet všetkých hodnôt v stĺpci ObjemPredaja. Tento súčet všetkých hodnôt v stĺpci % predaja bude vždy 100 %.
Tip: Nezabudnite si prečítať kontext vo vzorcoch DAX. Poskytuje dobré pochopenie kontextu na úrovni riadkov a kontextu filtra, čo tu popisujeme.
Môžeme odstrániť vypočítaný stĺpec % predaja, pretože nám to nepomôže. Namiesto toho vytvoríme mierku, ktorá správne vypočíta naše percento celkového predaja bez ohľadu na použité filtre alebo rýchle filtre.
Pamätáte si mierku TotalSalesAmount, ktorú sme predtým vytvorili, teda mierku, ktorá jednoducho sčíta stĺpec ObjemPredaja? Použili sme ho ako argument v mierke Celkový zisk a použijeme ho znova ako argument v novom vypočítavanom poli.
Tip: Vytváranie explicitných mierok, ako napríklad Celkový objem predaja a Celkový cogs, nie sú užitočné len v kontingenčnej tabuľke alebo zostave, ale sú užitočné aj ako argumenty v iných mierkach, keď potrebujete výsledok ako argument. Vďaka tomu sú vzorce efektívnejšie a čitateľnejšie. Toto je dobrá prax modelovania údajov.
Vytvoríme novú mierku s nasledujúcim vzorcom:
% celkového predaja:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Tento vzorec uvádza: Vydelenie výsledku z celkového objemu predaja súčtom súčtu ObjemPredaja bez akýchkoľvek filtrov stĺpcov alebo riadkov okrem filtrov definovaných v kontingenčnej tabuľke.
Tip: Nezabudnite si prečítať o funkciách CALCULATE a ALLSELECTED v odkaze jazyka DAX.
Ak teraz pridáme do kontingenčnej tabuľky naše nové % celkového predaja , získame:
Vyzerá to lepšie. Teraz sa percento celkového predaja pre každú kategóriu produktov vypočíta ako percento z celkového predaja za rok 2007. Ak vyberieme iný rok alebo viac ako jeden rok v rýchlom filtri KalendárnyRok, získame nové percentá pre naše kategórie produktov, ale celkový súčet je stále 100 %. Môžeme pridať aj ďalšie rýchle filtre a filtre. Naša mierka % celkového predaja vždy vytvorí percento celkového predaja bez ohľadu na použité rýchle filtre alebo filtre. Pri použití mierok sa výsledok vždy vypočíta podľa kontextu určeného poľami v stĺpcoch a riadkoch a všetkými použitými filtrami alebo rýchlymi filtrami. Toto je sila mierok.
Tu je niekoľko pokynov, ktoré vám pomôžu pri rozhodovaní, či je vypočítaný stĺpec alebo mierka vhodná pre konkrétnu potrebu výpočtu:
Používanie vypočítaných stĺpcov
-
Ak chcete, aby sa nové údaje zobrazovali v riadkoch, stĺpcoch alebo vo filtroch v kontingenčnej tabuľke, na osi, legende alebo dlaždici BY vo vizualizácii Power View, musíte použiť vypočítaný stĺpec. Rovnako ako bežné stĺpce údajov, aj vypočítané stĺpce možno použiť ako pole v ľubovoľnej oblasti a ak sú číselné, môžu byť tiež agregované v časti HODNOTY.
-
Ak chcete, aby boli nové údaje pevnou hodnotou riadka. Máte napríklad tabuľku dátumov so stĺpcom dátumov a chcete ďalší stĺpec, ktorý obsahuje len číslo mesiaca. Môžete vytvoriť vypočítaný stĺpec, ktorý vypočíta iba číslo mesiaca z dátumov v stĺpci Dátum. Napríklad =MONTH('Date'[Date]).
-
Ak chcete do tabuľky pridať textovú hodnotu pre každý riadok, použite vypočítaný stĺpec. Polia s textovými hodnotami nie je možné nikdy agregovať v časti HODNOTY. Napríklad vzorec =FORMAT('Date'[Date];"mmmm") nám prizná názov mesiaca pre každý dátum v stĺpci Dátum v tabuľke Dátum.
Používanie mierok
-
Ak bude výsledok výpočtu vždy závisieť od ostatných polí, ktoré vyberiete v kontingenčnej tabuľke.
-
Ak potrebujete vykonať zložitejšie výpočty, napríklad vypočítať počet na základe určitého filtra alebo vypočítať medziročný rozdiel alebo odchýlku, použite vypočítavané pole.
-
Ak chcete zachovať minimálnu veľkosť zošita a maximalizovať jeho výkon, vytvorte čo najviac výpočtov. V mnohých prípadoch môžu byť všetky výpočty mierkami, čím sa výrazne zmenší veľkosť zošita a skráti sa čas obnovenia.
Nezabúdajte, že na vytváraní vypočítaných stĺpcov, ako sme to urobili s naším stĺpcom Profit (Zisk), a následnom agregovaní v kontingenčnej tabuľke alebo zostave, nie je nič zlé. V skutočnosti je to naozaj dobrý a jednoduchý spôsob, ako sa o nich oboznámiť a vytvoriť si vlastné výpočty. Keďže vaše chápanie týchto dvoch mimoriadne výkonných funkcií doplnku Power Pivot narastá, budete chcieť vytvoriť čo najefektívnejší a najpresnejší dátový model. Dúfajme, že to, čo ste sa naučili tu pomáha. Tam sú niektoré ďalšie naozaj skvelé zdroje tam, ktoré vám môžu pomôcť taky. Tu je len niekoľko: Kontext vo vzorcoch DAX, agregáciách v doplnku Power Pivot a v Centre zdrojov jazyka DAX. A hoci je trochu pokročilejšia a smeruje na odborníkov v oblasti účtovníctva a financií, modelovanie a analýza údajov ziskov a strát pomocou doplnku Microsoft Power Pivot v Exceli sa načíta s skvelými príkladmi modelovania údajov a vzorcov.
