Kdaj uporabiti izračunane stolpce in izračunana polja

Ko se prvič naučijo uporabljati Power Pivot, večina uporabnikov odkrije, da je resnična moč v združevanju ali izračunavanju rezultatov na nek način. Če imajo vaši podatki stolpec s številskimi vrednostmi, ga lahko preprosto združite tako, da ga izberete v vrtilni tabeli ali seznamu polj Power View. Ker je po naravi številčna, se samodejno sešteje, povpreči, prešteje ali katera koli vrsta združevanja, ki jo izberete. To je znano kot implicitni ukrep. Implicitni ukrepi so odlični za hitro in enostavno združevanje, vendar imajo omejitve, ki jih je skoraj vedno mogoče premagati z eksplicitnimi meritvami in izračunanimi stolpci.

Najprej si oglejmo primer, kjer izračunani stolpec uporabimo za dodajanje nove besedilne vrednosti za vsako vrstico v tabeli z imenom Izdelek. Vsaka vrstica v tabeli »Izdelek« vsebuje vse vrste informacij o vsakem izdelku, ki ga prodajamo. Imamo stolpce za ime izdelka, barvo, velikost, prodajno ceno itd. Imamo še eno povezano tabelo z imenom Kategorija izdelka, ki vsebuje stolpec ProductCategoryName. Želimo, da vsak izdelek v tabeli »Izdelek« vključuje ime kategorije izdelka iz tabele »Kategorija« izdelka. V tabeli »Izdelki« lahko ustvarimo izračunani stolpec z imenom Kategorija izdelka, kot je ta:

Naša nova formula »Kategorija izdelka« uporablja funkcijo DAX RELATED za pridobivanje vrednosti iz stolpca »ProductCategoryName« v povezani tabeli »Kategorija izdelkov« in nato vnese te vrednosti za vsak izdelek (vsako vrstico) v tabelo »Izdelek«.

To je odličen primer, kako lahko izračunani stolpec uporabimo za dodajanje fiksne vrednosti za vsako vrstico, ki jo lahko uporabimo pozneje v območju VRSTICE, STOLPCI ali FILTRI vrtilne tabele ali v poročilu Power View.

Ustvarimo še en primer, kjer želimo izračunati stopnjo dobička za naše kategorije izdelkov. To je pogost scenarij, tudi v številnih vadnicah. V podatkovnem modelu imamo tabelo »Prodaja«, ki vsebuje podatke o transakcijah, med tabelo »Prodaja« in tabelo »Kategorija« izdelka pa obstaja povezava. V tabeli Prodaja imamo stolpec z zneski prodaje in drug stolpec s stroški.

Ustvarimo lahko izračunani stolpec, ki izračuna znesek dobička za vsako vrstico, tako da odštejemo vrednosti v stolpcu COGS od vrednosti v stolpcu SalesAmount, kot je ta:

Zdaj lahko ustvarimo vrtilno tabelo in povlečemo polje Kategorija izdelka v STOLPCI in naše novo polje »Dobiček« v območje VREDNOSTI (stolpec v tabeli v orodju PowerPivot je polje na seznamu polj vrtilne tabele). Rezultat je implicitna mera z imenom vsota dobička. To je združena količina vrednosti iz stolpca »dobiček« za vsako od različnih kategorij izdelkov. Naš rezultat izgleda takole:

V tem primeru je dobiček smiseln le kot polje v VALUES. Če bi v območje STOLPCI postavili dobiček, bi bila vrtilna tabela videti takole:

Naše polje »Dobiček« ne vsebuje nobenih uporabnih informacij, če je postavljeno v območja »Stolpci«, »VRSTICE« ali »Filtri«. Smiselna je le kot združena vrednost v območju VREDNOSTI.

Ustvarili smo stolpec z imenom Dobiček, ki izračuna stopnjo dobička za vsako vrstico v tabeli Prodaja. Nato smo dodali dobiček v območje VREDNOSTI v vrtilni tabeli in samodejno ustvarili implicitno mero, kjer se izračuna rezultat za vsako kategorijo izdelkov. Če mislite, da smo dobiček za naše kategorije izdelkov izračunali dvakrat, imate prav. Najprej smo izračunali dobiček za vsako vrstico v tabeli »Prodaja«, nato pa smo dobiček dodali v območje VREDNOSTI, kjer je bil združen za vsako kategorijo izdelkov. Če tudi vi mislite, da nam v resnici ni bilo treba ustvariti izračunanega stolpca »Dobiček«, imate prav tudi. Toda kako potem izračunamo dobiček, ne da bi ustvarili izračunani stolpec Dobiček?

Dobiček, bi bilo res bolje izračunati kot eksplicitno merilo.

Za zdaj bomo stolpec »Dobiček« »Izračunani dobiček« pustili v tabeli »Prodaja« in »Kategorija izdelka« v razdelku »Stolpci« in »Dobiček« v »Vrednosti« v vrtilni tabeli, da bomo primerjali rezultate.

V območju izračuna tabele »Prodaja« bomo ustvarili mero z imenom Skupni dobiček (da se izognemo sporom pri poimenovanju). Na koncu bo prinesel enake rezultate kot prej, vendar brez izračunanega stolpca Dobiček.

Najprej v tabeli »Prodaja« izberemo stolpec »Prodaja« in nato kliknemo »Samodejna vsota«, da ustvarimo eksplicitno mero »Vsota zneska«. Ne pozabite, da je eksplicitna mera tista, ki jo ustvarimo v območju izračuna tabele v dodatku Power Pivot. Enako počnemo za stolpec COGS. Preimenovali jih bomo v skupni znesek prodaje in skupni skupni količinski znesek, da ju bomo lažje prepoznali.

Nato ustvarimo še eno mero s to formulo:

Skupni dobiček:=[Skupni znesek prodaje] - [Skupni stroški prodaje]

Ko spremenimo obliko našega novega merila skupnega dobička v valuto, ga lahko dodamo v vrtilno tabelo.

Naše novo merilo skupnega dobička lahko vrne enake rezultate kot ustvarjanje izračunanega stolpca »Dobiček« in njegovo postavitev v VREDNOSTI. Razlika je v tem, da je naše merilo skupnega dobička veliko učinkovitejše in naredi naš podatkovni model čistejši in vitkejši, ker izračunavamo takrat in samo za polja, ki jih izberemo za vrtilno tabelo. V resnici ne potrebujemo tega izračunanega stolpca Dobiček.

Zakaj je ta zadnji del pomemben? Izračunani stolpci dodajo podatke v podatkovni model, podatki pa zavzamejo pomnilnik. Če osvežimo podatkovni model, so potrebni tudi viri za obdelavo za ponovni izračun vseh vrednosti v stolpcu Dobiček. V resnici nam ni treba uporabljati takšnih virov, ker resnično želimo izračunati dobiček, ko izberemo polja, za katera želimo dobiček v vrtilni tabeli, kot so kategorije izdelkov, regije ali datumi.

Poglejmo še en primer. Takšnega, kjer izračunani stolpec ustvari rezultate, ki so na prvi pogled videti pravilni, vendar...

V tem primeru želimo izračunati zneske prodaje kot odstotek skupne prodaje. V tabeli »Prodaja« ustvarimo izračunani stolpec z imenom »% prodaje«, kot je navedeno:

V naši formuli je navedeno: Za vsako vrstico v tabeli »Prodaja« delite znesek v stolpcu »ZnesekProdaje« s vsoto vseh zneskov v stolpcu »ZnesekProdaje«.

Če ustvarimo vrtilno tabelo in dodamo kategorijo izdelka v STOLPCE ter izberemo naš novi stolpec % prodaje , da ga vnesemo v VREDNOSTI, dobimo skupno vsoto % prodaje za vsako od naših kategorij izdelkov.

V redu. To je zaenkrat videti dobro. Ampak, dodajmo razčlenjevalnik. Dodamo Calendar Year in nato izberemo leto. V tem primeru izberemo 2007. To je tisto, kar dobimo.

Na prvi pogled se to morda še vedno zdi pravilno. Toda naši odstotki bi morali v resnici znašati 100%, ker želimo vedeti odstotek celotne prodaje za vsako od naših kategorij izdelkov za leto 2007. Torej, kaj je šlo narobe?

V stolpcu »% prodaje« je izračunan odstotek za vsako vrstico, ki je vrednost v stolpcu »ZnesekProdaje«, deljena s skupno vsoto vseh vrednosti v stolpcu »ZnesekProdaje«. Vrednosti v izračunanem stolpcu so nespremenljive. So nespremenljiv rezultat za vsako vrstico v tabeli. Ko smo v vrtilno tabelo dodali % prodaje , je bila ta združena kot vsota vseh vrednosti v stolpcu »SalesAmount«. Ta vsota vseh vrednosti v stolpcu »% prodaje« bo vedno 100%.

Izračunani stolpec »% prodaje« lahko izbrišemo, ker nam to ne bo pomagalo. Namesto tega bomo ustvarili merilo, ki pravilno izračuna naš odstotek skupne prodaje, ne glede na uporabljene filtre ali razčlenjevalnike.

Se spomnite mere »TotalSalesAmount«, ki smo jo ustvarili prej, tistega, ki preprosto sešteje stolpec »SalesAmount«? Uporabili smo ga kot argument v merilu skupnega dobička in ga bomo ponovno uporabili kot argument v našem novem izračunanem polju.

Ustvarimo novo mero z naslednjo formulo:

% skupne prodaje:=([Skupni znesek prodaje]) / CALCULATE([Skupni znesek prodaje], ALLSELECTED())

V tej formuli je navedeno: Rezultat delite iz skupne vrednosti »SalesAmount« z vsoto »SalesAmount« brez filtrov stolpcev ali vrstic, ki niso določeni v vrtilni tabeli.

Če zdaj v vrtilno tabelo dodamo nov odstotek skupne prodaje , dobimo:

To izgleda bolje. Zdaj se naš odstotek skupne prodaje za vsako kategorijo izdelkov izračuna kot odstotek skupne prodaje za leto 2007. Če v razčlenjevalniku »Koledarsko leto« izberemo drugo leto ali več kot eno leto, dobimo nove odstotke za naše kategorije izdelkov, vendar je naš skupni seštevek še vedno 100-odstotni. Dodamo lahko tudi druge razčlenjevalnike in filtre. Naše merilo % skupne prodaje bo vedno ustvarilo odstotek skupne prodaje, ne glede na uporabljene razčlenjevalnike ali filtre. Pri merah je rezultat vedno izračunan glede na kontekst, ki ga določajo polja v STOLPCIH in VRSTICAH ter vsi uporabljeni filtri ali razčlenjevalniki. To je moč ukrepov.

Tukaj je nekaj smernic, ki vam bodo v pomoč pri odločanju, ali je izračunani stolpec ali mera primerna za določeno potrebo izračuna:

Uporaba izračunanih stolpcev

• Če želite, da so novi podatki prikazani v VRSTICAH, STOLPCIH ali v FILTRIH v vrtilni tabeli ali na OSI, LEGENDI ali TILE BY v ponazoritvi Power View, morate uporabiti izračunani stolpec. Tako kot običajni stolpci podatkov lahko izračunane stolpce uporabite kot polje na katerem koli območju, in če so številski, jih lahko združite tudi v VREDNOSTI.
• Če želite, da so novi podatki nespremenljiva vrednost za vrstico. Imate na primer datumsko tabelo s stolpcem z datumi in želite še en stolpec, ki vsebuje samo številko meseca. Ustvarite lahko izračunani stolpec, ki izračuna le številko meseca iz datumov v stolpcu Datum. Na primer =MONTH('Datum'[Datum]).
• Če želite tabeli dodati besedilno vrednost za vsako vrstico, uporabite izračunani stolpec. Polj z besedilnimi vrednostmi ni mogoče nikoli združiti v funkciji VALUES. Na primer, =FORMAT('Datum'[Datum],"mmmm") nam pokaže ime meseca za vsak datum v stolpcu Datum v tabeli »Datum«.

Uporabite ukrepe

• Če bo rezultat izračuna vedno odvisen od drugih polj, ki jih izberete v vrtilni tabeli.
• Če morate narediti bolj zapletene izračune, na primer izračunati štetje na podlagi filtra neke vrste ali izračunati letno ali odstopanje, uporabite izračunano polje.
• Če želite čim bolj zmanjšati velikost delovnega zvezka in povečati njegovo učinkovitost delovanja, ustvarite čim več izračunov. V mnogih primerih so lahko vsi vaši izračuni mere, kar znatno zmanjša velikost delovnega zvezka in pospeši čas osveževanja.

Ne pozabite, da ni nič narobe, če ustvarite izračunane stolpce, kot smo to storili s stolpcem »Dobiček«, in ga nato združite v vrtilno tabelo ali poročilo. Pravzaprav je to res dober in enostaven način za učenje in ustvarjanje lastnih izračunov. Ko se vaše razumevanje teh dveh izjemno zmogljivih funkcij dodatka Power Pivot povečuje, boste želeli ustvariti najučinkovitejši in najnatančnejši podatkovni model, ki ga lahko. Upajmo, da bo to, kar ste se naučili tukaj, pomagalo. Obstaja še nekaj drugih res odličnih virov, ki vam lahko pomagajo. Tukaj je le nekaj: Kontekst v formulah DAX, Združevanja v dodatku Power Pivot in Središče za vire DAX. Čeprav je nekoliko naprednejši in usmerjen v računovodske in finančne strokovnjake, je vzorec modeliranja in analize podatkov o dobičku in izgubi z Microsoft Power Pivotom v Excelu naložen z odličnimi primeri modeliranja podatkov in formul.