Första gången de lär sig använda Power Pivot upptäcker de flesta användare att den verkliga kraften är att aggregera eller beräkna ett resultat på något sätt. Om dina data innehåller en kolumn med numeriska värden kan du enkelt aggregera den genom att markera den i en pivottabell eller power view-fältlista. Eftersom den är numerisk summeras den automatiskt, beräknas, räknas eller vilken typ av aggregering du väljer. Detta kallas för ett implicit mått. Implicita mått är bra för snabb och enkel aggregering, men de har gränser, och dessa gränser kan nästan alltid övervinnas med explicita mått och beräknade kolumner.
Låt oss först titta på ett exempel där vi använder en beräknad kolumn för att lägga till ett nytt textvärde för varje rad i en tabell med namnet Produkt. Varje rad i tabellen Produkt innehåller all slags information om varje produkt vi säljer. Vi har kolumner för produktnamn, färg, storlek, återförsäljarpris osv. Vi har en annan relaterad tabell med namnet Produktkategori som innehåller kolumnen ProductCategoryName. Det vi vill är att varje produkt i tabellen Produkt ska innehålla produktkategorinamnet från tabellen Produktkategori. I tabellen Produkt kan vi skapa en beräknad kolumn med namnet Produktkategori så här:
Vår nya produktkategoriformel använder funktionen RELATED DAX för att hämta värden från kolumnen ProductCategoryName i den relaterade tabellen Produktkategori och anger sedan dessa värden för varje produkt (varje rad) i tabellen Produkt.
Det här är ett bra exempel på hur vi kan använda en beräknad kolumn till att lägga till ett fast värde för varje rad som vi kan använda senare i området RADER, KOLUMNER, eller FILTER i pivottabellen eller i en Power View-rapport.
Nu skapar vi ett annat exempel där vi vill beräkna en vinstmarginal för våra produktkategorier. Det här är ett vanligt scenario, även i många självstudiekurser. Vi har Försäljning-tabellen i vår datamodell som har transaktionsdata och det finns ett samband mellan tabellerna Försäljning och Produktkategori. Försäljning-tabellen finns en kolumn med försäljningsbelopp och en annan med kostnader.
Vi kan skapa en beräknad kolumn som beräknar ett vinstbelopp för varje rad genom att subtrahera värden i kolumnen Varukostnad från värden i kolumnen Försäljningsbelopp, så här
Nu kan vi skapa en pivottabell och dra fältet Produktkategori till KOLUMNER och vårt nya Vinst-fält till området VÄRDEN (en kolumn i en tabell i PowerPivot är ett fält i pivottabellens fältlista). Resultatet är ett implicit mått med namnet Vinstsumma. Det är en aggregerat belopp med värden från vinstkolumnen för de olika produktkategorierna. Resultatet ser ut så här:
I det här fallet är Vinst bara meningsfullt som ett fält i VÄRDEN. Om vi skulle placera Vinst i området KOLUMNER skulle pivottabellen se ut så här:
Vinst-fältet har ingen användbar information när det placeras i områdena KOLUMNER, RADER eller FILTER. Det är bara meningsfullt som ett mängdvärde i området VÄRDEN.
Det vi har gjort är att skapa en kolumn med namnet Vinst som beräknar en vinstmarginal för varje rad i tabellen Försäljning. Sedan lade vi till Vinst I området VÄRDEN i pivottabellen, vilket automatiskt skapar ett implicit mått, där ett resultat beräknas för varje produktkategori. Om du tänker att vi faktiskt har beräknad vinsten för våra produktkategorier två gånger tänker du rätt. Först beräknade vi vinsten för varje rad i tabellen Försäljning och sedan lade vi till Vinst i området VÄRDEN, där det aggregerades för varje produktkategori. Om du också tänker att vi faktiskt inte behövde skapa den beräknade kolumnen Vinst tänker du rätt. Men hur beräknar vi då vinsten utan att skapa en beräknad Vinst-kolumn?
Vinsten skulle beräknas bättre som ett explicit mått.
Nu lämnar vi den beräknade kolumnen Vinst i tabellen Försäljning och Produktkategori i KOLUMNER och Vinst i VÄRDEN i pivottabellen, för att jämföra resultaten.
I beräkningsområdet i tabellen Försäljning skapar vi ett mått som heter Total vinst(för att undvika namnkonflikter). I slutänden ger det samman resultat som förut men utan den beräknade kolumnen Vinst.
Först markerar vi kolumnen Försäljningsbelopp i tabellen Försäljning och klickar sedan på Autosumma för att skapa ett explicit mått Summa av försäljningsbelopp. Tänk på att ett explicit mått är ett vi skapar i beräkningsområdet i en tabell i Power Pivot. Vi gör likadant för kolumnen Varukostnad. Vi ger dem nya namn: Totalt försäljningsbelopp och Total varukostnad för att göra dem lättare att identifiera.
Sedan skapar vi ett annat mått med den här formeln:
Total vinst:=[ Totalt försäljningsbelopp] - [Total varukostnad]
Obs!: Vi kan också skriva formeln som Total vinst:=SUMMA([Försäljningsbelopp]) - SUMMA([Varukostnad]), men genom att skapa separata mått för Totalt försäljningsbelopp och Total varukostnad kan vi också använda dem i vår pivottabell. Och vi kan använda dem som argument i alla andra måttformler.
När vi har ändrat Total vinst-måttformatet till valuta kan vi lägga till det i pivottabellen.
Du kan se att vårt nya Total vinst-mått returnerar samma resultat som om vi skapar den beräknade kolumnen Vinst och sedan placerar den i VÄRDEN. Skillnaden är att Total vinst-måttet är mycket effektivare och gör datamodellen renare och smidigare eftersom vi beräknar vid tidpunkten och bara för fälten vi markerar för pivottabellen. Vi behöver faktiskt inte den beräknade kolumnen Vinst trots allt.
Varför är den sista delen viktig? Beräknade kolumner lägger till datamodellen och data tar upp minne. Om vi uppdaterar datamodellen behöver vi även bearbeta resurser för att beräkna om alla värden i Vinst-kolumnen. Vi behöver inte ta upp resurser så här eftersom vi vill beräkna vinsten när vi markerar de fält vi vill ha vinsten för i pivottabellen, som produktkategorier, region eller efter datum.
Vi tittar på ett annat exempel. Ett där en beräknad kolumn ger resultat som vid en första anblick ser rätt ut, men….
I det här exemplet vill vi beräkna försäljningsbelopp som procent av totalförsäljningen. Vi skapar den beräknade kolumnen % av försäljning i tabellen Försäljning, så här:
Formeln anger: För varje rad i tabellen Försäljning dividerar vi beloppet i kolumnen Försäljningsbelopp med SUMMA totalt av alla belopp i kolumnen Försäljningsbelopp.
Om vi skapar en pivottabell och lägger till Produktkategori i KOLUMNER och väljer den nya kolumnen % av försäljning och placerar den i VÄRDEN får vi en summa totalt av % av försäljning för varje produktkategori.
Ok. Det ser bra ut hittills. Men nu lägger vi till ett utsnitt. Vi lägger till Kalenderår och väljer ett år. I det här fallet väljer vi 2007. Det här blir resultatet.
Det här kan se rätt ut först. Men procenttalen ska faktiskt vara 100 %, eftersom vi vill veta procent av totalförsäljningen för varje produktkategori 2007. Så vad gick fel?
Kolumnen % av försäljning beräknade procent för varje rad som är värdet i kolumnen Försäljningsbelopp dividerat med summan totalt av alla värden i kolumnen Försäljningsbelopp. Värden i en beräknad kolumn är fasta. De är ett oföränderligt resultat för varje rad i tabellen. När vi lade till % av försäljning i pivottabellen aggregerades det som en summa av alla värden i kolumnen Försäljningsbelopp. Den summan av alla värden i kolumnen % av försäljning är alltid 100 %.
Tips: Läs kontext i DAX-formler. Där får du en bra förståelse av kontext på radnivå och filterkontext, vilket vi beskriver här.
Vi kan ta bort den beräknade kolumnen % av försäljning eftersom den inte hjälper oss. Istället skapar vi ett mått som på rätt sätt beräknar vår procent av totalförsäljningen, oavsett vilka filter eller utsnitt som används.
Kommer du ihåg måttet Totalt försäljningsbelopp som vi skapade tidigare, den som helt enkelt summerar kolumnen Försäljningsbelopp? Vi använde det som ett argument i måttet Total vinst och vi ska använda det ingen som ett argument i det nya beräknade fältet.
Tips: Att skapa explicita mått som Totalt försäljningsbelopp och Total varukostnad är inte bara användbart i sig i en pivottabell eller rapport, utan även som argument i andra mått när du behöver resultatet som ett argument. Det gör formlerna effektivare och enklare att läsa. Det är en bra datamodelleringsmetod.
Vi skapar ett nytt möt med följande formel:
% av total försäljning:=([Totalt försäljningsbelopp]) / CALCULATE([Totalt försäljningsbelopp], ALLSELECTED())
Den här formeln anger: Dividera resultatet av Totalt försäljningsbelopp med summan totalt av Försäljningsbelopp utan några andra kolumn- eller radfilter än dem som definieras i pivottabellen.
Tips: Läs om funktionerna CALCULATE och ALLSELECTED i DAX-referensen.
Om vi nu lägger till nya % av totalförsäljning i pivottabeller blir resultatet:
Det ser bättre ut. Nu beräknas % av totalförsäljning för varje produktkategori som procent på totalförsäljningen för 2007. Om vi väljer ett annat år, eller mer än ett år i utsnittet Kalenderår, får vi nya procenttal för produktkategorierna men totalsumman är fortfarande 100 %. Vi kan också lägga till andra utsnitt och filter. Måttet % av totalförsäljning ger alltid procent av totalförsäljningen, oavsett vilka utsnitt eller filter som används. Med mått beräknas resultatet alltid enligt kontexten som bestäms av fälten i KOLUMNER och RADER samt av de filter eller utsnitt som används. Det här är måttens styrka.
Här är några riktlinjer för att hjälpa dig att avgöra om en beräknad kolumn eller ett mått är rätt för en viss beräkning:
Använda beräknade kolumner
-
Om du vill att dina nya data ska visas på RADER, KOLUMNER eller i FILTER i en pivottabell, eller på en AXEL, FÖRKLARING eller, DELA IN EFTER i en Power View-visualisering, måste du använda en beräknad kolumn. Precis som vanliga kolumner med data kan beräknade kolumner användas som ett fält i valfritt område, och om de är numeriska kan de även aggregeras i VÄRDEN.
-
Om du vill att dina nya data ska vara ett fast värde för raden. Du har till exempel en datumtabell med en kolumn med datum och du vill ha en annan kolumn som bara innehåller månadens nummer. Du kan skapa en beräknad kolumn som bara beräknar månadsnumret från datumen i kolumnen Datum. Exempel: =MÅNAD('Datum'[Datum]).
-
Om du vill lägga till ett textvärde för varje rad i en tabell använder du en beräknad kolumn. Fält med textvärden kan aldrig aggregeras i VÄRDEN. Exempel: =FORMAT('Date'[Datum],"mmmm") ger oss månadsnamnet för varje datum i kolumnen Datum i tabellen Datum.
Använda mått
-
Om resultatet av beräkningen alltid blir beroende av andra fält du väljer i en pivottabell.
-
Om du behöver göra mer komplexa beräkningar, som att beräkna ett antal baserat på ett filter av något slag, eller beräkna på årsbasis, eller varians, använder du ett beräknat fält.
-
Om du vill ha en så liten arbetsbok som möjligt och maximera dess prestanda skapar du så många beräkningar som mått som möjligt. I många fall kan alla beräkningar vara mått, vilket minskar storleken på arbetsboken betydligt och ger snabbare uppdateringar.
Tänk på att det inte är något fel att skapa beräknade kolumner som vi gjorde med Vinst-kolumnen och sedan aggregerade detta i en pivottabell eller rapport. Det är faktiskt ett riktigt bra och enkelt sätt att lära sig att skapa egna beräkningar. När du får bättre kunskaper om de här två mycket kraftfulla funktionerna i Power Pivot kommer du att vilja skapa den effektivaste och mest exakta datamodellen du kan. Förhoppningsvis får du hjälp av det du har lärt dig här. Det finns några andra riktigt bra resurser som också kan hjälpa dig. Här när några: Context in DAX Formulas (Kontext i DAX-formler), Aggregations in Power Pivot (Aggregeringar i Power Pivot) och DAX Resource Center (DAX-resurscenter). Exemplet om modellering och analys av vinst och förlust med Microsoft Power Pivot i Excel är lite mer avancerat och riktar sig till yrkesverksamma inom redovisning och ekonomi, men där fullspäckat med bra exempel på datamodellering och formler.
