Aggregasjoner er en måte å skjule, oppsummere eller gruppere data på. Når du starter med rådata fra tabeller eller andre datakilder, er dataene ofte flate, noe som betyr at det er mange detaljer, men de har ikke blitt organisert eller gruppert på noen måte. Denne mangelen på sammendrag eller struktur kan gjøre det vanskelig å oppdage mønstre i dataene. En viktig del av datamodellering er å definere aggregeringer som forenkler, abstrakte eller oppsummerer mønstre som svar på et bestemt forretningsspørsmål.
De vanligste aggregasjonene, for eksempel de som bruker GJENNOMSNITT, ANTALL, DISTINKTANTALL, MAKS,MINeller SUMMER, kan opprettes automatisk i et mål ved hjelp av Autosummer. Andre typer aggregasjoner, for eksempel AVERAGEX,COUNTX,COUNTROWSeller SUMX,returnerer en tabell og krever en formel opprettet ved hjelp av DAX (Data Analysis Expressions).
Forstå aggregasjoner i Power Pivot
Velge Grupper for aggregering
Når du aggregerer data, grupperer du data etter attributter som produkt, pris, område eller dato, og deretter definerer du en formel som fungerer på alle dataene i gruppen. Når du for eksempel oppretter en totalsum for et år, oppretter du en aggregasjon. Hvis du deretter oppretter et forhold for dette året i løpet av forrige år og presenterer disse som prosent, er det en annen type aggregasjon.
Beslutningen om hvordan du grupperer dataene, er drevet av forretningsspørsmålet. Aggregasjoner kan for eksempel svare på følgende spørsmål:
Antall Hvor mange transaksjoner var det i en måned?
Gjennomsnitt Hva var gjennomsnittssalget i denne måneden, etter selger?
Minimums- og maksimumsverdier Hvilke salgsdistrikter var de fem øverste når det gjelder solgte enheter?
Hvis du vil opprette en beregning som svarer på disse spørsmålene, må du ha detaljerte data som inneholder tallene som skal telles eller summeres, og de numeriske dataene må på en eller annen måte være relatert til gruppene du skal bruke til å organisere resultatene.
Hvis dataene ikke allerede inneholder verdier som du kan bruke til gruppering, for eksempel en produktkategori eller navnet på det geografiske området der lageret er plassert, vil du kanskje introdusere grupper i dataene ved å legge til kategorier. Når du bygger grupper i Excel, må du skrive inn eller velge gruppene du vil bruke blant kolonnene i regnearket. I et relasjonssystem lagres imidlertid hierarkier, for eksempel kategorier for produkter, ofte i en annen tabell enn fakta- eller verditabellen. Kategoritabellen er vanligvis koblet til faktadataene med en eller annen type nøkkel. Anta for eksempel at du finner ut at dataene inneholder produkt-IDer, men ikke navnene på produktene eller kategoriene deres. Hvis du vil legge til kategorien i et Excel regneark, må du kopiere i kolonnen som inneholdt kategorinavnene. Med Power Pivot kan du importere produktkategoritabellen til datamodellen, opprette en relasjon mellom tabellen med talldataene og produktkategorilisten, og deretter bruke kategoriene til å gruppere data. Hvis du vil ha mer informasjon, kan du se Opprette en relasjon mellom tabeller.
Velge en funksjon for aggregering
Når du har identifisert og lagt til grupperinger som skal brukes, må du bestemme hvilke matematiske funksjoner som skal brukes for aggregering. Ordet aggregasjon brukes ofte som et synonym for matematiske eller statistiske operasjoner som brukes i aggregasjoner, for eksempel summer, gjennomsnitt, minimum eller antall. Du Power Pivot imidlertid opprette egendefinerte formler for aggregering, i tillegg til standardaggregasjonene som finnes i både Power Pivot og Excel.
Hvis du for eksempel har det samme settet med verdier og grupperinger som ble brukt i de foregående eksemplene, kan du opprette egendefinerte aggregasjoner som svarer på følgende spørsmål:
Filtrerte antall Hvor mange transaksjoner var det i en måned, unntatt vedlikeholdsvinduet for slutten av måneden?
Forhold ved hjelp av gjennomsnitt over tid Hva var prosentvis vekst eller nedgang i salg sammenlignet med samme periode i fjor?
Grupperte minimums- og maksimumsverdier Hvilke salgsdistrikter ble rangert øverst for hver produktkategori, eller for hver salgskampanje?
Legge til aggregasjoner i formler og pivottabeller
Når du har en generell idé om hvordan dataene skal grupperes til å være meningsfulle, og verdiene du vil arbeide med, kan du bestemme om du vil bygge en pivottabell eller opprette beregninger i en tabell. Power Pivot utvider og forbedrer den opprinnelige muligheten til å Excel opprette aggregasjoner, for eksempel summer, antall eller gjennomsnitt. Du kan opprette egendefinerte aggregasjoner i Power Pivot enten i Power Pivot eller i Excel pivottabellområdet.
-
I en beregnetkolonne kan du opprette aggregasjoner som tar hensyn til gjeldende radkontekst for å hente relaterte rader fra en annen tabell, og deretter summere, telle eller gjennomsnitte disse verdiene i de relaterte radene.
-
I et mål kandu opprette dynamiske aggregasjoner som bruker begge filtrene som er definert i formelen, og filtre som er pålagt av utformingen av pivottabellen og utvalget av slicere, kolonneoverskrifter og radoverskrifter. Mål ved hjelp av standardaggregasjoner kan opprettes i Power Pivot ved hjelp av Autosummer eller ved å opprette en formel. Du kan også opprette implisitte mål ved hjelp av standardaggregasjoner i en pivottabell i Excel.
Legge til grupperinger i en pivottabell
Når du utformer en pivottabell, drar du felt som representerer grupperinger, kategorier eller hierarkier, til inndelingen kolonner og rader i pivottabellen for å gruppere dataene. Deretter drar du felt som inneholder numeriske verdier til verdiområdet, slik at de kan telles, beregnes i gjennomsnitt eller summeres.
Hvis du legger til kategorier i en pivottabell, men kategoridataene ikke er relatert til faktadataene, kan det hende du får en feil eller merkelige resultater. Vanligvis Power Pivot vil prøve å løse problemet ved automatisk å oppdage og foreslå relasjoner. Hvis du vil ha mer informasjon, kan du se Arbeide med relasjoner i pivottabeller.
Du kan også dra felt til slicere for å velge bestemte grupper med data som skal vises. Med slicere kan du gruppere, sortere og filtrere resultatene i en pivottabell interaktivt.
Arbeide med grupperinger i en formel
Du kan også bruke grupperinger og kategorier til å aggregere data som er lagret i tabeller, ved å opprette relasjoner mellom tabeller og deretter opprette formler som bruker disse relasjonene til å slå opp relaterte verdier.
Hvis du med andre ord vil opprette en formel som grupperer verdier etter en kategori, må du først bruke en relasjon til å koble tabellen som inneholder detaljdataene og tabellene som inneholder kategoriene, og deretter bygge formelen.
Hvis du vil ha mer informasjon om hvordan du oppretter formler som bruker oppslag, kan du se Oppslag i Power Pivot-formler.
Bruke filtre i aggregasjoner
En ny funksjon i Power Pivot er muligheten til å bruke filtre på kolonner og tabeller med data, ikke bare i brukergrensesnittet og i en pivottabell eller et diagram, men også i selve formlene du bruker til å beregne aggregasjoner. Filtre kan brukes i formler både i beregnede kolonner og i s.
I de nye DAX-aggregasjonsfunksjonene, kan du for eksempel angi en hel tabell som argument i stedet for å angi verdier som skal summeres eller telles over. Hvis du ikke brukte filtre på denne tabellen, vil aggregeringsfunksjonen fungere mot alle verdiene i den angitte kolonnen i tabellen. I DAX kan du imidlertid opprette et dynamisk eller statisk filter på tabellen, slik at aggregasjonen fungerer mot et annet delsett med data, avhengig av filterbetingelsen og gjeldende kontekst.
Ved å kombinere betingelser og filtre i formler kan du opprette aggregasjoner som endres avhengig av verdiene som følger med i formler, eller som endres avhengig av utvalget av radoverskrifter og kolonneoverskrifter i en pivottabell.
Hvis du vil ha mer informasjon, kan du se Filtrere data i formler.
Sammenligning av Excel aggregeringsfunksjoner og DAX-aggregasjonsfunksjoner
Tabellen nedenfor viser noen av de standard aggregeringsfunksjonene som leveres av Excel, og inneholder koblinger til implementeringen av disse funksjonene i Power Pivot. DAX-versjonen av disse funksjonene fungerer mye på samme måte som Excel, med noen mindre forskjeller i syntaks og håndtering av bestemte datatyper.
Standardaggregasjonsfunksjoner
|
Funksjon |
Bruk |
|
Returnerer gjennomsnittet (aritmetisk gjennomsnitt) av alle tallene i en kolonne. |
|
|
Returnerer gjennomsnittet (aritmetisk middelverdi) for alle verdiene i en kolonne. Håndterer tekst og ikke-numeriske verdier. |
|
|
Teller antall numeriske verdier i en kolonne. |
|
|
Teller antall verdier i en kolonne som ikke er tomme. |
|
|
Returnerer den største numeriske verdien i en kolonne. |
|
|
Returnerer den største verdien fra et sett med uttrykk som er evaluert over en tabell. |
|
|
Returnerer den minste numeriske verdien i en kolonne. |
|
|
Returnerer den minste verdien fra et sett med uttrykk som er evaluert over en tabell. |
|
|
Legger sammen alle tallene i en kolonne. |
DAX-aggregeringsfunksjoner
DAX inneholder aggregeringsfunksjoner som lar deg angi en tabell som aggregasjonen skal utføres over. I stedet for bare å legge til eller beregne gjennomsnitt av verdiene i en kolonne, kan du derfor bruke disse funksjonene til å opprette et uttrykk som dynamisk definerer dataene som skal aggregeres.
Tabellen nedenfor viser aggregasjonsfunksjonene som er tilgjengelige i DAX.
|
Funksjon |
Bruk |
|
Beregner gjennomsnittet av et sett med uttrykk som er evaluert over en tabell. |
|
|
Teller et sett med uttrykk som er evaluert over en tabell. |
|
|
Teller antall tomme verdier i en kolonne. |
|
|
Teller totalt antall rader i en tabell. |
|
|
Teller antall rader som returneres fra en nestet tabellfunksjon, for eksempel filterfunksjon. |
|
|
Returnerer summen av et sett med uttrykk som er evaluert over en tabell. |
Forskjeller mellom DAX og Excel Aggregeringsfunksjoner
Selv om disse funksjonene har samme navn som Excel, bruker de Power Pivot 's in-memory analytics engine og har blitt skrevet på nytt for å arbeide med tabeller og kolonner. Du kan ikke bruke en DAX-formel i en Excel arbeidsbok, og omvendt. De kan bare brukes i Power Pivot og i pivottabeller som er basert på Power Pivot data. Selv om funksjonene har identiske navn, kan virkemåten også være litt annerledes. Hvis du vil ha mer informasjon, kan du se de individuelle funksjonsreferanseemnene.
Måten kolonnene evalueres på i en aggregasjon, er også forskjellig fra måten Excel håndterer aggregasjoner på. Et eksempel kan være til hjelp for å illustrere.
La oss si at du vil få en sum av verdiene i Beløp-kolonnen i tabellen Salg, slik at du oppretter følgende formel:
=SUM('Sales'[Amount])
I det enkleste tilfellet får funksjonen verdiene fra én enkelt ufiltrert kolonne, og resultatet er det samme som i Excel, som alltid bare legger sammen verdiene i kolonnen Beløp. I Power Pivot tolkes formelen imidlertid som «Hent verdien i Beløp for hver rad i Salg-tabellen, og legger deretter sammen disse enkeltverdiene. Power Pivot evaluerer hver rad som aggregeringen utføres over, og beregner én enkelt skalarverdi for hver rad, og utfører deretter en aggregasjon på disse verdiene. Resultatet av en formel kan derfor være forskjellig hvis filtre er brukt på en tabell, eller hvis verdiene beregnes basert på andre aggregasjoner som kan filtreres. Hvis du vil ha mer informasjon, kan du se Kontekst i DAX-formler.
DAX-funksjoner for tidsintelligens
I tillegg til tabellaggregasjonsfunksjonene som er beskrevet i forrige del, har DAX aggregeringsfunksjoner som fungerer med datoer og klokkeslett du angir, for å gi innebygd tidsintelligens. Disse funksjonene bruker datoområder til å hente relaterte verdier og aggregere verdiene. Du kan også sammenligne verdier på tvers av datoområder.
Tabellen nedenfor viser tidsintelligensfunksjonene som kan brukes til aggregering.
|
Funksjon |
Bruk |
|
Beregner en verdi i kalenderen slutten av den angitte perioden. |
|
|
Beregner en verdi ved kalenderslutt av perioden før den angitte perioden. |
|
|
Beregner en verdi over intervallet som starter på den første dagen i perioden, og slutter på siste dato i den angitte datokolonnen. |
De andre funksjonene i Tidsintelligens-funksjonsdelen(Tidsintelligensfunksjoner ) er funksjoner som kan brukes til å hente datoer eller egendefinerte datoområder som skal brukes i aggregering. Du kan for eksempel bruke DATOINPERIOD-funksjonen til å returnere et datoområde, og bruke dette settet med datoer som et argument til en annen funksjon til å beregne en egendefinert aggregasjon for bare disse datoene.
