Keď sa prvý raz učíte používať Power Pivot, väčšina používateľov zistí, že reálny výkon je v agregovaní alebo výpočte výsledku niektorým spôsobom. Ak údaje majú stĺpec s číselnými hodnotami, môžete ich jednoducho agregovať tak, že ich vyberiete v kontingenčnej tabuľke alebo zozname polí funkcie Power View. Keďže je číselné, od prírody sa táto agregácia automaticky sčíta, priemeruje, počíta alebo bez ohľadu na typ agregácie, ktorý vyberiete. Tento príklad sa nazýva implicitný miera. Implicitné opatrenia sú skvelé na rýchlu a jednoduchú agregáciu, ale majú limity a tieto limity takmer vždy možno vyriešiť explicitnými odchýlkami a vypočítanými stĺpcami.
Pozrime sa najprv na príklad, v ktorom sa používa vypočítaný stĺpec na pridanie novej textovej hodnoty pre každý riadok tabuľky s názvom Produkt. Každý riadok v tabuľke Produkt obsahuje všetky druhy informácií o každom produkte, ktorý predávame. Máme stĺpce pre názov produktu, farbu, veľkosť, priemennú cenu atď. Máme ďalšiu súvisiacu tabuľku s názvom Kategória produktu, ktorá obsahuje stĺpec NázovKategórieNázovKategórie. Chceme, aby každý produkt v tabuľke Produkt zahŕňal názov kategórie produktov z tabuľky Kategória produktov. V našej tabuľke Produkty môžeme vytvoriť vypočítavaný 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 ProductCategoryName 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 pomocou vypočítaného stĺpca pridať pevnú hodnotu 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 ďalší príklad, v ktorom chceme vypočítať maržu zisku pre kategórie produktov. Toto je bežný scenár, dokonca aj vo mnohých kurzoch. V údajovom modeli máme tabuľku Predaj, ktorá obsahuje údaje transakcií 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 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 produktov do stĺpcov a nové pole Zisk do oblasti HODNOTY (stĺpec v tabuľke v programe PowerPivot je pole v zozname polí kontingenčnej tabuľky). Výsledkom je implicitný miera s názvom Súčet zisku. Ide o súhrnné množstvo hodnôt zo stĺpca o zisku pre každú z rôznych kategórií produktov. Náš výsledok vyzerá takto:
V tomto prípade má zisk zmysel iba ako pole v časti HODNOTY. Ak by sme umiestnili Zisk do oblasti STĹPCE, kontingenčná tabuľka bude vyzerať takto:
Pole Zisk neposkytuje žiadne užitočné informácie, ak sa umiestni do oblastí STĹPCE, RIADKY alebo FILTRE. Má význam iba ako agregovaná hodnota v oblasti HODNOTY.
Chceli sme vytvoriť stĺpec s názvom Zisk, ktorý vypočíta maržu zisku pre každý riadok v tabuľke Predaj. Potom sme pridali Zisk do oblasti HODNOTY v kontingenčnej tabuľke, čím sa automaticky vytvára implicitná miera, kde sa výsledok vypočíta pre každú z kategórií produktov. Ak si myslíte, že sme naozaj dvakrát vypočítali zisk z kategórií produktov, je to správne. Najskôr 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 myslíte, že ani nie je potrebné vytvoriť vypočítavaný stĺpec Zisk, tiež je to správne. Ako teda vypočítame zisk bez vytvorenia vypočítaného stĺpca Zisk?
Zisk by sa v skutočnosti lepšie vypočítaval ako explicitný miera.
V súčasnosti vám v tabuľke Predaj a Kategória produktov v stĺpcoch a Zisk v hodnotách kontingenčnej tabuľky opustíme vypočítavaný stĺpec Zisk, aby sme mohli porovnať výsledky.
V oblasti výpočtov v tabuľke Predaj vytvoríme mieru s názvom Celkový zisk (aby sme zabránili konfliktom v pomenovaní). Nakoniec bude mať rovnaký výsledok ako predtým, ale bez vypočítaného stĺpca Zisk.
Najskôr v tabuľke Predaj vyberte stĺpec ObjemPredaja a potom kliknutím na položku Automatický súčet vytvorte explicitný súčet miery ObjemPredaja. Explicitný miera je ten, ktorý vytvoríme v oblasti výpočtov tabuľky v doplnku Power Pivot. V prípade stĺpca COGS robíme to isté. Tieto celkové objemy predaja a celkové COGS premenováme, aby sme ich ľahšie identifikovali.
Potom vytvoríme ďalší miera pomocou tohto vzorca:
Celkový zisk:=[ Celkový objem predaja] - [Celkové COG]
Poznámka: Môžeme tiež napísať vzorec ako Celkový zisk:=SUM([ObjemPredaja]) - SUM([COGS]), ale vytvorením samostatných opatrení Total SalesAmount a Total COGS (Celkový zisk) ich môžeme použiť aj v kontingenčnej tabuľke a môžeme ich použiť ako argumenty v rôznych vzorcoch s mierami.
Po zmene formátu nášho nového formátu miery celkového zisku na menu ho môžeme pridať do našej kontingenčnej tabuľky.
Uvidíte náš nový meranie celkového zisku rovnaké výsledky ako vytvorenie vypočítaného stĺpca Zisk a jeho umiestnenie v časti HODNOTY. Rozdiel je v tom, že miera celkového zisku je omnoho efektívnejšia a robí náš dátový model čistejší a zošúchnejší, pretože vypočítavame v čase a len pre polia, ktoré vyberieme pre kontingenčnú tabuľku. V skutočnosti nepotrebujeme, aby stĺpec Zisk bol vypočítaný na základe všetkých údajov.
Prečo je táto posledná časť dôležitá? Vypočítavané stĺpce pridávajú údaje do dátového modelu a údaje zaberú pamäť. Pri obnovení dátového modelu sú na prepočítanie všetkých hodnôt v stĺpci Zisk potrebné aj spracovanie zdrojov. V skutočnosti nepotrebujeme prevziať zdroje podobné tomuto, pretože v skutočnosti chceme vypočítať zisk, keď v kontingenčnej tabuľke vyberieme polia Zisk pre, ako sú kategórie produktov, oblasť alebo dátumy.
Pozrime sa na ďalší príklad. Na mieste, kde vypočítaný stĺpec vytvorí výsledky, ktoré na prvý pohľad vyzerajú správne, ale....
V tomto príklade chceme vypočítať objemy predaja ako percento celkového predaja. V našej tabuľke Predaj vytvoríme vypočítavaný stĺpec s názvom % predaja, napríklad takto:
Náš vzorec uvádza: Pre každý riadok v tabuľke Predaj vydelte čiastku v stĺpci ObjemPredaja súčtom 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 vyberiete nový stĺpec % predaja a umiestnime ju do stĺpca HODNOTY, dostaneme 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 2007. Toto je to, čo dostaneme.
Na prvý pohľad sa to môže stále zobrazovať správne. Naše percentuálne hodnoty by však mali v skutočnosti celková hodnota 100 %, pretože chceme poznať percentuálny podiel z celkového predaja pre každú z našich kategórií produktov za rok 2007. Čo sa teda stalo?
Stĺpec % predaja vypočítal percento pre každý riadok, ktorý je hodnotou v stĺpci ObjemPredaja a vydelený súčtom všetkých hodnôt v stĺpci ObjemPredaja. Hodnoty vo vypočítaných stĺpcoch sú pevné. Ide o nemenný výsledok pre každý riadok tabuľky. Keď sme do kontingenčnej tabuľky pridali percento predaja, 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ú znalosť kontextu úrovne riadkov a kontextu filtra, čo je to, čo popisujeme tu.
Vypočítaný stĺpec % predaja môžeme odstrániť, pretože nám už nepomôže. Namiesto toho vytvoríme mieru, ktorá bude správne vypočítať percentá z celkového predaja bez ohľadu na použité filtre alebo rýchle filtre.
Pamätáte si na mieru TotalSalesAmount, ktorý sme predtým vytvorili, a ten, ktorý jednoducho sčíta stĺpec ObjemPredaja? Použili sme ho ako argument v našom miere celkového zisku a použijeme ho znova ako argument v našom novom vypočítavaom poli.
Tip: Vytvorenie explicitných opatrení, ako sú 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 iných opatrení v prípade, že potrebujete výsledok ako argument. Vďaka tomu sú vzorce efektívnejšie a ľahšie čitateľné. Toto je dobrý postup modelovania údajov.
Nový miera vytvoríme pomocou tohto vzorca:
% z celkového predaja:=([Celkový objemPredaja]) / CALCULATE([Celkový ObjemPredaja],ALLSELECTED())
Tento vzorec uvádza: Vydelenie výsledku z celkového predajaMyšle súčtom objemuPredaja bez akýchkoľvek filtrov stĺpcov alebo riadkov okrem tých, ktoré sú definované v kontingenčnej tabuľke.
Tip: Prečítajte si o funkciách CALCULATE a ALLSELECTED v časti Odkaz na DAX.
Ak teraz pridáme do kontingenčnej tabuľky naše nové percento z celkového predaja, získate:
To vyzerá lepšie. Teraz sa percento z celkového predaja pre každú kategóriu produktov vypočíta ako percentuálny podiel celkového predaja za rok 2007. Ak v rýchlych filtroch kalendárneho roka vyberieme iný rok alebo viac ako jeden rok, pre naše kategórie produktov získate nové percentuálne hodnoty, no celkový súčet je stále 100 %. Môžeme pridať aj ďalšie rýchle filtre a filtre. Naše percento z celkového predaja vždy vyprodukuje percento z celkového predaja bez ohľadu na použité rýchle filtre alebo filtre. Pri použití opatrení sa výsledok vždy vypočíta podľa kontextu určeného poľami v stĺpcoch a RIADKOCH a podľa filtrov alebo filtrov, ktoré sa použijú. Ide o moc opatrení.
Tu je niekoľko pokynov, ktoré vám pomôžu pri rozhodovaní, či vypočítavaný stĺpec alebo miera potrebuje konkrétny výpočet:
Používanie vypočítaných stĺpcov
-
Ak chcete, aby sa nové údaje zobrazili v RIADKOCH, STĹPCOCH alebo VO FILTROCH v kontingenčnej tabuľke, alebo na OSI, LEGENDE alebo DLAŽDICI PODĽA vo vizualizácii Power View, musíte použiť vypočítaný stĺpec. Rovnako ako v prípade bežných stĺpcov údajov je možné vypočítané stĺpce použiť ako pole v ľubovoľnej oblasti a ak sú číselné, možno ich tiež agregovať 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 iba číslo mesiaca. Môžete vytvoriť vypočítaný stĺpec, ktorý z dátumov v stĺpci Dátum vypočíta iba číslo mesiaca. Príklad: =MONTH('Dátum'[Dátum]).
-
Ak chcete do tabuľky pridať textovú hodnotu pre každý riadok, použite vypočítaný stĺpec. Polia s textovými hodnotami nemožno nikdy agregovať v časti HODNOTY. Príklad: =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átum.
Použiť opatrenia
-
Ak bude výsledok výpočtu vždy závislý od ostatných polí, ktoré vyberiete v kontingenčnej tabuľke.
-
Ak potrebujete zložitejšie výpočty, napríklad vypočítať počet na základe určitého filtra, alebo vypočítať medziročný či odchýlka, použite vypočítavané pole.
-
Ak chcete zachovať veľkosť zošita na minimálny počet a maximalizovať jeho výkon, vytvorte čo najviac výpočtov. V mnohých prípadoch môžu byť všetky výpočty miery a výrazne sa zudukuje veľkosť zošita a zrýchli sa čas obnovenia.
Majte na pamäti, že vytváranie vypočítavaných stĺpcov, ako sme to urobili v stĺpci Zisk, nie je v poriadku a potom ho agreguje v kontingenčnej tabuľke alebo zostave. Je to skutočne veľmi dobrý a jednoduchý spôsob, ako sa naučiť niečo nové a vytvoriť vlastné výpočty. Keďže vaše pochopenie týchto dvoch mimoriadne výkonných funkcií doplnku Power Pivot narastá, budete chcieť vytvoriť čo najefektívnejší a najpresnejší dátový model, ktorý môžete. Veríme, že to, čo ste sa naučili, vám pomôže. Nájdete tu aj niekoľko ďalších veľmi skvelých zdrojov informácií, ktoré vám môžu pomôcť. Tu je len niekoľko: Kontext vo vzorcoch DAX, agregácie v doplnku Power Pivota Stredisko zdrojov pre DAX. Hoci je tento nástroj trochu pokročilejší Excel zameraný na účtovných a finančných profesionálov, modelovanie a analýza údajov o zisku Excel strate s microsoft Power Pivotom vo vzorovej ukážke sa načíta s veľkými príkladmi modelovania údajov Excel vzorcov.
