Kad se koriste izračunate kolone i izračunata polja

Kada prvi put uči kako se koristi Power Pivot, većina korisnika otkrije da njegova prava moć leži u prikupljanju ili izračunavanju rezultata na razne načine. Ako vaši podaci sadrže kolonu sa numeričkim vrednostima, možete lako da ih prikupite tako što ćete ih izabrati u izvedenoj tabeli ili na Power View listi polja. Numeričke vrednosti se zbog njihove prirode automatski sabiraju, broje ili se izračunava njihov prosek, u zavisnosti od tipa agregacije koji ste izabrali. Taj postupak se naziva „implicitna mera“. Implicitne mere su sjajne za brzu i jednostavnu agregaciju, međutim, imaju granice koje u većini slučajeva možete da prevaziđete korišćenjem eksplicitnih mera i izračunatih kolona.

Hajde prvo da pogledamo primer u kom koristimo izračunatu kolonu za dodavanje nove tekstualne vrednosti za svaki red u tabelu pod nazivom „Proizvod“. Svaki red u tabeli „Proizvod“ sadrži razne informacije o svakom proizvodu koji prodajemo. U tabeli imamo kolone za ime proizvoda, boju, veličinu, prodajnu cenu itd. Imamo još jednu srodnu tabelu pod nazivom „Kategorija proizvoda“ koja sadrži kolonu „ImeKategorijeProizvoda“. Ono što želimo je da svaki proizvod u tabeli „Proizvod“ uključuje ime kategorije proizvoda iz tabele „Kategorija proizvoda“. U tabeli „Proizvod“ možemo da napravimo izračunatu kolonu pod nazivom „Kategorija proizvoda“ kao što je ova:

Naša nova formula "Kategorija proizvoda" koristi funkciju RELATED DAX za preuzimanje vrednosti iz kolone "ImeKategorije Proizvoda" u povezanoj tabeli "Kategorija proizvoda", a zatim unosi te vrednosti za svaki proizvod (svaki red) u tabelu "Proizvod".

Ovo je dobar primer načina na koji možemo da koristimo izračunatu kolonu za dodavanje fiksne vrednosti za svaki red, a tu vrednost kasnije možemo da koristimo u oblastima „REDOVI“, „KOLONE“ ili „FILTERI“ u izvedenoj tabeli ili Power View izveštaju.

Hajde da napravimo još jedan primer tabele u kojoj želimo da izračunamo stopu profita za kategorije proizvoda. Ovaj primer je veoma uobičajen i pojavljuje se u mnogim uputstvima. U modelu podataka koji sadrži podatke o transakcijama imamo tabelu „Prodaja“ i relaciju između tabele „Prodaja“ i tabele „Kategorija proizvoda“. U tabeli „Prodaja“ imamo kolonu koja sadrži iznos prodaje i još jednu kolonu koja sadrži troškove.

Možemo da napravimo izračunatu kolonu koja će izračunati iznos dobiti za svaki red, tako što će od vrednosti u koloni „IznosProdaje“ oduzeti vrednosti u koloni „CenaProdateRobe“ kao na sledećoj slici:

Sada možemo da napravimo izvedenu tabelu i prevučemo polje „Kategorija proizvoda“ u oblast „KOLONE“, a novo polje „Profit“ u oblast „VREDNOSTI“ (ono što je kolona u tabeli u programskom dodatku Power Pivot jeste polje na listi polja u izvedenoj tabeli). Kao rezultat dobijamo implicitnu meru pod nazivom Zbir profita. To je ukupna količina vrednosti iz kolone „Profit“ za svaku različitu kategoriju proizvoda. Rezultat izgleda ovako:

U ovom slučaju je logično jedino da „Profit“ bude polje u oblasti „VREDNOSTI“. Ako bismo polje „Profit“ stavili u oblast „KOLONE“, naša izvedena tabela bi izgledala ovako:

Polje „Profit“ neće pružati korisne informacije ako ga stavimo u oblasti „KOLONE“, „REDOVI“ ili „FILTERI“. Ono ima smisla jedino kao polje ukupnih vrednosti u oblasti „VREDNOSTI“.

Ono što smo uradili je da smo napravili kolonu pod nazivom „Profit“ koja izračunava stopu profita za svaki red u tabeli „Prodaja“. Zatim smo kolonu (polje) „Profit“ dodali u oblast „VREDNOSTI“ u izvedenoj tabeli i time automatski napravili implicitnu meru pomoću koje se rezultat izračunava za svaku kategoriju proizvoda. Ako mislite da smo zapravo dvaput izračunali profit za kategorije proizvoda, u pravu ste. Prvo smo izračunali profit za svaki red u tabeli „Prodaja“, a zatim smo polje „Profit“ dodali u oblast „VREDNOSTI“ gde je izračunata ukupna vrednost za svaku kategoriju proizvoda. Ako takođe smatrate da nije bilo potrebe da napravimo izračunatu kolonu „Profit“, opet ste u pravu. Međutim, kako onda da izračunamo profit bez pravljenja izračunate kolone „Profit“?

Profit bi zapravo bolje bilo izračunati u obliku eksplicitne mere.

Za sada ćemo ostaviti izračunatu kolonu „Profit“ u tabeli „Prodaja“, polje „Kategorija proizvoda“ u oblasti „KOLONE“ i polje „Profit“ u oblasti „VREDNOSTI“ u izvedenoj tabeli kako bismo mogli da uporedimo rezultate.

U oblasti izračunavanja u tabeli „Prodaja“ napravićemo meru pod nazivom Ukupni profit(da bismo izbegli mešanje imena). Na kraju ćemo dobiti isti rezultat kao i ranije, samo bez izračunate kolone „Profit“.

Prvo ćemo u tabeli „Prodaja“ izabrati kolonu „IznosProdaje“ pa kliknuti na funkciju „Automatski zbir“ da bismo napravili eksplicitnu meru Zbir iznosa prodaje. Zapamtite, eksplicitna mera je ona koju pravimo u oblasti izračunavanja tabele u programskom dodatku Power Pivot. Isto to radimo i za kolonu „CenaProdateRobe“. Daćemo ovim kolonama nova imena Ukupni IznosProdaje i Ukupna CenaProdateRobe da bismo ih lakše identifikovali.

Zatim ćemo napraviti novu meru pomoću sledeće formule:

Ukupni profit:=[ Ukupni IznosProdaje] - [Ukupna CenaProdateRobe]

Kada format mere „Ukupni profit“ promenimo u valutu, meru možemo da dodamo u izvedenu tabelu.

Videćete da se pomoću nove mere „Ukupni profit“ dobija isti rezultat kao i pravljenjem izračunate kolone „Profit“ koja se stavlja u oblast „VREDNOSTI“. Razlika je u tome što je mera „Ukupni profit“ efikasnija i čini model podataka prostijim zato što izračunavanje radimo na licu mesta i samo za ona polja koja izaberemo za izvedenu tabelu. Ipak nam nije zaista bila potrebna izračunata kolona „Profit“.

Zašto je važan ovaj poslednji deo? Izračunate kolone dodaju podatke u model podataka, a ti dodati podaci zauzimaju prostor u memoriji. Kada osvežavamo model podataka, potrebni su nam i resursi za obradu kako bi se ponovo izračunale sve vrednosti u koloni „Profit“. Ovakvi resursi nam zapravo nisu neophodni jer želimo da izračunamo profit za izabrana polja u izvedenoj tabeli, kao što su polja za kategorije proizvoda, region ili datume.

Hajde da pogledamo još jedan primer. To je primer u kom izračunata kolona vraća rezultat koji na prvi pogled deluje tačno, međutim...

U ovom primeru želimo da izračunamo iznos prodaje kao procenat ukupne prodaje. Napravili smo izračunatu kolonu pod nazivom % prodaje u tabeli „Prodaja“, kao na sledećoj slici:

Naša formula glasi: Za svaki red u tabeli „Prodaja“ podeli iznos u koloni „IznosProdaje“ ukupnim zbirom svih iznosa iz kolone „IznosProdaje“.

Ako napravimo izvedenu tabelu, dodamo polje „Kategorija proizvoda“ u oblast „KOLONE“, izaberemo novu kolonu % prodaje i stavimo je u oblast „VREDNOSTI“, dobićemo ukupni zbir vrednosti iz kolone „% prodaje“ za svaku kategoriju proizvoda.

U redu. Ovo za sada izgleda dobro. Ali, hajde da dodamo modul za sečenje. Dodaćemo Kalendarsku godinu, a zatim izabrati godinu. U ovom slučaju, biramo 2007. Ovo je ono љto dobijamo.

Na prvi pogled ovo možda i dalje izgleda tačno. Međutim, procenti bi trebalo da iznose 100%, jer želimo da znamo procenat ukupne prodaje za svaku kategoriju proizvoda za 2007. Љta je poљlo po zlu?

Kolona „% prodaje“ izračunala je procenat za svaki red tako što je vrednost iz kolone „IznosProdaje“ podeljena ukupnim zbirom svih vrednosti iz kolone „IznosProdaje“. Vrednosti u izračunatoj koloni su fiksne. One predstavljaju nepromenljivi rezultat za svaki red u tabeli. Kada smo u izvedenu tabelu dodali kolonu % prodaje, vrednosti iz te kolone su prikupljene u zbir svih vrednosti u koloni „IznosProdaje“. Zbir svih vrednosti iz kolone „% prodaje“ uvek će biti 100%.

Možemo da izbrišemo izračunatu kolonu „% prodaje“ jer nam neće pomoći. Umesto nje ćemo napraviti meru koja pravilno izračunava procenat ukupne prodaje, bez obzira na primenjene filtere ili module za sečenje.

Sećate se mere „Ukupni IznosProdaje“ koju smo ranije napravili, one koja jednostavno sabira vrednosti iz kolone „IznosProdaje“? Koristili smo je kao argument za meru „Ukupni profit“ i koristićemo je ponovo kao argument za novo izračunato polje.

Napravićemo novu meru pomoću sledeće formule:

% ukupne prodaje:=([Ukupni IznosProdaje]) / CALCULATE([Ukupni IznosProdaje], ALLSELECTED())

Ta formula glasi: Podeli rezultat iz kolone „Ukupni IznosProdaje“ ukupnim zbirom vrednosti iz kolone „IznosProdaje“ primenjujući samo one filtere koje smo definisali u izvedenoj tabeli, a ne ostale filtere za kolonu ili red.

Ako sada dodamo novu meru % ukupne prodaje u izvedenu tabelu, dobićemo sledeće:

To izgleda bolje. Sada se % ukupne prodaje za svaku kategoriju proizvoda izračunava kao procenat ukupne prodaje za 2007. godinu. Ako izaberemo drugu godinu ili više od jedne godine u modulu za sečenje Godina kalendara, dobijamo nove procente za kategorije proizvoda, ali sveukupna vrednost je i dalje 100%. Možemo da dodamo i druge module za sečenje i filtere. Mera % ukupne prodaje uvek daje procenat ukupne prodaje bez obzira na primenjene module za sečenje ili filtere. Pomoću mera, rezultat se uvek izračunava u skladu sa kontekstom koji određuju polja u oblastima KOLONE i REDOVI, kao i pomoću filtera ili modula za sečenje koji su primenjeni. Ovo je moж mera.

Evo nekoliko smernica koje će vam pomoći da odlučite da li izračunate kolone ili mere odgovaraju određenom izračunavanju:

Koristite izračunate kolone

• Ako želite da se novi podaci pojave u redovima, KOLONAMA ili u filterima u izvedenoj tabeli ili na OSI, LEGENDI ili, PLOČICU BY u Power View vizuelizaciji, morate da koristite izračunatu kolonu. Baš kao i standardne kolone podataka, izračunate kolone mogu da se koriste kao polja u svim oblastima, a mogu i da se agregiraju u oblasti „VREDNOSTI“ ako su numeričke.

• Ako želite da novi podaci budu fiksne vrednosti za neki red. Na primer, imate tabelu datuma sa kolonama sa datumima i želite da napravite drugu kolonu koja sadrži samo broj meseca. Možete da napravite izračunatu kolonu koja će izračunati samo broj meseca iz datuma u koloni „Datum“. Na primer, =MONTH(‘Date’[Datum]).

• Koristite izračunatu kolonu ako želite da dodate tekstualnu vrednost u svaki red tabele. Polja sa tekstualnim vrednostima nikada nije moguće agregirati u oblasti „VREDNOSTI“. Na primer, formula =FORMAT('Date'[Datum],"mmmm") kao rezultat daje ime meseca za svaki datum u koloni „Datum“ u tabeli „Datum“.

Koristite mere

• Ako rezultat izračunavanja uvek zavisi od drugih polja koja izaberete u izvedenoj tabeli.

• Koristite izračunato polje ako treba da uradite složenija izračunavanja, kao što su izračunavanja broja na osnovu nekog filtera, godišnje izračunavanje ili odstupanje.

• Ako želite da veličina radne sveske bude minimalna, a njena efikasnost maksimalna, što više izračunavanja uradite u obliku mera. U većini slučajeva je moguće da izračunavanja budu u obliku mera, što značajno smanjuje veličinu radne sveske i ubrzava vreme njenog osvežavanja.

Imajte na umu da je pravljenje izračunatih kolona i njihovo agregiranje u izvedenoj tabeli ili izveštaju sasvim u redu, mi smo to i uradili sa kolonom „Profit“. To je zapravo veoma dobar i lak način da naučite kako da napravite sopstvena izračunavanja. Kako bude raslo vaše znanje o ove dve izuzetno moćne funkcije programskog dodatka Power Pivot, tako će rasti i vaša želja da napravite najefikasniji i najprecizniji mogući model podataka. Nadamo se da će vam pomoći ono što ste naučili iz ovog članka. Dostupni su još neki zaista sjajni resursi koji vam mogu biti od pomoći. Evo samo nekoliko njih: Kontekst u DAX formulama, Agregacije u programskom dodatku Power Pivot i Centar za DAX resurse. Iako je malo napredniji i usmeren ka stručnjacima u oblasti računovodstva i finansija, modelovanje i analiza podataka o profitu i gubitku uz Microsoft Power Pivot u programu Excel učitava se sa sjajnim primerima modelovanja podataka i formula.