Kdaj uporabiti izračunane stolpce in izračunana polja

Ko se prvič naučite uporabljati Power Pivot, večina uporabnikov odkrije, da je prava moč na kakšen način v združevanju ali izračunu rezultata. Če podatki vsebuje stolpec s številskimi vrednostmi, jih lahko preprosto združite tako, da jih izberete v vrtilni tabeli ali na seznamu polj funkcije Power View. Ker je številska, se samodejno sešteje, izračuna povprečje, šteje ali katero koli vrsto združevanja, ki ga izberete. To je znano kot implicitna mera. Implicitne mere so odlične za hitro in preprosto združevanje, vendar imajo omejitve in te omejitve je skoraj vedno mogoče premagati s eksplicitne mere in izračunanimi stolpci.

Najprej si opišite primer, v katerem smo uporabili izračunan stolpec za dodajanje nove besedilne vrednosti za vsako vrstico v tabeli z imenom »Izdelek«. Vsaka vrstica v tabeli »Izdelek« vsebuje vse vrste informacij o posameznem izdelku, ki ga prodajamo. Na voljo so stolpci za ime izdelka, barvo, velikost, ceno prodajalca itd. Imamo drugo povezano tabelo z imenom Kategorija izdelka, ki vsebuje stolpec ProductCategoryName. Za vsak izdelek v tabeli »Izdelek« želimo vključiti ime kategorije izdelka iz tabele »Kategorija izdelka«. V tabeli »Izdelek« lahko ustvarite izračunan stolpec z imenom »Kategorija izdelka« tako:

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

To je odličen primer, kako lahko z izračunanimi stolpci dodamo nespremenljivo vrednost za vsako vrstico, ki jo lahko pozneje uporabite v območju VRSTICE, STOLPCI ali FILTRI vrtilne tabele ali poročila Power View.

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

Ustvarimo lahko izračunan stolpec, ki izračuna znesek dobička za vsako vrstico tako, da odštejemo vrednosti v stolpcu COGS od vrednosti v stolpcu »KoličinaProjekta«, kot je prikazano tukaj:

Zdaj lahko ustvarimo vrtilno tabelo in povlečemo polje »Kategorija izdelka« v STOLPCE, naše 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 Sum of Profit. Gre za združeno količino vrednosti iz stolpca z dobičkom za vsako od različnih kategorij izdelkov. Naš rezultat je videti tako:

V tem primeru je dobiček smiselen le kot polje v vrednostih. Če bi v območje STOLPCEV dodali Dobiček, bi bila naša vrtilna tabela videti tako:

Naše polje »Dobiček« ne zagotavlja nobenih uporabnih informacij, ko jih postavite 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 maržo dobička za vsako vrstico v tabeli »Prodaja«. Nato smo v območje VREDNOSTI naše vrtilne tabele dodali Dobiček in samodejno ustvarili implicitno mero, kjer se rezultat izračuna za vsako od kategorij izdelkov. Če mislite, da smo za naše kategorije izdelkov dvakrat izračunali dobiček, ste pravilni. Najprej smo izračunali dobiček za vsako vrstico v tabeli »Prodaja«, nato pa smo v območje VREDNOSTI, kjer je bil združen, dodali Dobiček za vsako od kategorij izdelkov. Če mislite tudi, da vam ni treba ustvariti izračunanega stolpca »Dobiček«, ste prav tako pravilni. Toda kako nato izračunamo dobiček brez ustvarjanja izračunanega stolpca »Dobiček«?

Dobiček bi bilo res bolje izračunati kot eksplicitni ukrep.

Za zdaj bomo pustili naš izračunani stolpec »Dobiček« v tabeli »Prodaja« in »Kategorija izdelka« v stolpcih in dobiček v vrednostih vrtilne tabele, da primerjamo naše rezultate.

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

Najprej v tabeli »Prodaja« izberite stolpec »ZnesekProprojekta« in nato kliknite Samodejna vsota, da ustvarite eksplicitno vsoto mere »ZnesekProprojekte«. Ne pozabite, eksplicitna mera je mera, ki jo ustvarimo v območju izračuna tabele v dodatku Power Pivot. Enako velja za stolpec »COGS«. Preimenovali bomo ti funkciji Total SalesAmount in Total COGS , da ju boste lažje prepoznali.

Nato s to formulo ustvarimo še eno mero:

Skupni dobiček:=[ Skupni znesek prodaje] - [Skupni SKUPNI ZNESEK PRODAJE]

Ko spremenite obliko naše nove mere za skupni dobiček v valuto, jo lahko dodamo v vrtilno tabelo.

Naša nova mera za skupni dobiček vrne enake rezultate kot ustvarjanje izračunanega stolpca »Dobiček« in jo nato vnesete v vrednosti. Razlika je v tem, da je mera »Skupni dobiček« veliko učinkovitejša in je naš podatkovni model čistejši in vitkejši, saj računamo v času in le za polja, ki jih izberemo za vrtilno tabelo. Ne potrebujemo tega izračunanega stolpca »Dobiček«.

Zakaj je ta zadnji del pomemben? Izračunani stolpci dodajajo podatke v podatkovni model, podatki pa zavlacajo v pomnilnik. Če osvežimo podatkovni model, je za preračunavanje vseh vrednosti v stolpcu »Dobiček« potreben tudi obdelovanj virov. V resnici vam ni treba vzeti podobnih sredstev, saj želimo resnično izračunati svoj dobiček, ko v vrtilni tabeli izberemo polja, za katera želimo izračunati dobiček, na primer kategorije izdelkov, regijo ali datume.

O poglejmo še en primer. Tisti, 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« smo ustvarili izračunan stolpec z imenom % prodaje, in to:

Naša formula določa: Za vsako vrstico v tabeli »Prodaja« delite znesek v stolpcu »KoličinaProprojekta« s skupno vsoto vseh zneskov v stolpcu »KoličinaProprojekta«.

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

V redu. To je videti dobro do sedaj. Toda dodajmo razčlenjevalnik. Dodali bomo Koledarsko leto in nato izbrali leto. V tem primeru izberemo 2007. To je tisto, kar nam je na voljo.

Na prvi pogled je lahko to še vedno videti pravilno. Toda naši odstotki bi morali biti res skupaj 100 %, ker želimo poznati odstotek skupne prodaje za vsako od naših kategorij izdelkov za leto 2007. Kaj je šlo narobe?

Our % of Sales column calculated a percent for each row that is the value in the SalesAmount column divided by the sum total of all values in the SalesAmount column. Vrednosti v izračunanem stolpcu so nespremenljive. To je 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 »ZnesekProjekte«. Ta vsota vseh vrednosti v stolpcu % prodaje bo vedno 100 %.

Naš % izračunanega stolpca »Prodaja« lahko izbrišemo, ker nam ne bo pomagal. Namesto tega bomo ustvarili mero, ki bo pravilno izračunala naš odstotek skupne prodaje, ne glede na morebitne filtre ali razčlenjevalnike.

Se spomnite mere TotalSalesAmount, ki smo jo ustvarili prej, mere, ki preprosto sešteje stolpec »KoličinaProdaje«? To smo uporabili kot argument v okviru mere »Skupni dobiček«, ki jo bomo znova uporabili kot argument v novem izračunanem polju.

Ustvarili smo novo mero s to formulo:

% skupne prodaje:=([Skupna količinaProprojekta]) / CALCULATE([Skupni znesekProprojekta], ALLSELECTED())

Ta formula določa: Razdelite rezultat iz možnosti Total SalesAmount z vsoto vsote »KoličinaProprojekta« brez filtrov stolpcev ali vrstic, razen tistih, ki so določeni v vrtilni tabeli.

Če v vrtilno tabelo dodamo naš novi % skupne prodaje, imamo:

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 CalendarYear izberemo drugo leto ali več kot eno leto, pridobimo nove odstotke za kategorije izdelkov, vendar je skupna vsota še vedno 100 %. Dodate lahko tudi druge razčlenjevalnike in filtre. Mera % skupne prodaje vedno ustvari odstotek skupne prodaje, ne glede na morebitne uporabljene razčlenjevalnike ali filtre. Z merami je rezultat vedno izračunan glede na kontekst, ki ga določajo polja v stolpcih in vrsticah ter kateri koli 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 prava za določeno izračunavanje:

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, RAZPOŠLI PO ponazoritve v funkciji Power View, morate uporabiti izračunan stolpec. Tako kot navadne stolpce s podatki, lahko tudi izračunane stolpce uporabite kot polje v katerem koli območju in če so številski, jih lahko tudi v vrednostih združite.

• Če želite, da so novi podatki nespremenljiva vrednost za vrstico. Imate na primer datumsko tabelo s stolpcem z datumi in želite še en stolpec, v katerem je le številka meseca. Ustvarite lahko izračunan stolpec, ki izračuna samo številko meseca iz datumov v stolpcu Datum. Primer: =MONTH('Datum'[Datum]).

• Če želite dodati besedilno vrednost za vsako vrstico v tabeli, uporabite izračunan stolpec. Polj z besedilnimi vrednostmi nikoli ni mogoče združiti v vrednostih. =FORMAT('Datum'[Datum],"mmmm") nam na primer da ime meseca za vsak datum v stolpcu Datum v tabeli Datum.

Mere uporabe

• Č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 število na podlagi filtra neke vrste ali izračunati letno vrednost ali varianco, uporabite izračunano polje.

• Če želite ohraniti minimalno velikost delovnega zvezka in maksimirati njegovo učinkovitost delovanja, ustvarite čim več izračunov, kolikor je mogoče. V mnogih primerih so lahko vsi izračuni mere, s čimer se znatno zmanjša velikost delovnega zvezka in skrajša čas osveževanja.

Upoštevajte, da z ustvarjanjem izračunanih stolpcev, kot smo jih ustvarili v stolpcu »Dobiček«, ni nič narobe in jih nato združete v vrtilni tabeli ali poročilu. To je pravzaprav res dober in preprost način za spoznanje in ustvarjanje lastnih izračunov. Ko boste razumeli ti dve izjemno zmogljivi funkciji dodatka Power Pivot, boste želeli ustvariti najbolj učinkovit in natančen podatkovni model, ki ga lahko. Upamo, da vam bo to, kar ste se tu naučili, v pomoč. Obstaja še nekaj res odličnih virov, ki vam lahko pomagajo. Tukaj je le nekaj: Kontekst v formulah jezika DAX, Združevanja v dodatku Power Pivot in središče za vire JEZIKA DAX. Čeprav je to nekoliko naprednejše in usmerjeno k računovodskim in finančnim strokovnjakom, je modeliranje in analiza podatkov o dobičku in izgubi z dodatkom Microsoft Power Pivot v Excelovem vzorcu naloženo z odličnimi primeri modeliranja podatkov in formul.