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

Kada prvi put naučite koristiti Power Pivot, većina korisnika otkrije da se stvarna snaga na neki način zbraja ili izračunava. Ako podaci imaju stupac s numeričkim vrijednostima, možete ih jednostavno objediniti tako da ih odaberete u zaokretnoj tablici ili Power View popis polja. Po prirodi, budući da je brojčana, automatski će se zbrajanje, prosjek, brojanje ili bilo koju vrstu agregacije koju odaberete. To se naziva implicitna mjera. Implicitne su mjere odlične za brzo i jednostavno zbrajanje, ali imaju ograničenja, a ta se ograničenja gotovo uvijek mogu prevladati eksplicitnim mjerama iizračunatim stupcima.

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:

Naša nova formula Kategorija proizvoda koristi funkciju RELATED DAX da bi doši do vrijednosti iz stupca ProductCategoryName u povezanoj tablici Kategorija proizvoda, a zatim te vrijednosti unosi za svaki proizvod (svaki redak) u tablicu Proizvod.

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:

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:

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:

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.

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

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

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

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:

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.

ok. Ovo zasad izgleda dobro. No dodat ćemo rezač. Dodat ćemo kalendarsku godinu, a zatim godinu dana. U tom slučaju odabiremo 2007. To je ono što smo dobili.

Na prvi pogled to može i dalje izgledati točno. No postoci bi trebali ukupan iznos od 100 %, jer želimo znati postotak ukupne prodaje za svaku kategoriju proizvoda za 2007. Što je pošlo po zlu?

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

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.

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.

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

To izgleda bolje. Sada se naš postotak ukupne prodaje za svaku kategoriju proizvoda izračunava kao postotak ukupne prodaje za godinu 2007. Ako u rezaču CalendarYear odaberete drugu godinu ili više od jedne godine, za naše kategorije proizvoda primamo nove postotke, no ukupni zbroj i dalje iznosi 100%. Možemo dodati i druge rezače i filtre. Naša mjera % ukupne prodaje uvijek će proizvesti postotak ukupne prodaje bez obzira na primijenjene rezače ili filtre. Pomoću mjera rezultat se uvijek izračunava u skladu s kontekstom koji su određena poljima u stupcima i recima te svim primijenjenim filtrima ili rezačima. To je moć 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 recima, stupcima ili filtrima u zaokretnoj tablici ili na OSI, LEGENDi ili TILE BY u Power View vizualizaciji, 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. Iako je malo naprednija i usmjerena na računovodstvene i financijske stručnjake, modeliranje podataka o dobiti i gubitku i analiza uz Microsoft Power Pivot u Excel primjeru učitava se s sjajnim modeliranjem podataka i primjerima formula.