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:

Izračunata kolona za kategorije proizvoda

Naša nova formula za kategoriju proizvoda koristi funkciju RELATED DAX da bi uzela vrednosti iz kolone „ImeKategorijeProizvoda“ iz srodne tabele „Kategorija proizvoda“ i zatim te vrednosti unela za svaki proizvod (svaki red) u tabeli „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:

Kolona sa dobiti u tabeli programskog dodatka Power Pivot

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:

Jednostavna izvedena tabela

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:

Izvedena tabela bez korisnih vrednosti

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.

Dugme „Automatski zbir“ u programskom dodatku Power Pivot

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

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

Napomena :  Formulu možemo da napišemo i u obliku: Ukupni profit:=SUM([IznosProdaje]) - SUM([CenaProdateRobe]). Međutim, kada napravimo zasebne mere „Ukupni IznosProdaje“ i „Ukupna CenaProdateRobe“, možemo da ih koristimo i u izvedenoj tabeli i kao argumente u raznim drugim formulama mere.

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

Izvedena tabela

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:

Izračunata kolona % prodaje

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.

Izvedena tabela koja prikazuje zbir % prodaje za kategorije proizvoda

U redu. Za sada sve izgleda dobro. Hajde sad da dodamo modul za sečenje. Dodaćemo modul za sečenje „Kalendarska godina“ i zatim izabrati godinu. U ovom slučaju biramo 2007. godinu. Evo šta dobijamo.

Neispravan rezultat zbira % prodaje u izvedenoj tabeli

Na prvi pogled vam se ovo možda i dalje čini ispravnim. Međutim, konačni zbir treba da bude 100% jer želimo da utvrdimo procenat ukupne prodaje za svaku kategoriju proizvoda iz 2007. godine. U čemu smo onda pogrešili?

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%.

Savet :  Obavezno pročitajte članak Kontekst u DAX formulama. On pomaže u razumevanju konteksta na nivou reda i kontekstu filtera, a to je ono čime se bavimo u ovom članku.

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.

Savet :  Eksplicitne mere kao što su „Ukupni IznosProdaje“ i „Ukupna CenaProdateRobe“ nisu korisne samo u izvedenoj tabeli ili izveštaju, već i kao argumenti u drugim merama kada je neophodno da rezultat bude u vidu argumenta. One čine formule efikasnijim i lakšim za čitanje. Takođe su dobre u modelima podataka.

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.

Savet :  Obavezno pročitajte više o funkcijama CALCULATE i ALLSELECTED u referenci za DAX funkciju.

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

Ispravan rezultat zbira % prodaje u izvedenoj tabeli

To izgleda bolje. Sada se mera % ukupne prodaje za svaku kategoriju proizvoda izračunava kao procenat ukupne prodaje za 2007. godinu. Ako u modulu za sečenje „Kalendarska godina“ izaberemo neku drugu godinu ili više njih, dobićemo nove procente za kategorije proizvoda, ali će konačni zbir i dalje biti 100%. Takođe možemo da dodamo nove filtere i module za sečenje. Mera „% ukupne prodaje“ uvek kao rezultat daje procenat ukupne prodaje, bez obzira na primenjene filtere ili module za sečenje. Kod mera se rezultat uvek izračunava na osnovu konteksta koji određuju polja u oblastima „KOLONE“ i „REDOVI“ i primenjeni filteri i moduli za sečenje. U tome se ogleda 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

  • Morate da koristite izračunatu kolonu ako želite da se novi podaci pojave u oblastima „REDOVI“, „KOLONE“ ili „FILTERI“ u izvedenoj tabeli ili u oblastima „OSA“, „LEGENDA“ ili „REDOSLED PLOČICA“ u Power View vizuelizaciji. 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. Uzorak Modelovanje i analiza podataka o profitu i gubitku pomoću programskog dodatka Microsoft PowerPivot u programu Excel pun je izuzetnih primera modela podataka i formula, iako je malo napredniji i usmeren ka stručnjacima u oblasti računovodstva i finansija.

Unapredite veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala vam na povratnim informacijama!

Hvala za povratne informacije! Zvuči da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×