Aggregeringar är ett sätt att dölja, sammanfatta eller gruppera data. När du börjar med rådata från tabeller eller andra datakällor är data ofta platta, vilket innebär att det finns mycket detaljer, men de har inte organiserats eller grupperats på något sätt. Denna brist på sammanfattningar eller struktur kan göra det svårt att upptäcka mönster i data. En viktig del av datamodellering är att definiera aggregeringar som förenklar, abstraherar eller sammanfattar mönster som svar på en specifik affärsfråga.
De vanligaste aggregeringarna, till exempel de som använder MEDEL,ANTAL,ANTALANTAL, ANTAL, MAX, MIN eller SUMMA, kan skapas i ett mått automatiskt med hjälp av Autosumma. Andra typer av aggregeringar, till exempel AVERAGEX, COUNTX, COUNTROWS eller SUMX, returnerar en tabell och kräver en formel som har skapats med hjälp av DAX (Data Analysis Expressions).
Förstå aggregeringar i Power Pivot
Välja grupper för aggregering
När du aggregerar data grupperar du data efter attribut som produkt, pris, region eller datum och definierar sedan en formel som fungerar med alla data i gruppen. Om du till exempel skapar en summa för ett år skapar du en aggregering. Om du sedan skapar en kvot för det här året jämfört med föregående år och presenterar dem som procentsatser är det en annan typ av aggregering.
Beslutet om hur data ska grupperas styrs av en affärsfråga. Aggregeringar kan till exempel besvara följande frågor:
Antal Hur många transaktioner gjordes under en månad?
Medelvärden Vad var den genomsnittliga försäljningen den här månaden, per säljare?
Minimivärden och maxvärden Vilka försäljningsdistrikt var de fem bästa när det gäller sålda enheter?
För att kunna skapa en beräkning som besvarar de här frågorna måste du ha detaljerade data som innehåller de tal som ska räknas eller summeras och dessa numeriska data måste vara relaterade på något sätt till de grupper som du ska använda för att ordna resultatet.
Om data inte redan innehåller värden som du kan använda för gruppering, till exempel en produktkategori eller namnet på det geografiska område där butiken ligger, kanske du vill lägga till grupper i dina data genom att lägga till kategorier. När du skapar grupper i Excel måste du manuellt ange eller markera de grupper du vill använda bland kolumnerna i kalkylbladet. I ett relationssystem lagras dock hierarkier, som produktkategorier, ofta i en annan tabell än fakta- eller värdetabellen. Vanligtvis länkas kategoritabellen till dessa faktadata med hjälp av någon form av nyckel. Anta att dina data innehåller produkt-ID:n, men inte namnen på produkter eller deras kategorier. Om du vill lägga till kategorin i ett platt Excel-kalkylblad måste du kopiera i kolumnen som innehåller kategorinamnen. Med Power Pivot kan du importera produktkategoritabellen till din datamodell, skapa en relation mellan tabellen med taldata och produktkategorilistan och sedan använda kategorierna för att gruppera data. Mer information finns i Skapa en relation mellan tabeller.
Välja en funktion för aggregering
När du har identifierat och lagt till de grupperingar som ska användas måste du bestämma vilka matematiska funktioner som ska användas för aggregering. Ordet aggregering används ofta som en synonym för de matematiska eller statistiska åtgärder som används i aggregeringar, till exempel summor, medelvärden, minimum eller antal. Med Power Pivot kan du emellertid skapa anpassade sammansättningsformler utöver de standardaggregeringar som finns i både Power Pivot och Excel.
Med samma uppsättning värden och grupperingar som användes i exemplen ovan kan du till exempel skapa anpassade aggregeringar som besvarar följande frågor:
Filtrerade antal Hur många transaktioner fanns det under en månad, exklusive underhållsfönstret i slutet av månaden?
Kvoter med medelvärden över tid Hur stor var den procentuella ökningen eller minskningen av försäljningen jämfört med samma period förra året?
Grupperade minimi- och maximivärden Vilka försäljningsdistrikt rankades högst för varje produktkategori eller för varje säljkampanj?
Lägga till aggregeringar i formler och pivottabeller
När du har en allmän uppfattning om hur dina data ska grupperas för att vara meningsfulla, och vilka värden du vill arbeta med, kan du bestämma om du vill skapa en pivottabell eller skapa beräkningar i en tabell. Power Pivot utökar och förbättrar Excels inbyggda förmåga att skapa aggregeringar som summor, antal eller medelvärden. Du kan skapa anpassade aggregeringar i Power Pivot antingen i Power Pivot-fönstret eller i pivottabellområdet i Excel.
- I en beräknad kolumn kan du skapa aggregeringar som tar hänsyn till den aktuella radkontexten för att hämta relaterade rader från en annan tabell och sedan summera, räkna eller beräkna medelvärdet för dessa värden i de relaterade raderna.
- I ett mått kan du skapa dynamiska aggregeringar som använder både filter som definieras i formeln och filter som införts genom utformningen av pivottabellen och valet av utsnitt, kolumnrubriker och radrubriker. Mått som använder standardaggregeringar kan skapas i Power Pivot med hjälp av Autosumma eller genom att skapa en formel. Du kan också skapa implicita mått med standardaggregeringar i en pivottabell i Excel.
Lägga till grupperingar i en pivottabell
När du designar en pivottabell drar du fält som representerar grupperingar, kategorier eller hierarkier till kolumn- och radavsnittet i pivottabellen för att gruppera data. Sedan drar du fält som innehåller numeriska värden till värdeområdet så att de kan räknas, medelvärdesättas eller summeras.
Om du lägger till kategorier i en pivottabell men kategoridata inte är relaterade till faktadata kan du få ett fel eller märkliga resultat. Vanligtvis försöker Power Pivot korrigera problemet genom att automatiskt identifiera och föreslå relationer. Mer information finns i Arbeta med relationer i pivottabeller.
Du kan också dra fält till utsnitt för att välja vissa grupper med data för visning. Med utsnitt kan du interaktivt gruppera, sortera och filtrera resultaten i en pivottabell.
Arbeta med grupperingar i en formel
Du kan också använda grupperingar och kategorier för att aggregera data som lagras i tabeller genom att skapa relationer mellan tabeller och sedan skapa formler som utnyttjar dessa relationer för att slå upp relaterade värden.
Med andra ord, om du vill skapa en formel som grupperar värden efter en kategori använder du först en relation för att koppla samman tabellen som innehåller detaljdata och tabellerna som innehåller kategorierna, och sedan skapar du formeln.
Mer information om hur du skapar formler som använder uppslag finns i Uppslag i PowerPivot-formler.
Använda filter i aggregeringar
En ny funktion i Power Pivot är möjligheten att tillämpa filter på kolumner och tabeller med data, inte bara i användargränssnittet och i en pivottabell eller ett diagram, utan även i själva formlerna som du använder för att beräkna aggregeringar. Filter kan användas i formler både i beräknade kolumner och i s.
I de nya DAX-aggregeringsfunktionerna kan du till exempel ange en hel tabell som argument i stället för att ange värden som ska summeras eller räknas. Om du inte använder några filter i tabellen fungerar aggregeringsfunktionen mot alla värden i den angivna kolumnen i tabellen. I DAX kan du emellertid skapa antingen ett dynamiskt eller statiskt filter i tabellen, så att aggregeringen fungerar mot en annan delmängd av data beroende på filtervillkoret och den aktuella kontexten.
Genom att kombinera villkor och filter i formler kan du skapa aggregeringar som ändras beroende på de värden som anges i formlerna, eller som ändras beroende på valet av radrubriker och kolumnrubriker i en pivottabell.
Mer information finns i Filtrera data i formler.
Jämförelse mellan Excel-aggregeringsfunktioner och DAX-aggregeringsfunktioner
I följande tabell visas några av de standardaggregeringsfunktioner som tillhandahålls av Excel samt länkar till implementeringen av dessa funktioner i Power Pivot. DAX-versionen av de här funktionerna fungerar ungefär på samma sätt som Excel-versionen, med några mindre skillnader i syntax och hantering av vissa datatyper.
Standardaggregeringsfunktioner
| Funktion | Användning |
|---|---|
| MEDEL | Returnerar medelvärdet (det aritmetiska medelvärdet) av alla tal i en kolumn. |
| AVERAGEA | Returnerar medelvärdet (aritmetiskt medelvärde) för alla värden i en kolumn. Hanterar text och icke-numeriska värden. |
| ANTAL | Räknar antalet numeriska värden i en kolumn. |
| ANTALV | Räknar antalet värden i en kolumn som inte är tomma. |
| MAX | Returnerar det största numeriska värdet i en kolumn. |
| MAXX | Returnerar det största värdet från en uppsättning uttryck som utvärderats över en tabell. |
| MIN | Returnerar det minsta numeriska värdet i en kolumn. |
| MINX | Returnerar det minsta värdet från en uppsättning uttryck som utvärderats över en tabell. |
| SUMMA | Adderar alla tal i en kolumn. |
DAX-aggregeringsfunktioner
DAX innehåller aggregeringsfunktioner som du kan använda för att ange en tabell som aggregeringen ska utföras över. I stället för att bara addera eller beräkna medelvärdet för värdena i en kolumn kan du med de här funktionerna skapa ett uttryck som dynamiskt definierar de data som ska aggregeras.
I följande tabell visas de aggregeringsfunktioner som är tillgängliga i DAX.
| Funktion | Användning |
|---|---|
| AVERAGEX | Medelvärdet för en uppsättning uttryck som utvärderats över en tabell. |
| RÅD | Räknar en uppsättning uttryck som utvärderats över en tabell. |
| ANTAL.TOMMA | Räknar antalet tomma värden i en kolumn. |
| COUNTX | Beräknar det totala antalet rader i en tabell. |
| COUNTROWS (antal) | Räknar antalet rader som returneras från en kapslad tabellfunktion, till exempel en filterfunktion. |
| SUMX | Returnerar summan av en uppsättning uttryck som utvärderats i en tabell. |
Skillnader mellan DAX- och Excel-aggregeringsfunktioner
Även om de här funktionerna har samma namn som sina Excel-motsvarigheter, använder de Power Pivots minnesinterna analysmotor och har skrivits om för att fungera med tabeller och kolumner. Det går inte att använda en DAX-formel i en Excel-arbetsbok, och tvärtom. De kan endast användas i Power Pivot-fönstret och i pivottabeller som är baserade på Power Pivot-data. Även om funktionerna har identiska namn kan beteendet skilja sig något. Mer information finns i referensavsnitten för enskilda funktioner.
Det sätt som kolumner utvärderas i en aggregering skiljer sig också från det sätt som Excel hanterar aggregeringar. Ett exempel kan hjälpa till att illustrera.
Anta att du vill få fram en summa av värdena i kolumnen Belopp i tabellen Försäljning, så att du skapar följande formel:
=SUM('Sales'[Amount])
I det enklaste fallet hämtar funktionen värdena från en enda ofiltrerad kolumn och resultatet blir detsamma som i Excel, som alltid summerar värdena i kolumnen Belopp. Men i Power Pivot tolkas formeln som "Hämta värdet i belopp för varje rad i tabellen Försäljning och summera sedan de enskilda värdena. Power Pivot utvärderar varje rad som aggregeringen utförs på och beräknar ett enda skalvärde för varje rad och utför sedan en aggregering på dessa värden. Därför kan resultatet av en formel bli annorlunda om filter har använts på en tabell eller om värdena beräknas baserat på andra aggregeringar som kan filtreras. Mer information finns i Kontext i DAX-formler.
DAX-tidsinformationsfunktioner
Utöver de tabellaggregeringsfunktioner som beskrivs i föregående avsnitt innehåller DAX aggregeringsfunktioner som fungerar med datum och tider som du anger, för att ge inbyggd tidsinformation. De här funktionerna använder datumintervall för att hämta relaterade värden och aggregera värdena. Du kan också jämföra värden mellan datumintervall.
I följande tabell visas de tidsinformationsfunktioner som kan användas för aggregering.
| Funktion | Användning |
|---|---|
|
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR |
Beräknar ett värde i slutet av kalendern för den angivna perioden. |
|
OPENINGBALANCEMONTH ÖPPNINGBALANSKVARTAL ÖPPNINGSBALANSÅR |
Beräknar ett värde i slutet av kalendern för perioden som följer på den angivna perioden. |
|
TOTALTMTD TOTALTYTD TOTALQTD |
Beräknar ett värde över intervallet som börjar på den första dagen i perioden och slutar på det senaste datumet i den angivna datumkolumnen. |
De andra funktionerna i avsnittet Tidsinformationsfunktioner (Tidsinformationsfunktioner) är funktioner som kan användas för att hämta datum eller anpassade datumintervall som ska användas vid aggregering. Du kan till exempel använda funktionen DATUMSINPERIOD för att returnera ett datumintervall och använda den uppsättningen datum som ett argument till en annan funktion för att beräkna en anpassad aggregering för just dessa datum.