Agregacije u dodatku Power Pivot

Agregacije su način sažimanja, sažimanja ili grupiranja podataka. Kada započnete s neobrađenim podacima iz tablica ili drugih izvora podataka, podaci su često plositi, što znači da sadrži mnogo detalja, ali nisu ni na koji način organizirani ni grupirani. Nedostatak sažetaka ili strukture može otežati otkrivanje uzoraka u podacima. Važan dio modeliranja podataka jest definiranje agregacija koje pojednostavnjuju, apstrahiraju ili sažimaju uzorke kao odgovor na određeno poslovno pitanje.

Najčešće agregacije, npr. one koje koriste AVERAGE,COUNT, DISTINCTCOUNT, MAX, MIN ili SUM , mogu se u mjeri stvoriti automatski pomoću funkcije Automatski zbroj. Druge vrste agregacija, kao što su AVERAGEX, COUNTX, COUNTROWS ili SUMX, vraćaju tablicu i zahtijevaju formulu stvorenu pomoću izraza za analizu podataka (DAX).

Objašnjenje agregacija u dodatku Power Pivot

Odabir grupa za zbrajanje

Prilikom prikupljanja podataka grupirate podatke prema atributima kao što su proizvod, cijena, regija ili datum, a zatim definirate formulu koja funkcionira na svim podacima u grupi. Kada, primjerice, stvarate ukupni zbroj za godinu, stvarate agregaciju. Ako zatim stvorite omjer ove godine u odnosu na prethodnu godinu i prikažete te vrijednosti kao postotke, riječ je o drugoj vrsti agregacije.

Odluka o grupiranju podataka temelji se na poslovnom pitanju. Agregacijom, primjerice, možete odgovoriti na sljedeća pitanja:

Brojevi Koliko je transakcija bilo u mjesecu?

Prosjek Koja je bila prosječna prodaja u ovom mjesecu po prodavaču?

Minimalne i maksimalne vrijednosti Koja su prodajna područja bila među prvih pet po broju prodanih jedinica?

Da biste stvorili izračun koji odgovara na ta pitanja, morate imati detaljne podatke koji sadrže brojeve koje želite prebrojiti ili zbrojiti te da brojčani podaci moraju biti na neki način povezani s grupama koje ćete koristiti za organizaciju rezultata.

Ako podaci još ne sadrže vrijednosti koje se mogu koristiti za grupiranje, kao što su kategorija proizvoda ili naziv zemljopisne regije u kojoj se trgovina nalazi, bilo bi dobro da u podatke dodate grupe. Kada sastavljate grupe u programu Excel, morate ručno unijeti ili odabrati grupe koje želite koristiti među stupcima na radnom listu. No u relacijskom sustavu hijerarhije kao što su kategorije proizvoda često se pohranjuju u drugoj tablici od tablice činjenica ili vrijednosti. Tablica kategorije obično je s podacima činjenice povezana nekom vrstom ključa. Pretpostavimo, primjerice, da podaci sadrže ID-ove proizvoda, ali ne i nazive proizvoda ili njihovih kategorija. Da biste dodali kategoriju na plošni radni list programa Excel, morali biste kopirati stupac koji sadrži nazive kategorija. Power Pivot omogućuje vam uvoz tablice kategorija proizvoda u podatkovni model, stvaranje odnosa između tablice s brojčanim podacima i popisa kategorija proizvoda, a zatim korištenje kategorija za grupiranje podataka. Dodatne informacije potražite u članku Stvaranje odnosa između tablica.

Odabir funkcije za agregaciju

Nakon što odredite i dodate grupe koje želite koristiti, morate odlučiti koje ćete matematičke funkcije koristiti za agregaciju. Riječ zbrajanje često se koristi kao sinonim za matematičke i statističke operacije koje se koriste u agregacijama, kao što su zbrojevi, prosjeki, minimum ili brojevi. No Power Pivot omogućuje i stvaranje prilagođenih formula za agregiranje, uz standardne agregacije koje možete koristiti u dodatcima Power Pivot i Excel.

Na primjer, uz isti skup vrijednosti i grupa koji su korišteni u prethodnim primjerima, možete stvoriti prilagođene agregacije koje odgovaraju na sljedeća pitanja:

Filtrirani brojevi Koliko je transakcija bilo u mjesecu, bez prozora za održavanje na kraju mjeseca?

Omjeri koji koriste prosjeke tijekom vremena Koliki je bio postotak rasta ili pada prodaje u usporedbi s istim razdobljem prošle godine?

Grupirane minimalne i maksimalne vrijednosti Koja su prodajna područja rangirana najbolje za pojedinu kategoriju proizvoda ili za pojedinu promociju prodaje?

Dodavanje agregacija u formule i zaokretne tablice

Kada imate općenitu predodžbu o tome kako bi trebalo grupirati smislene podatke te s kojim vrijednostima želite raditi, možete odlučiti hoćete li sastaviti zaokretnu tablicu ili stvoriti izračune unutar tablice. Power Pivot proširuje i poboljšava nativnu sposobnost programa Excel da stvara agregacije kao što su zbrojevi, brojevi ili prosjeki. U dodatku Power Pivot možete stvarati prilagođene agregacije u prozoru dodatka Power Pivot ili u području zaokretne tablice programa Excel.

• U izračunatom stupcu možete stvoriti agregacije koje uzimaju u obzir kontekst trenutnog retka radi dohvaćanja povezanih redaka iz druge tablice, a zatim zbrojiti, prebrojiti ili procijeniti te vrijednosti u povezanim recima.
• Kao određenu mjeru možete stvoriti dinamičke agregacije koje koriste i filtre definirane u formuli i filtre nametnute dizajnom zaokretne tablice i odabirom rezača, zaglavlja stupaca i zaglavlja redaka. Mjere koje koriste standardne agregacije mogu se u dodatku Power Pivot stvoriti pomoću automatskog zbroja ili stvaranjem formule. Implicitne mjere možete stvarati i pomoću standardnih agregacija u zaokretnoj tablici programa Excel.

Dodavanje grupa u zaokretnu tablicu

Kada dizajnirate zaokretnu tablicu, polja koja predstavljaju grupiranja, kategorije ili hijerarhije povlačite u sekciju stupaca i redaka zaokretne tablice da biste grupirali podatke. Zatim povlačite polja koja sadrže brojčane vrijednosti u područje vrijednosti da biste ih mogli prebrojati, izračunati za prosjek ili zbrojiti.

Ako u zaokretnu tablicu dodate kategorije, ali podaci kategorije nisu povezani s podacima o činjenicama, mogla bi vam se prikazati pogreška ili neobični rezultati. Power Pivot obično pokušava riješiti problem automatskim otkrivanjem i predlaganjem odnosa. Dodatne informacije potražite u članku Rad s odnosima u zaokretnim tablicama.

Polja možete i povući u rezače da biste odabrali određene grupe podataka za pregled. Rezači omogućuju interaktivno grupiranje, sortiranje i filtriranje rezultata u zaokretnoj tablici.

Rad s grupiranjima u formuli

Grupe i kategorije možete koristiti i za agregiranje podataka pohranjenih u tablicama stvaranjem odnosa među tablicama, a zatim stvaranjem formula koje te odnose iskorištavaju radi traženja povezanih vrijednosti.

Drugim riječima, ako želite stvoriti formulu koja vrijednosti grupira prema kategoriji, najprije koristite odnos za povezivanje tablice koja sadrži detaljne podatke i tablica koje sadrže kategorije, a zatim sastavite formulu.

Dodatne informacije o stvaranju formula koje koriste pretraživanja potražite u članku Pretraživanja u formulama dodatka Power Pivot.

Korištenje filtara u agregacijama

Nova je značajka dodatka Power Pivot mogućnost primjene filtara na stupce i tablice s podacima, ne samo u korisničkom sučelju i unutar zaokretne tablice ili grafikona, nego i na same formule koje koristite za izračun agregacija. Filtri se mogu koristiti u formulama i u izračunatim stupcima i u s.

Tako u novim funkcijama zbrajanja za DAX umjesto određivanja vrijednosti preko kojih će se zbrajati ili prebrojavati, kao argument možete navesti cijelu tablicu. Ako na tu tablicu ne primijenite filtre, funkcija zbrajanja funkcionirat će na svim vrijednostima u navedenom stupcu tablice. No u programu DAX možete stvoriti dinamički ili statički filtar tablice da bi agregacija funkcionirala na različitim podskupovima podataka, ovisno o uvjetu filtra i trenutnom kontekstu.

Kombiniranjem uvjeta i filtara u formulama možete stvoriti agregacije koje se mijenjaju ovisno o vrijednostima navedenima u formulama ili one koje se mijenjaju ovisno o odabiru redaka, zaglavlja i zaglavlja stupaca u zaokretnoj tablici.

Dodatne informacije potražite u odjeljku Filtriranje podataka u formulama.

Usporedba agregacijskih funkcija programa Excel i DAX agregacijskih funkcija

U sljedećoj se tablici navode neke od standardnih agregacijskih funkcija koje nudi Excel i veze za implementaciju tih funkcija u Power Pivot. DAX verzija tih funkcija ponaša se gotovo slično kao verzija programa Excel, s nekim manjim razlikama u sintaksi i rukovanju određenim vrstama podataka.

Standard funkcije agregacije

Funkcija	Koristite značajku
AVERAGE	Vraća prosjek (aritmetičku sredinu) svih brojeva u stupcu.
AVERAGEA	Vraća prosjek (aritmetičku sredinu) svih vrijednosti u stupcu. Obrađuje tekstne i nebrojčane vrijednosti.
COUNT	Broji numeričke vrijednosti u stupcu.
COUNTA	Broji vrijednosti u stupcu koje nisu prazne.
MAX	Vraća najveću numeričku vrijednost u stupcu.
MAXX	Vraća najveću vrijednost iz skupa izraza izračunatih u tablici.
MIN	Vraća najmanju numeričku vrijednost u stupcu.
MINX	Vraća najmanju vrijednost iz skupa izraza izračunatih u tablici.
SUM	Zbraja sve brojeve u stupcu.

DAX funkcije agregacije

DAX sadrži agregacijske funkcije koje vam omogućuju određivanje tablice nad kojom će se agregacija izvesti. Zato vam te funkcije umjesto zbrajanja ili prosjeka vrijednosti u stupcu omogućuju stvaranje izraza koji dinamično definira podatke za zbrajanje.

U sljedećoj se tablici navode agregacijske funkcije dostupne u DAX-u.

Funkcija	Koristite značajku
AVERAGEX	Određuje srednju vrijednost skupa izraza izračunatih u tablici.
COUNTAX	Broji skup izraza izračunatih u tablici.
COUNTBLANK	Broji prazne vrijednosti u stupcu.
COUNTX	Broji ukupne retke u tablici.
COUNTROWS	Broji retke vraćene iz funkcije ugniježđene tablice, kao što je funkcija filtriranja.
SUMX	Vraća zbroj skupa izraza procijenjenih u tablici.

Razlike između DAX i Excel agregacijskih funkcija

Iako te funkcije imaju iste nazive kao njihove inačice iz programa Excel, koriste se modulom za analitiku u memoriji dodatka Power Pivot i ponovno su napisane za rad s tablicama i stupcima. DAX formulu ne možete koristiti u radnoj knjizi programa Excel i obratno. Mogu se koristiti samo u prozoru dodatka Power Pivot i u zaokretnim tablicama koje se temelje na podacima dodatka Power Pivot. Također, iako funkcije imaju identične nazive, ponašanje se može neznatno razlikovati. Dodatne informacije potražite u referentnim temama za pojedinačne funkcije.

Način na koji se stupci izračunavaju u agregaciji također se razlikuje od načina na koji Excel obrađuje agregacije. Primjer može biti koristan za ilustraciju.

Pretpostavimo da želite dobiti zbroj vrijednosti u stupcu Iznos u tablici Prodaja, pa stvoriti sljedeću formulu:

=SUM('Sales'[Amount])

Najjednostavnije je, u najjednostavnijem slučaju, funkcija dohvaća vrijednosti iz jednog nefiltriranog stupca, a rezultat je isti kao u programu Excel, koji uvijek zbraja vrijednosti u stupcu Iznos. No u dodatku Power Pivot formula se tumači kao "Dohvati vrijednost u Iznos za svaki redak tablice Prodaja, a zatim zbroji te pojedinačne vrijednosti. Power Pivot procjenjuje svaki redak preko kojeg se izvodi agregacija i izračunava jednu skalarnu vrijednost za svaki redak, a zatim provodi agregaciju nad tim vrijednostima. Stoga rezultat formule može biti drukčiji ako su na tablicu primijenjeni filtri ili ako su vrijednosti izračunate na temelju drugih agregacija koje se možda filtriraju. Dodatne informacije potražite u odjeljku Kontekst u DAX formulama.

DAX funkcije inteligencije vremena

Osim funkcija zbrajanja tablica opisanih u prethodnom odjeljku, DAX sadrži i agregacijske funkcije koje funkcioniraju s datumima koje odredite da bi omogućile ugrađenu inteligenciju vremena. Te funkcije koriste raspone datuma da bi dohvatile povezane vrijednosti i agregirale vrijednosti. Možete i uspoređivati vrijednosti unutar raspona datuma.

U sljedećoj se tablici navode funkcije inteligencije vremena koje se mogu koristiti za agregaciju.

Funkcija	Koristite značajku
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Izračunava vrijednost na kraju zadanog razdoblja na kalendaru.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Izračunava vrijednost na kalendarskom kraju razdoblja koje prethodi navedenom razdoblju.
TOTALMTD TOTALYTD TOTALQTD	Izračunava vrijednost tijekom intervala koji počinje prvog dana razdoblja i završava na zadnji datum u navedenom stupcu datuma.

Ostale funkcije u odjeljku s funkcijama inteligencije vremena (Funkcije inteligencije vremena) funkcije su koje se mogu koristiti za dohvaćanje datuma ili prilagođenih raspona datuma koji se koriste u agregaciji. Primjerice, funkciju DATESINPERIOD možete koristiti da biste dobili raspon datuma i taj skup datuma koristiti kao argument za drugu funkciju da biste izračunali prilagođenu agregaciju samo za te datume.