Používanie vypočítavaných stĺpcov a vypočítavaných polí

Keď sa používatelia prvýkrát učia používať doplnok Power Pivot, zistia, že skutočná sila spočíva v nejakým spôsobe agregácie alebo výpočtu výsledku. Ak vaše ú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. Keďže má číselný charakter, bude zo svojej podstaty automaticky sčítaný, priemerovaný, spočítaný alebo akýkoľvek typ agregácie, ktorú vyberiete. Nazýva sa implicitné mierenie. Implicitné miery sú skvelé na rýchle a jednoduché agregovanie, majú však limity a tieto limity je takmer vždy možné prekonať explicitnými mierkami a vypočítavanými stĺpcami.

Pozrime sa najprv na príklad, v ktorom vypočítavaný stĺpec použijeme na pridanie novej textovej hodnoty pre každý riadok v tabuľke s názvom Produkt. Každý riadok v tabuľke Produkty 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órieProduktu. Chceme, aby každý produkt v tabuľke Produkty obsahoval názov kategórie produktu z tabuľky Kategória produktov. V tabuľke Produkty môžeme vytvoriť vypočítavaný stĺpec s názvom Kategória produktov takto:

Náš nový vzorec Kategória produktu používa funkciu RELATED DAX na získanie hodnôt zo stĺpca NázovKategórie produktu v súvisiacej tabuľke Kategória produktov a potom zadá tieto hodnoty pre každý produkt (každý riadok) do tabuľky Produkty.

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 použiť neskôr v oblasti RIADKY, STĹPCE alebo FILTRE kontingenčnej tabuľky alebo v zostave Power View.

Vytvorme si ďalší príklad, v ktorom chceme vypočítať ziskovú maržu pre naše kategórie produktov. Tento scenár je bežný dokonca aj v mnohých kurzoch. V údajovom modeli máme tabuľku Predaj, ktorá obsahuje údaje o transakciách. Medzi tabuľkami Predaj a Kategórie produktov existuje vzťah. V tabuľke Predaj máme stĺpec, ktorý obsahuje sumy predaja, a ďalší stĺpec, ktorý obsahuje náklady.

Môžeme vytvoriť vypočítaný stĺpec, ktorý vypočíta výšku 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 produktu do STĹPCE a 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á miera nazvaná Súčet zisku. Ide o súhrnné množstvo hodnôt zo stĺpca Zisk pre každú z rôznych kategórií produktov. Náš výsledok vyzerá takto:

V tomto prípade má Profit zmysel len ako pole vo funkcii HODNOTY. Ak by sme údaje Profit umiestnili do oblasti STĹPCE, naša kontingenčná tabuľka by vyzerala takto:

Pole Zisk neposkytuje žiadne užitočné informácie, keď je umiestnené v oblastiach STĹPCE, RIADKY alebo FILTRE. Dáva zmysel len ako agregovaná hodnota v oblasti HODNOTY.

Vytvorili sme stĺpec s názvom Zisk, ktorý vypočíta ziskovú maržu pre každý riadok v tabuľke Predaj. Potom sme do oblasti HODNOTY v kontingenčnej tabuľke pridali zisk, čím sme automaticky vytvorili implicitné ukazovatele, kde sa vypočíta výsledok pre každú z kategórií produktov. Ak si myslíte, že sme v skutočnosti vypočítali zisk pre naše kategórie produktov dvakrát, máte pravdu. Najskôr sme vypočítali zisk pre každý riadok v tabuľke Predaj a potom sme zisk pridali do oblasti HODNOTY, kde bol agregovaný pre jednotlivé kategórie produktov. Ak si tiež myslíte, že sme vlastne nemuseli vytvoriť stĺpec vypočítaný zisk, tiež máte pravdu. Ako potom vypočítame svoj zisk bez vytvorenia vypočítaného stĺpca Zisk?

Zisk by sa skutočne lepšie vypočítal ako explicitné meradlo.

Zatiaľ ponecháme vypočítavaný stĺpec Zisk v tabuľke Predaj a Kategóriu produktov v STĹPCOCH a Zisk v HODNOTÁCH našej kontingenčnej tabuľky, aby sme mohli porovnať naše výsledky.

V oblasti výpočtov v tabuľke Predaj vytvoríme ukazovateľ s názvom Celkový zisk (aby sme sa vyhli konfliktom pomenovania). Nakoniec prinesie rovnaké výsledky ako predtým, ale bez stĺpca s vypočítavaným ziskom.

Najprv v tabuľke Predaj vyberieme stĺpec ObjemPredaja a potom kliknutím na položku Automatický súčet vytvoríme explicitnú mieru Súčet Objemu predaja . Nezabudnite, že explicitný ukazovateľ je taký, ktorý vytvoríme v oblasti výpočtov tabuľky v Power Pivote. To isté robíme pre stĺpec COGS. Premenujeme ich Celkové objemy predaja a Celkové COGS, aby sa dali ľahšie identifikovať.

Potom vytvoríme ďalší ukazovateľ pomocou tohto vzorca:

Celkový zisk:=[Celkové objemy predaja] - [Celkové COGS]

Po zmene formátu nového ukazovateľa celkového zisku na menu ho môžeme pridať do kontingenčnej tabuľky.

Nová miera celkového zisku vráti rovnaké výsledky ako vytvorenie stĺpca vypočítavaného zisku a jeho umiestnenia medzi hodnoty. Rozdiel je v tom, že ukazovateľ celkového zisku je oveľa efektívnejší a náš dátový model je prehľadnejší a prehľadnejší, pretože počítame v danom čase a len pre polia, ktoré vyberieme pre kontingenčnú tabuľku. Tento stĺpec vypočítaný zisk predsa naozaj nepotrebujeme.

Prečo je táto posledná časť dôležitá? Vypočítavané stĺpce pridávajú údaje do dátového modelu a údaje zaberajú pamäť. Ak obnovíme dátový model, zdroje spracovania sú tiež potrebné na prepočet všetkých hodnôt v stĺpci Zisk. Vlastne nepotrebujeme zaberať takéto zdroje, pretože naozaj chceme vypočítať náš zisk pri výbere polí, pre ktoré chceme v kontingenčnej tabuľke zistiť zisk, ako sú kategórie produktov, oblasť alebo dátum.

Pozrime sa na iný príklad. Taký, 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 tabuľke Predaj vytvoríme vypočítaný stĺpec s názvom % z predaja takto:

Náš vzorec hovorí: Pre každý riadok v tabuľke Predaj vydeľte sumu v stĺpci ObjemPredaja súčtom SÚČET všetkých čias v stĺpci ObjemPredaja.

Ak vytvoríme kontingenčnú tabuľku a pridáme kategóriu produktov do STĹPCE a vyberieme nový stĺpec % z predaja a vložíme ho do časti HODNOTY, získame súčet % predaja pre každú z našich kategórií produktov.

Dobre. Zatiaľ to vyzerá dobre. Pridajme však rýchly filter. Pridáme Calendar Year a vyberieme rok. V tomto prípade vyberieme verziu 2007. To je to, čo dostaneme.

Na prvý pohľad sa to stále môže javiť ako správne. V skutočnosti by však naše percentuálne hodnoty mali slúžiť na 100 %, pretože chceme poznať percentuálny podiel celkového predaja pre každú z našich kategórií produktov za rok 2007. Čo sa teda pokazilo?

Náš stĺpec % predaja vypočítaval percento pre každý riadok, ktorý je hodnotou v stĺpci ObjemPredaja, vydelenou celkovým súčtom všetkých hodnôt v stĺpci ObjemPredaja. Hodnoty vo vypočítanom stĺpci sú pevne stanovené. Predstavujú nezmeniteľný výsledok pre každý riadok tabuľky. Keď sme do kontingenčnej tabuľky pridali % predaja , sčítalo sa 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%.

Môžeme odstrániť vypočítaný stĺpec % z predaja, pretože nám to nepomôže. Namiesto toho vytvoríme ukazovateľ, ktorý správne vypočíta percento z celkového predaja bez ohľadu na to, aké filtre alebo rýchle filtre sú použité.

Pamätáte si ukazovateľ TotalSalesAmount, ktorý sme vytvorili predtým a ktorý jednoducho sčítava stĺpec ObjemPredaja? Použili sme ho ako argument v ukazovateli Total Profit (Celkový zisk) a použijeme ho znova ako argument v našom novom vypočítavanom poli.

Nový ukazovateľ vytvoríme pomocou nasledujúceho vzorca:

% z celkového predaja:=([Celkové množstvo predaja]) / CALCULATE([Celkové množstvo predaja], ALLSELECTED())

Tento vzorec hovorí: Vydelenie výsledku z hodnoty Total SalesAmount súčtom celkovej hodnoty ObjemPredaja bez filtrovania stĺpcov alebo riadkov okrem filtrov definovaných v kontingenčnej tabuľke.

Ak teraz pridáme nové % z celkového predaja do kontingenčnej tabuľky, dostaneme:

To vyzerá lepšie. Teraz sa % celkového predaja pre každú kategóriu produktov vypočíta ako percento celkového predaja za rok 2007. Ak v rýchlom filtri Kalendárny rok vyberieme iný alebo viac ako jeden rok, získame nové percentuálne hodnoty pre naše kategórie produktov, ale celkový súčet je stále 100 %. Pridať môžeme aj ďalšie rýchle filtre a filtre. Náš ukazovateľ % celkového predaja vždy vytvára percento z celkového predaja bez ohľadu na použité rýchle filtre alebo filtre. Pri použití ukazovateľov sa výsledok vždy vypočíta podľa kontextu určeného poľami v STĹPCOCH a RIADKOCH a použitými filtrami alebo rýchlymi filtrami. Toto je sila opatrení.

Tu je niekoľko usmernení, ktoré vám pomôžu pri rozhodovaní, či vypočítaný stĺpec alebo ukazovateľ vyhovuje konkrétnej potrebe výpočtu:

Použitie vypočítaných stĺpcov

• Ak chcete, aby sa vaše nové údaje zobrazili v RIADKOCH, STĹPCOCH, vo FILTROCH v kontingenčnej tabuľke alebo na OSI, LEGENDE alebo DLAŽDICI PODĽA vo vizualizácii, musíte použiť vypočítaný stĺpec. Vypočítavané stĺpce možno rovnako ako bežné stĺpce údajov použiť ako pole v ľubovoľnej oblasti, a ak sú číselné, možno ich agregovať aj do funkcie HODNOTY.
• Ak chcete, aby nové údaje boli pevnou hodnotou riadka. Máte napríklad tabuľku dátumov so stĺpcom dátumov a chcete ďalší stĺpec, ktorý obsahuje iba číslo mesiaca. Môžete vytvoriť vypočítaný stĺpec, ktorý vypočíta len číslo mesiaca z dátumov v stĺpci Dátum. Príklad: =MONTH('Dátum'[Dátum]).
• Ak chcete pridať textovú hodnotu pre každý riadok tabuľky, použite vypočítaný stĺpec. Polia s textovými hodnotami nemožno nikdy agregovať v rámci funkcie HODNOTY. Napríklad funkcia =FORMAT('Dátum'[Dátum];"mmmm") nám poskytne názov mesiaca pre každý dátum v stĺpci Dátum v tabuľke dátumov.

Používanie ukazovateľov

• Ak bude výsledok výpočtu vždy závisieť od ostatných polí vybratých v kontingenčnej tabuľke.
• Ak potrebujete vykonávať zložitejšie výpočty, napríklad vypočítať počet na základe určitého filtra alebo medziročný výpočet odchýlky, použite vypočítavané pole.
• Ak chcete obmedziť veľkosť zošita na minimum a maximalizovať jeho výkon, vytvorte čo najviac výpočtov, čo najviac mier. V mnohých prípadoch môžu byť všetky výpočty mierami, ktoré výrazne zmenšujú veľkosť zošita a urýchľujú obnovenie.

Nezabúdajte, že nie je nič zlé na tom, ak sa vytvoria vypočítané stĺpce, ako je to urobilo v našom stĺpci Zisk, a potom ho agregujú do kontingenčnej tabuľky alebo zostavy. Je to vlastne naozaj dobrý a jednoduchý spôsob, ako sa naučiť a vytvoriť vlastné výpočty. S postupným pochopením týchto dvoch mimoriadne výkonných funkcií doplnku Power Pivot budete postupne chcieť vytvoriť čo najefektívnejší a najpresnejší dátový model. Dúfam, že to, čo ste sa tu naučili, pomôže. Existuje niekoľko ďalších skutočne skvelých zdrojov, ktoré vám môžu pomôcť. Tu je len niekoľko z nich: Kontext vo vzorcoch jazyka DAX, agregácie v doplnku Power Pivot a Centrum zdrojov jazyka DAX. Ukážka modelovania a analýzy údajov o ziskoch a stratách pomocou doplnku Microsoft Power Pivot v Exceli je síce trochu pokročilejšia a zameraná na účtovníkov a profesionálov v oblasti účtovníctva a financií a zahŕňa aj skvelé príklady modelovania údajov a vzorcov.