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 informationen ofta platt, vilket innebär att det finns mycket information, men den 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, abstrakta eller sammanfattar mönster som svar på en specifik affärsfråga.
De vanligaste aggregeringarna, till exempel de som använder MEDEL,ANTAL,ANTAL UNIKAANTAL, MAX,MINeller SUMMA kan skapas automatiskt i ett mått med hjälp av Autosumma. Andra typer av aggregeringar, till exempel AVERAGEX,COUNTX,COUNTROWSeller SUMX,returnerar en tabell och kräver en formel som skapats med 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, till exempel produkt, pris, region eller datum, och definierar sedan en formel som fungerar på alla data i gruppen. När 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 över det föregående året och presenterar dem som procent är det en annan typ av aggregering.
Beslutet för hur data ska grupperas styrs av affärsfrågan. Aggregeringar kan till exempel besvara följande frågor:
Antal Hur många transaktioner fanns det under en månad?
Medelvärden Vad var den genomsnittliga försäljningen den här månaden, per säljare?
Lägsta och högsta värden Vilka försäljningsdistrikt var de fem högsta när det gäller antal 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 de tal som ska räknas eller summeras, och att numeriska data på något sätt måste relateras till de grupper som du använder för att ordna resultaten.
Om data 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 lagret 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. I ett relationssystem lagras dock hierarkier som produktkategorier ofta i en annan tabell än fakta- eller värdetabellen. Vanligtvis är kategoritabellen länkad till faktainformationen av någon typ av nyckel. Anta till exempel att dina data innehåller produkt-ID, men inte namnen på produkterna eller deras kategorier. Om du vill lägga till kategorin i Excel kalkylblad måste du kopiera den kolumn som innehåller kategorinamnen. Med Power Pivot kan du importera produktkategoritabellen till din datamodell, skapa en relation mellan tabellen med taldata och listan med produktkategorier 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 du vill använda måste du bestämma vilka matematiska funktioner som ska användas för aggregeringen. Ofta används ordet aggregering som en synonym för matematiska eller statistiska åtgärder 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 i både Power Pivot och Excel.
Om du till exempel har samma uppsättning värden och grupper som användes i de föregående exemplen kan du skapa anpassade aggregeringar som besvarar följande frågor:
Filtrerade antal Hur många transaktioner fanns det under en månad förutom underhållsfönstret i slutet av månaden?
Ratios using averages over time Hur stor är den procentuella tillväxten eller avvisande försäljningen jämfört med samma period förra året?
Grupperade lägsta och högsta värden Vilka sälj distrikt rangordnades högst upp för varje produktkategori eller för varje försäljningskampanj?
Lägga till aggregeringar i formler och pivottabeller
När du har en övergripande uppfattning om hur data ska grupperas till meningsfulla och vilka värden du vill arbeta med, kan du bestämma dig för om du vill skapa en pivottabell eller skapa beräkningar i en tabell. Power Pivot utökar och förbättrar den inbyggda möjligheten Excel skapa aggregeringar som summor, antal eller medelvärden. Du kan skapa anpassade aggregeringar Power Pivot antingen i Power Pivot eller i pivottabellområdet Excel aggregeringsfönster.
-
I en beräknad kolumnkan 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 antalet eller beräkna medelvärdet av dessa värden i de relaterade raderna.
-
I ettmått kan du skapa dynamiska aggregeringar som använder både filter som definierats i formeln och filter som införts av pivottabellens design och valet av utsnitt, kolumnrubriker och radrubriker. Mått med 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 gruppering 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. Dra sedan 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 data, kan du få ett fel eller ett första resultat. Vanligtvis Power Pivot ett försök att 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 som ska visas. Med utsnitt kan du interaktivt gruppera, sortera och filtrera resultatet i en pivottabell.
Arbeta med gruppering i en formel
Du kan också använda gruppering 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, ska du först använda en relation för att koppla ihop 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 även i de 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å 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 på tabellen, så att aggregeringen fungerar mot en annan delmängd data beroende på filtervillkor och det aktuella sammanhanget.
Genom att kombinera villkor och filter i formler kan du skapa aggregeringar som ändras beroende på vilka 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 de här funktionerna fungerar på ungefär samma sätt som Excel version, med vissa mindre skillnader i syntax och hantering av vissa datatyper.
Standardaggregeringsfunktioner
|
Funktion |
Använda |
|
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 tomma. |
|
|
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. |
|
|
Adderar alla tal i en kolumn. |
DAX-aggregeringsfunktioner
DAX innehåller aggregeringsfunktioner som låter dig ange en tabell över vilken aggregeringen ska utföras. I stället för att bara addera eller medelvärdet för värdena i en kolumn kan du med de här funktionerna skapa ett uttryck som dynamiskt definierar data för aggregering.
I följande tabell visas de aggregeringsfunktioner som är tillgängliga i DAX.
|
Funktion |
Använda |
|
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 filterfunktion. |
|
|
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 Excel-motsvarigheten använder de Power Pivot:s minnesanalysmotor och har skrivits om för att arbeta med tabeller och kolumner. Du kan inte använda en DAX-formel i Excel arbetsbok och tvärtom. De kan endast användas i Power Pivot och i pivottabeller som är baserade på Power Pivot data. Även om funktionerna har identiska namn kan beteendet vara något annorlunda. Mer information finns i avsnitten om enskilda funktionsreferenser.
Hur kolumner utvärderas i en aggregering skiljer sig också från hur Excel hanterar aggregeringar. Ett exempel kan hjälpa dig att illustrera.
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 får 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 addera sedan dessa enskilda värden. Power Pivot utvärderar varje rad som aggregeringen utförs över 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 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 har DAX aggregeringsfunktioner som fungerar med datum och tider som du anger, för att tillhandahålla inbyggd tidsinformation. I de här funktionerna används datumintervall för att få relaterade värden och aggregera värdena. Du kan också jämföra värden över datumintervall.
I följande tabell visas de tidsinformationsfunktioner som kan användas för aggregering.
|
Funktion |
Använda |
|
Beräknar ett värde vid kalenderslutet för den angivna perioden. |
|
|
Beräknar ett värde vid periodens slut i kalendern före den angivna perioden. |
|
|
Beräknar ett värde över intervallet som börjar vid den första dagen i perioden och slutar vid det senaste datumet i den angivna datumkolumnen. |
Andra funktioner i avsnittet om tidsinformationsfunktioner(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 DATUMINPERIOD för att returnera ett datumintervall och använda den datumuppsättningen som ett argument för en annan funktion för att beräkna en anpassad aggregering för bara dessa datum.
