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

De flesta som lär sig att använda Power Pivot upptäcker att den verkliga kraften ligger i 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 i Power View-fältlistan. Eftersom den har numeriska värden så kommer den att summeras, snittberäknas, räknas eller aggregeras på något annat sätt, efter det du väljer. Det här kallas för implicit mått. Implicita mått är bra för snabb och enkel aggregering men har sina begränsningar. Sådana begränsningar kan nästan alltid lösas med explicita mått och beräknade kolumner.

Vi tittar först närmare på ett exempel där vi använder en beräknad kolumn för att lägga till nya värden för varje rad i en tabell som heter Produkt. Varje rad i Produkt-tabellen innehåller alla typer av information om varje produkt vi säljer. Det finns kolumner för Produktnamn, Färg, Storlek, Återförsäljarpris osv... Det finns en relaterad tabell som heter Produktkategori, som innehåller kolumnen Produktkategorinamn. Det vi vill ha för varje produkt i tabellen Produkt är att inkludera produktkategorinamnet från tabellen Produktkategori. I Produkt-tabellen kan vi skapa en beräknad kolumn som heter Produktkategori, så här:

Beräknad kolumn för produktkategori

Vår nya Produktkategori-formel använder DAX-funktionen RELATED DAX för att hämta värde 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

Vinstkolumnen i Power Pivot-tabell

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:

Enkel pivottabell

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:

Pivotabell med oanvändbara värden

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.

Knappen AutoSum i Power Pivot

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.

Pivottabell

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:

Beräknad kolumn, % av försäljning

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.

Pivottabell som visar Summa för % av försäljning för produktkategorier

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.

Felaktigt resultat för summan av procentuell försäljning i pivottabell

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 artikeln om kontext i DAX-formler (engelska). 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 korrekta resultatet för Summan av % försäljning i Pivot-tabell

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 använder du 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 var ett fast värde för raden Exempel: du har 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 det månadsnumret från datumen i kolumnen Datum. Exempel: =MONTH(‘Date’[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.

Utöka dina kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×