När du ska använda beräknade kolumner och beräknade fält

När de första lär sig hur man använder Power Pivot upptäcker de flesta användare att det verkliga värdet ligger i att aggregera eller beräkna ett resultat på något sätt. Om dina data har en kolumn med numeriska värden kan du enkelt aggregera dem genom att välja den i en pivottabell eller fältlista i Power View. Eftersom den är numerisk summeras den automatiskt, beräknas, räknas eller vilken typ av aggregering du väljer. Det här kallas för ett implicit mått. Implicita mått är mycket bra för snabb och enkel aggregering, men de har gränser, och de begränsningarna kan nästan alltid förbikommas med explicita måttoch beräknade kolumner.

Vi tittar först 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 mycket information om varje produkt vi säljer. Vi har kolumner för Produktnamn, Färg, Storlek, Pris osv. Vi har en annan relaterad tabell med namnet Produktkategori som innehåller kolumnen Produktkategorinamn. Vad vi vill är att produktkategorinamnet från tabellen Produktkategori ska inkluderas för varje produkt i tabellen Produktkategori. I tabellen Produkt kan vi skapa en beräknad kolumn med namnet Produktkategori så här:

Vår nya Produktkategori-formel använder DAX-funktionen RELATED DAX för att hämta värden från kolumnen Produktkategorinamn 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]

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 %.

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.

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.

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 i RADER, KOLUMNER eller FILTER i en pivottabell, eller på en AXEL, i en FÖRKLARING eller i 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 kan till exempel ha 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 utifrå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.