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 många 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, sammanfattar eller sammanfattar mönster som svar på en viss affärsfråga.
De flesta vanliga aggregeringar, till exempel de som använder MEDEL, ANTAL, DISTINCTCOUNT, MAX, MIN eller SUM , 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 skapats med hjälp av DAX (Data Analysis Expressions).
Förstå aggregeringar i Power Pivot
Välja Grupper för aggregering
När du sammanställer data grupperar du data efter attribut som produkt, pris, region eller datum och definierar sedan en formel som fungerar med alla data i gruppen. När du till exempel skapar en summa för ett år skapar du en aggregering. Om du sedan skapar ett förhållande av det här året jämfört med föregående år och presenterar dem som procent, är det en annan typ av aggregering.
Beslutet om hur data ska grupperas styrs av affärsfrågan. Aggregeringar kan till exempel svara på följande frågor:
Räknas Hur många transaktioner var det på en månad?
Genomsnitt Vad var den genomsnittliga försäljningen den här månaden, efter säljare?
Lägsta och högsta värden Vilka försäljningsdistrikt var de fem högsta när det gäller sålda enheter?
Om du vill skapa en beräkning som besvarar de här frågorna måste du ha detaljerade data som innehåller talen som ska räknas eller summeras, och att numeriska data på något sätt måste vara relaterade till de grupper som du kommer att använda för att ordna resultaten.
Om informationen inte redan innehåller värden som du kan använda för gruppering, till exempel en produktkategori eller namnet på den geografiska region där butiken finns, kanske du vill introducera grupper för dina data genom att lägga till kategorier. När du skapar grupper i Excel måste du manuellt skriva eller välja de grupper du vill använda bland kolumnerna i kalkylbladet. Men i ett relationssystem lagras hierarkier som kategorier för produkter ofta i en annan tabell än fakta- eller värdetabellen. Vanligtvis är kategoritabellen länkad till faktadata med någon form av nyckel. Anta till exempel att dina data innehåller produkt-ID:ar, men inte namnen på produkterna eller deras kategorier. Om du vill lägga till kategorin i ett platt Excel-kalkylblad måste du kopiera i kolumnen som innehöll kategorinamnen. Med Power Pivot kan du importera produktkategoritabellen till datamodellen, 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 grupper som ska användas måste du bestämma vilka matematiska funktioner som ska användas för aggregering. Ofta används ordet aggregering som en synonym för matematiska eller statistiska operationer som används i aggregeringar, till exempel summor, medelvärden, minimum eller antal. Men med Power Pivot kan du skapa anpassade formler för aggregering, utöver standardaggregeringarna som finns i både Power Pivot och Excel.
Med tanke på samma uppsättning värden och gruppering som användes i föregående exempel kan du till exempel skapa anpassade aggregeringar som svarar på följande frågor:
Filtrerade antal Hur många transaktioner gjordes under en månad, exklusive underhållsfönstret i slutet av månaden?
Förhållanden med medelvärden över tid Vad var den procentuella ökningen eller nedgången i försäljningen jämfört med samma period förra året?
Grupperade lägsta och högsta vä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 de värden som 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 excel-pivottabellområdet.
-
I en beräknad kolumn kan du skapa aggregeringar som tar hänsyn till aktuell radkontext 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 på de relaterade raderna.
-
I ett mått kan du skapa dynamiska aggregeringar som använder både filter som definierats i formeln och filter som införts genom pivottabellens design 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 grupper i en pivottabell
När du utformar en pivottabell drar du fält som representerar gruppering, kategorier eller hierarkier till avsnittet kolumner och rader 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, beräknas eller summeras.
Om du lägger till kategorier i en pivottabell men kategoridata inte är relaterade till faktadata kan du få ett fel eller ett märkligt resultat. Vanligtvis försöker Power Pivot åtgärda 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 markera 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 grupper och kategorier för att aggregera data som lagras i tabeller genom att skapa relationer mellan tabeller och sedan skapa formler som använder 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 skapa formeln.
Mer information om hur du skapar formler som använder uppslag finns i Uppslag i Power Pivot-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 också i själva formler 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 tillämpat några filter på den tabellen fungerar aggregeringsfunktionen mot alla värden i den angivna kolumnen i tabellen. Men i DAX kan du skapa antingen ett dynamiskt eller statiskt filter i tabellen, så att aggregeringen fungerar mot en annan delmängd data beroende på filtervillkoret och det aktuella sammanhanget.
Genom att kombinera villkor och filter i formler kan du skapa aggregeringar som ändras beroende på de värden som anges i formler, eller som ändras beroende på valet av radrubriker och kolumnrubriker i en pivottabell.
Mer information finns i Filtrera data i formler.
Jämförelse av Excel-aggregeringsfunktioner och DAX-aggregeringsfunktioner
I följande tabell visas några av de standardaggregeringsfunktioner som tillhandahålls av Excel och länkar till implementeringen av dessa funktioner i Power Pivot. DAX-versionen av dessa funktioner fungerar ungefär på samma sätt som Excel-versionen, med vissa mindre skillnader i syntax och hantering av vissa datatyper.
Standardaggregeringsfunktioner
|
Funktion |
Användning |
|
Returnerar medelvärdet (det aritmetiska medelvärdet) för alla tal i en kolumn. |
|
|
Returnerar medelvärdet (det aritmetiska medelvärdet) för alla värden i en kolumn. Hanterar text och icke-numeriska värden. |
|
|
Räknar antalet numeriska värden i en kolumn. |
|
|
Räknar antalet värden i en kolumn som inte är tom. |
|
|
Returnerar det största numeriska värdet i en kolumn. |
|
|
Returnerar det största värdet från en uppsättning uttryck som utvärderats över en tabell. |
|
|
Returnerar det minsta numeriska värdet i en kolumn. |
|
|
Returnerar det minsta värdet från en uppsättning uttryck som utvärderats över en tabell. |
|
|
Summerar alla tal i en kolumn. |
DAX Aggregation-funktioner
DAX innehåller aggregeringsfunktioner som gör att du kan ange en tabell som aggregeringen ska utföras för. I stället för att bara addera eller beräkna medelvärdet av värdena i en kolumn kan du med de här funktionerna skapa ett uttryck som dynamiskt definierar data som ska aggregeras.
I följande tabell visas de aggregeringsfunktioner som är tillgängliga i DAX.
|
Funktion |
Användning |
|
Beräknar medelvärden för en uppsättning uttryck som utvärderats över en tabell. |
|
|
Räknar en uppsättning uttryck som utvärderats över en tabell. |
|
|
Räknar antalet tomma värden i en kolumn. |
|
|
Räknar det totala antalet rader i en tabell. |
|
|
Räknar antalet rader som returneras från en kapslad tabellfunktion, till exempel filterfunktionen. |
|
|
Returnerar summan av en uppsättning uttryck som utvärderats över en tabell. |
Skillnader mellan DAX- och Excel-aggregeringsfunktioner
Även om dessa funktioner har samma namn som sina Excel-motsvarigheter, använder de Power Pivot 's in-memory analytics engine och har skrivits om för att arbeta med tabeller och kolumner. Du kan inte använda en DAX-formel i en Excel-arbetsbok och tvärtom. De kan bara användas i fönstret Power Pivot och i pivottabeller som baseras 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.
Sättet som kolumner utvärderas i en aggregering skiljer sig också från hur excel hanterar aggregeringar. Ett exempel kan illustreras.
Anta att du vill få 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 är detsamma som i Excel, som alltid bara 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 över vilken aggregeringen utförs och beräknar ett enda skalvärde för varje rad, och utför sedan en aggregering på dessa värden. Resultatet av en formel kan därför vara annorlunda om filter har tillämpats på en tabell eller om värdena beräknas baserat på andra aggregeringar som kan filtreras. Mer information finns i Sammanhang i DAX-formler.
DAX-tidsinformationsfunktioner
Förutom tabellaggregeringsfunktionerna som beskrivs i föregående avsnitt har DAX aggregeringsfunktioner som fungerar med datum och tider som du anger för att tillhandahålla inbyggd tidsinformation. Dessa funktioner använder datumintervall för att hämta relaterade värden och sammanställa 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 |
|
Beräknar ett värde i kalenderslutet för den angivna perioden. |
|
|
Beräknar ett värde i kalenderslutet av perioden före den angivna perioden. |
|
|
Beräknar ett värde över intervallet som börjar den första dagen i perioden och slutar med det senaste datumet i kolumnen för angivet datum. |
De andra funktionerna i avsnittet Funktionen Tidsinformation (Tidsinformationsfunktioner) är funktioner som kan användas för att hämta datum eller anpassade datumintervall som ska användas i aggregering. Du kan till exempel använda funktionen DATUMPERIOD 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 bara dessa datum.
