Kada se koriste izračunati stupci i izračunata polja

Dok se upoznaje s dodatkom Power Pivot, većina korisnika otkrije da je njegova prava snaga u agregaciji ili izračunavanju rezultata na neki način. Ako se u podacima nalazi stupac s numeričkim vrijednostima, možete ih brzo agregirati tako da ih odaberete u zaokretnoj tablici ili na popisu polja dodatka Power View. Budući da je riječ o numeričkim vrijednostima, one će se automatski zbrojiti, izračunat će se njihov prosjek, prebrojit će se ili će se na njima izvršiti agregacija bilo koje vrste koju odaberete. To je poznato kao implicitna mjera. Implicitne mjere odlične su za brzu i jednostavniju agregaciju, ali imaju ograničenja. No ta se ograničenja gotovo uvijek mogu zaobići pomoću eksplicitnih mjera i izračunatih stupaca.

Pogledajmo najprije primjer u kojem se pomoću izračunatog stupca dodaje nova tekstna vrijednost za svaki redak u tablici pod nazivom Proizvodi. Svaki redak u tablici Proizvodi sadrži razne podatke o svakom proizvodu koji prodajemo. Tablica sadrži stupce Naziv proizvoda, Boja, Veličina, Nabavna cijena itd. Imamo još jednu povezanu tablicu pod nazivom Kategorije proizvoda, koja sadrži stupac NazivKategorijeProizvoda. Želimo da svaki proizvod u tablici Proizvodi sadrži naziv kategorije proizvoda iz tablice Kategorije proizvoda. U tablici Proizvodi možemo stvoriti izračunati stupac pod nazivom Kategorija proizvoda, ovako:

Stavka koju treba pregledati s dvije žute trake za poruku.

Naša nova formula za kategoriju proizvoda pomoću DAX funkcije RELATED dohvaća vrijednosti iz stupca NazivKategorijeProizvoda u povezanoj tablici Kategorije proizvoda, a zatim te vrijednosti unosi za svaki proizvod (svaki redak) u tablici Proizvodi.

To je odličan primjer korištenja izračunatog stupaca za dodavanje fiksne vrijednosti za svaki redak koju poslije možemo koristiti u području RECI, STUPCI ili FILTRI zaokretne tablice ili izvješća dodatka Power View.

Prijeđimo na sljedeći primjer, u kojem ćemo izračunati profitnu maržu za kategorije proizvoda. To je uobičajeni scenarij i u mnogim vodičima. U podatkovnom modelu imamo tablicu Prodaja s podacima o transakcijama i postoji odnos između tablice Prodaja i tablice Kategorije proizvoda. Tablica Prodaja sadrži stupac s iznosima prodaje i stupac s troškovima.

Možemo stvoriti izračunati stupac koji izračunava iznos dobiti tako što vrijednosti u stupcu Trošak prodane robe oduzima od vrijednosti u stupcu IznosProdaje, ovako:

Mogućnost Različita prva stranica za zaglavlje i podnožje

Sada možemo stvoriti zaokretnu tablicu i polje Kategorija proizvoda povući u područje STUPCI, a novo polje Dobit u područje VRIJEDNOSTI (stupac u tablici u dodatku PowerPivot polje je na popisu polja zaokretne tablice). Rezultat je implicitna mjera pod nazivom Zbroj dobiti. To je agregatni iznos vrijednosti iz stupca Dobit za svaku kategoriju proizvoda. Rezultat izgleda ovako:

MelbourneIT-konfiguracija-5

U ovom slučaju polje Dobit ima smisla samo kao polje u području VRIJEDNOSTI. Kad bismo polje Dobit smjestili u područje STUPCI, zaokretna bi tablica izgledala ovako:

Zaokretna tablica u kojoj nema korisnih vrijednosti

Polje Dobit ne nudi korisne informacije kada se smjesti u područje STUPCI, RECI ili FILTRI. Ima smisla samo kao agregatna vrijednost u području VRIJEDNOSTI.

Stvorili smo stupac Dobit koji izračunava profitnu maržu za svaki redak u tablici Prodaja. Zatim smo dodali polje Dobit u područje VRIJEDNOST zaokretne tablice te tako automatski stvorili implicitnu mjeru, pri čemu je rezultat izračunat za svaku kategoriju proizvoda. Ako mislite da smo dvaput izračunali dobit za kategorije proizvoda, imate pravo. Najprije smo izračunali dobit za svaki redak u tablici Prodaja, a zatim smo polje Dobit dodali u područje VRIJEDNOSTI, gdje je agregirano za svaku kategoriju proizvoda. Ako mislite i da zapravo nismo morali stvoriti izračunati stupac Dobit, opet imate pravo. No kako onda izračunati dobit bez stvaranja izračunatog stupca Dobit?

Dobit bi bilo bolje izračunati kao eksplicitnu mjeru.

Zasad ćemo ostaviti izračunati stupac Dobit u tablici Prodaja te polje Kategorija proizvoda u području STUPCI i polje Dobit u području VRIJEDNOSTI zaokretne tablice da bismo usporedili rezultate.

U području izračuna tablice Prodaja stvorit ćemo mjeru pod nazivom Ukupna dobit (da bismo spriječili sukob naziva). Naposljetku ćemo dobiti iste rezultate kao u prethodnom primjeru, ali bez izračunatog stupaca Dobit.

Najprije ćemo u tablici Prodaja odabrati stupac IznosProdaje, a zatim ćemo kliknuti Automatski zbroj da bismo stvorili eksplicitnu mjeru Zbroj za IznosProdaje. Imajte na umu da je eksplicitna mjera ona koju stvorimo u području izračuna tablice u dodatku Power Pivot. To ćemo učiniti i za stupac Trošak prodane robe. Te ćemo stupce preimenovati u Ukupni IznosProdajei Ukupni trošak prodane robe da bismo ih lakše raspoznavali.

Gumb Automatski zbroj u dodatku Power Pivot

Zatim ćemo stvoriti drugu mjeru pomoću ove formule:

Ukupna dobit:=[ Ukupni IznosProdaje] - [Ukupni trošak prodane robe]

Napomena :  Formulu smo mogli napisati i ovako: ukupna dobit:=SUM([IznosProdaje]) - SUM([Trošak prodane robe]), ali ako stvorimo zasebne mjere Ukupni IznosProdaje i Ukupni trošak prodane robe, možemo ih koristiti i u zaokretnoj tablici te kao argumente u formulama za razne druge mjere.

Kada oblik nove mjere Ukupna dobit promijenimo u valutni, možemo je dodati u zaokretnu tablicu.

Zaokretna tablica

Kao što možete vidjeti, nova mjera Ukupna dobit vraća isti rezultat kao kada stvorimo izračunati stupac Dobit, a zatim ga smjestimo u područje VRIJEDNOSTI. Razlika je u tome što je mjera Ukupna dobit mnogo učinkovitija i uz nju je podatkovni model pregledniji i sažetiji jer izračunavamo jednu po jednu stavku, i to samo za polja koja odaberemo za zaokretnu tablicu. Izračunati stupac Dobit zapravo nam nije potreban.

Zašto je ovaj zadnji dio važan? Izračunati stupci dodaju podatke u podatkovni model, a podaci zauzimaju memoriju. Ako osvježimo podatkovni model, potrebni su i resursi za obradu za ponovno izračunavanje svih vrijednosti u stupcu Dobit. Ne moramo tako trošiti resurse jer bi se dobit trebala izračunavati kada u zaokretnoj tablici odaberemo polja za koja želimo izračunati dobit, kao što su kategorije proizvoda, regija ili datum.

Pogledajmo sljedeći primjer. Primjer u kojem izračunati stupac stvara rezultate koji na prvi pogled izgledaju točni, ali…

U ovom primjeru želimo izračunati iznose prodaje kao postotke ukupne prodaje. U tablici Prodaja stvorit ćemo izračunati stupac pod nazivom Postotak prodaje, ovako:

Gumb Tekstni okvir

Formula glasi ovako: za svaki redak u tablici Prodaja podijeli iznos u stupcu IznosProdaje s ukupnim zbrojem svih iznosa u stupcu IznosProdaje.

Ako stvorimo zaokretnu tablicu i polje Kategorija proizvoda dodamo u područje STIPCI te odaberemo novi stupac Postotak prodaje, dobit ćemo ukupni zbroj za Postotak prodaje za svaku kategoriju proizvoda.

Zaokretna tablica koja prikazuje zbroj postotaka prodaje za kategorije proizvoda

U redu. To zasad izgleda dobro. Ali dodat ćemo rezač. Dodat ćemo rezač za kalendarsku godinu, a zatim odabrati godinu. U ovom ćemo slučaju odabrati 2007. godinu. Dobili smo ovo.

Netočni rezultati zbroja postotaka prodaje u zaokretnoj tablici

Na prvi pogled to možda i dalje izgleda točno. No ukupan postotak trebao bi biti 100 % jer želimo znati postotak ukupne prodaje za svaku kategoriju proizvoda za 2007. godinu. U čemu je problem?

Stupac Postotak prodaje za svaki je redak izračunao postotak dobiven dijeljenjem vrijednosti u stupcu IznosProdaje s ukupnim zbrojem svih vrijednosti u stupcu IznosProdaje. Vrijednosti su u izračunatom stupcu fiksne. One su nepromjenjivi rezultat za svaki redak u tablici. Kada smo u zaokretnu tablicu dodali stupac Postotak prodaje, on je agregiran kao zbroj svih vrijednosti u stupcu IznosProdaje. Zbroj svih vrijednosti u stupcu Postotak prodaje uvijek je 100 %.

Savjet :  Svakako pročitajte članak Kontekst u DAX formulama. Taj članak sadrži odlično objašnjenje konteksta razine retka i konteksta filtra, o kojima je ovdje riječ.

Izračunati stupac Postotak prodaje možemo izbrisati jer nam nije potreban. Umjesto njega stvorit ćemo mjeru koja točno izračunava postotak ukupne prodaje neovisno o primijenjenim filtrima i rezačima.

Sjećate se mjere UkupniIznosProdaje koju smo prethodno stvorili, one koja jednostavno zbraja stupac IznosProdaje? Koristili smo je kao argument u mjeri Ukupna dobit, a sada ćemo je ponovno koristiti kao argument u novom izračunatom polju.

Savjet :  Eksplicitne mjere kao što su Ukupni IznosProdaje i Ukupni trošak prodane robe nisu korisne samo u zaokretnim tablica i izvješćima, već i kao argumenti u drugim mjerama kada vam je potreban rezultat kao argument. Tako su formule učinkovitije i čitkije. To je dobra praksa modeliranja podataka.

Stvorit ćemo novu mjeru pomoću sljedeće formule:

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

Formula glasi ovako: podijeli rezultat iz stupca Ukupni IznosProdaje ukupnim zbrojem stupca IznosProdaje bez filtara stupaca i redaka osim onih definiranih u zaokretnoj tablici.

Savjet :  Svakako se informirajte o funkcijama CALCULATE i ALLSELECTED u referenci za DAX.

Ako sad u zaokretnu tablicu dodamo novi stupac Postotak ukupne prodaje, dobit ćemo ovo:

Predložak za praćenje imovine na radnoj površini

Sada izgleda bolje. Sada se stupac Postotak ukupne prodaje za svaku kategoriju proizvoda izračunava kao postotak ukupne prodaje za 2007. godinu. Ako u rezaču za kalendarsku godinu odaberemo neku drugu godinu ili više godina, dobit ćemo nove postotke za kategorije proizvoda, ali ukupni će zbroj ostati 100 %. Možemo dodati i druge rezače i filtre. Mjera Postotak ukupne prodaje uvijek će davati postotak ukupne prodaje neovisno o primijenjenim rezačima i filtrima. Kad se koriste mjere, rezultat se uvijek izračunava ovisno o kontekstu koji određuju polja u područjima STUPCI i RECI te primijenjeni filtri i rezači. To je snaga mjera.

Evo nekoliko smjernica za jednostavnije donošenje odluke o tome je li za potrebe određenog izračuna primjereniji izračunati stupac ili mjera:

Situacije u kojima se koriste izračunati stupci

  • Ako želite da se novi podaci prikazuju u području RECI, STUPCI ili FILTRI u zaokretnoj tablici ili pak u području OS, LEGENDA ili PLOČICA U VLASNIŠTVU u vizualizaciji dodatka Power View, morate koristiti izračunati stupac. Baš kao i obični stupci podataka, izračunati se stupci mogu koristiti kao polje u bilo kojem području, a ako su numerički, mogu se i agregirati u području VRIJEDNOSTI.

  • Ako želite da novi podaci budu fiksna vrijednost za redak. Ako, primjerice, imate tablicu datuma sa stupcem koji sadrži datume i želite drugi stupac koji sadrži samo broj mjeseca. Možete stvoriti izračunati stupac koji izračunava samo broj mjeseca iz datuma u stupcu Datum. Na primjer =MONTH(‘Datum’[Datum]).

  • Ako u tablicu želite dodati tekstnu vrijednost za svaki redak, koristite izračunati stupac. Polja s tekstnim vrijednostima nije moguće agregirati u području VRIJEDNOSTI. Na primjer, =FORMAT('Datum'[Datum],"mmmm") daje naziv mjeseca za svaki datum u stupcu Datum u tablici Datum.

Situacije u kojima se koriste mjere

  • Ako će rezultat izračuna uvijek ovisiti o drugim poljima koja odaberete u zaokretnoj tablici.

  • Ako morate izvesti složenije izračune, npr. izračunati broj stavki na temelju nekog filtra ili pak izračunati rezultat iz godine u godinu ili varijancu, koristite izračunato polje.

  • Ako želite minimizirati veličinu radne knjige i maksimizirati njezine performanse, što više izračuna stvorite kao mjere. U mnogim slučajevima svi izračuni mogu biti mjere, čime se znatno smanjuje veličina radne knjige i skraćuje vrijeme osvježavanja.

Imajte na umu da nećete pogriješiti ako stvorite izračunati stupac, kao što smo mi to učinili za stupac Dobit, a zatim ga agregirate u zaokretnoj tablici ili izvješću. To je zapravo vrlo dobar i jednostavan način savladavanja i stvaranja vlastitih izračuna. Kada bolje upoznate te dvije iznimno napredne značajke dodatka Power Pivot, htjet ćete stvoriti što učinkovitiji i točniji podatkovni model. Nadamo se da će vam ono što ste ovdje naučili biti korisno. Dostupni su i drugi odlični korisni resursi. Ovo su samo nekih od njih: Kontekst u DAX formulama, Agregacije u dodatku Power Pivot i Centar za resurse za DAX. Premda je nešto napredniji i namijenjen stručnjacima za računovodstvo i financije, primjer Modeliranje podataka dobiti i gubitka te analiza pomoću dodatka Microsoft Power Pivot u programu Excel prepun je odličnih primjera modeliranja podataka i formula.

Proširite svoje vještine
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×