Kdaj uporabiti izračunane stolpce in izračunana polja

Ko se večina uporabnikov prvič nauči uporabljati Power Pivot, odkrije, da je prava zmogljivost v združevanju ali računanju rezultata. Če vaši podatki vsebujejo stolpec s številskimi vrednostmi, jih lahko preprosto združite tako, da jih izberete v vrtilni tabeli ali na seznamu polj funkcije Power View. Ker gre za število, bo po naravi samodejno sešteta, izračunana povprečje, prešteta ali katera koli vrsta združevanja, ki jo izberete. To imenujemo implicitna mera. Implicitne mere so odlične za hitro in preprosto združevanje, vendar imajo svoje omejitve in te omejitve je skoraj vedno mogoče premagati z eksplicitnimi merami 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«. V vsaki vrstici tabele izdelkov so najrazličnejši podatki o vsakem izdelku, ki ga prodajamo. Imamo stolpce za ime izdelka, barvo, velikost, prodajno ceno itd. Imamo še eno povezano tabelo, imenovano Kategorija izdelka, ki vsebuje stolpec ImeKategorijeIzdelka. Želimo, da vsak izdelek v tabeli izdelkov vključuje ime kategorije izdelka iz tabele »Kategorija izdelka«. V tabeli izdelkov lahko ustvarite izračunan stolpec, imenovan »Kategorija izdelkov«, in sicer tako:

Naša nova formula za kategorijo izdelka uporablja povezano funkcijo DAX za pridobivanje vrednosti iz stolpca »ImeKategorijeIzdelka« v sorodni tabeli »Kategorija izdelka«, nato pa vnese te vrednosti za vsak izdelek (vsako vrstico) v tabelo izdelkov.

To je odličen primer, kako lahko z izračunanim stolpcem dodamo nespremenljivo vrednost 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 kategorije izdelkov. To je pogost scenarij tudi v številnih vadnicah. V podatkovnem modelu imamo tabelo »Prodaja«, ki vsebuje podatke o transakciji, med tabelama »Prodaja« in »Kategorija izdelka« pa obstaja relacija. V tabeli »Prodaja« je 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šteje vrednosti v stolpcu COGS od vrednosti v stolpcu »SalesAmount«, in sicer tako:

Zdaj lahko ustvarimo vrtilno tabelo in povlečemo polje »Kategorija izdelka« v »STOLPCI«, novo polje »Dobiček« pa v območje »VREDNOSTI« (stolpec v tabeli v orodju PowerPivot je polje na seznamu polj vrtilne tabele). Rezultat je implicitna mera, imenovana vsota dobička. Gre za združen znesek vrednosti iz stolpca »Dobiček« za vsako od različnih kategorij izdelkov. Naš rezultat je videti tako:

V tem primeru je vrednost »Dobiček« smiselna le kot polje v funkciji »VREDNOSTI«. Če bi želeli vrednost »Dobiček« vstaviti v območje STOLPCI, bi bila naša vrtilna tabela videti tako:

V polju »Dobiček« ni uporabnih informacij, če ga vstavite v območja »STOLPCI«, »VRSTICE« ali »FILTRI«. Smiselno 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 v območje VREDNOSTI vrtilne tabele dodali vrednost »Dobiček«, s čimer smo samodejno ustvarili implicitno mero, kjer je rezultat izračunan za vsako kategorijo izdelka. Če menite, 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 izdelka. Če tudi vi menite, da nismo morali ustvariti izračunanega stolpca »Dobiček«, imate prav. Toda kako potem izračunamo dobiček, ne da bi ustvarili stolpec z izračunanim dobičkom?

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

Za zdaj bomo stolpec »Izračunan dobiček« pustili v tabeli »Prodaja«, »Kategorija izdelka« pa v »STOLPCIH«, »Dobiček« pa »VREDNOSTI« vrtilne tabele, da bomo lahko primerjali naše rezultate.

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

V tabeli »Prodaja« najprej izberemo stolpec »ZnesekProdaje« in nato kliknemo »Samodejna vsota«, da ustvarimo eksplicitno vsoto mere »ZnesekProdaje «. Ne pozabite, da je eksplicitna mera tista, ki jo ustvarimo v območju za izračun tabele v orodju PowerPivot. Enako naredimo za stolpec COGS. Preimenovali bomo te skupne prodajne zneske in skupne količinske prodaje, da jih bo lažje prepoznati.

Nato ustvarimo novo mero s to formulo:

Total Profit:=[Total SalesAmount] - [Total COGS]

Ko spremenite obliko nove mere skupnega dobička v valuto, jo lahko dodamo v vrtilno tabelo.

Nova mera za skupni dobiček vrne iste rezultate kot pri ustvarjanju izračunanega stolpca »Dobiček« in vstavljanju vrednosti v VREDNOSTI. Razlika je v tem, da je mera skupnega dobička veliko učinkovitejša, zaradi česar je podatkovni model čistejši in vitkejši, saj računamo takrat in samo za polja, ki jih izberemo za vrtilno tabelo. V resnici ne potrebujemo stolpca z izračunanim dobičkom.

Zakaj je ta zadnji del pomemben? Izračunani stolpci dodajo podatke v podatkovni model, podatki pa zavzemajo pomnilnik. Če osvežimo podatkovni model, potrebujete tudi vire za obdelavo za preračun vseh vrednosti v stolpcu »Dobiček«. Takšnih virov nam pravzaprav ni treba uporabljati, ker želimo izračunati dobiček, ko v vrtilni tabeli izbirate polja za dobiček, kot so kategorije izdelkov, regija ali datumi.

Oglejmo si še en primer. Takšnega, kjer izračunan 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čunan stolpec, imenovan »% od prodaje«, in sicer tako:

Naša formula navaja: Za vsako vrstico v tabeli »Prodaja« delite znesek v stolpcu »ProdajaZnesek« s SUM vsote vseh zneskov v stolpcu »ZnesekProdaje«.

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

V redu. To je zaenkrat videti dobro. Dodajmo pa še razčlenjevalnik. Dodamo Calendar Year in nato izberemo leto. V tem primeru smo izbrali 2007. To je tisto, kar dobimo.

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

Naš stolpec »% prodaje« je izračunal odstotek za vsako vrstico, ki je vrednost v stolpcu »SalesAmount«, deljeno s skupno vsoto vseh vrednosti v stolpcu »SalesAmount«. Vrednosti v izračunanem stolpcu so nespremenljive. So nespremenljiv rezultat za vsako vrstico v tabeli. Ko smo v vrtilno tabelo dodali % prodaje , je bil ta združen kot vsota vseh vrednosti v stolpcu »SalesAmount«. Ta vsota vseh vrednosti v stolpcu »% prodaje« bo vedno 100 %.

Stolpec »% od prodaje« lahko izbrišemo, ker nam to ne bo pomagalo. Namesto tega bomo ustvarili mero, ki bo pravilno izračunala odstotek skupne prodaje, ne glede na uporabljene filtre ali razčlenjevalnike.

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

Ustvarimo novo mero s to formulo:

% skupne prodaje:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

Ta formula navaja: Deli rezultat iz skupne količine prodaje z vsoto skupne količine prodaje brez filtrov stolpcev ali vrstic, razen tistih, ki so določeni v vrtilni tabeli.

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

To je videti bolje. Zdaj je naš % skupne prodaje za vsako kategorijo izdelkov izračunan 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 kategorije izdelkov, vendar je naša skupna vsota še vedno 100 %. Dodamo lahko tudi druge razčlenjevalnike in filtre. Naša mera % skupne prodaje vedno ustvari 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 in morebitni uporabljeni filtri ali razčlenjevalniki. To je moč ukrepov.

Oglejte si nekaj navodil za 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 se novi podatki prikažejo v funkcijah VRSTICE, STOLPCIH ali v funkciji FILTRI v vrtilni tabeli, na OSI, v LEGENDI ali v ponazoritvi funkcije Power View izberite razpostavitev, morate uporabiti izračunani stolpec. Tako kot navadne stolpce s podatki lahko tudi izračunane stolpce uporabite kot polje na katerem koli območju in če so številski, jih je mogoče združiti v VREDNOSTIH.
• Če želite novi podatki obdržati nespremenljivo vrednost za vrstico. Recimo, da imate datumsko tabelo s stolpcem z datumi, želite pa drug stolpec, v katerem bi bila samo številka meseca. Ustvarite lahko izračunani stolpec, ki izračuna le številko meseca iz datumov v stolpcu »Datum«. Na primer =MONTH('datum'[datum]).
• Če želite dodati besedilno vrednost za vsako vrstico v tabeli, uporabite izračunani stolpec. Polj z besedilnimi vrednostmi nikoli ni mogoče združiti v VREDNOSTI. Tako nam na primer =FORMAT('datum'[datum],"mmmm") dodeli ime meseca za vsak datum v stolpcu »Datum« v tabeli »Datum«.

Uporaba mer

• Če bo rezultat izračuna vedno odvisen od drugih polj, ki jih izberete v vrtilni tabeli.
• Če morate izvesti bolj zapletene izračune, na primer izračunati seštevek na podlagi neke vrste filtra ali večletno obdobje ali odmik, uporabite izračunano polje.
• Če želite omejiti velikost delovnega zvezka na minimalno in maksimirati njegovo učinkovitost, ustvarite čim več izračunov in merskih enot. V mnogih primerih so vsi izračuni lahko mere, ki znatno zmanjšajo velikost delovnega zvezka in pospešijo čas osveževanja.

Upoštevajte, da ni nič narobe, če ustvarite izračunane stolpce, kot smo to storili v stolpcu »Dobiček«, in ga nato združite v vrtilni tabeli ali poročilu. Pravzaprav je to zelo dober in preprost način za učenje in ustvarjanje lastnih izračunov. Z vse boljšim razumevanjem teh dveh izjemno zmogljivih funkcij dodatka Power Pivot boste želeli ustvariti najučinkovitejši in najnatančnejši podatkovni model. Upamo, da bo to, kar ste se naučili tukaj, pomagalo. Obstaja še nekaj drugih, res odličnih virov, ki so vam lahko v pomoč. Tukaj je le nekaj: kontekst v formulah jezika DAX, združevanja v dodatku Power Pivot in središče virov jezika DAX. Čeprav je model modeliranja in analize podatkov o dobičku in izgubi z orodjem Microsoft Power Pivot v Excelu nekoliko naprednejši in namenjen računovodstvenim in finančnim strokovnjakom, vsebuje veliko odličnih primerov modeliranja podatkov in formul.