DAX-scenarier i Power Pivot

Gäller för
Excel för Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Det här avsnittet innehåller länkar till exempel som visar hur DAX-formler kan användas i följande scenarier.

  • Utföra komplexa beräkningar
  • Arbeta med text och datum
  • Villkorsvärden och feltest
  • Använda tidsinformation
  • Rangordna och jämföra värden

I den här artikeln

Kom igång

Besök DAX Resource Center Wiki där du hittar massor av information om DAX, inklusive bloggar, exempel, faktablad och videor från branschledande experter och Microsoft.

Scenarier: Utföra komplexa beräkningar

DAX-formler kan utföra komplexa beräkningar med anpassade aggregeringar, filtrering och användning av villkorsvärden. Det här avsnittet innehåller exempel på hur du kommer igång med anpassade beräkningar.

Skapa egna beräkningar för en pivottabell

CALCULATE och CALCULATETABLE är kraftfulla, flexibla funktioner som är användbara för att definiera beräknade fält. Med de här funktionerna kan du ändra i vilket sammanhang beräkningen ska utföras. Du kan också anpassa vilken typ av aggregering eller matematisk åtgärd som ska utföras. I följande avsnitt finns exempel.

Använda ett filter på en formel

I de flesta fall där en DAX-funktion använder en tabell som argument kan du vanligtvis skicka en filtrerad tabell i stället, antingen genom att använda funktionen FILTER i stället för tabellnamnet eller genom att ange ett filteruttryck som ett av funktionsargumenten. Följande avsnitt innehåller exempel på hur du skapar filter och hur filter påverkar resultatet av formler. Mer information finns i Filtrera data i DAX-formler.

Med FILTER-funktionen kan du ange filtervillkor med hjälp av ett uttryck, medan de andra funktionerna är utformade specifikt för att filtrera bort tomma värden.

Ta bort filter selektivt för att skapa ett dynamiskt förhållande

Genom att skapa dynamiska filter i formler kan du enkelt besvara frågor som följande:

  • Hur mycket bidrog försäljningen av den aktuella produkten till den totala försäljningen under året?
  • Hur mycket har denna division bidragit till det totala resultatet för alla verksamhetsår, jämfört med andra divisioner?

Formler som du använder i en pivottabell kan påverkas av pivottabellkontexten, men du kan selektivt ändra kontexten genom att lägga till eller ta bort filter. Exemplet i avsnittet ALL visar hur du gör detta. Om du vill ta reda på försäljningsförhållandet för en viss återförsäljare i förhållande till försäljningen för alla återförsäljare kan du skapa ett mått som beräknar värdet för den aktuella kontexten dividerat med värdet för ALL-kontexten.

Avsnittet ALLEXCEPT innehåller ett exempel på hur du selektivt rensar filter i en formel. I båda exemplen får du hjälp med hur resultatet ändras beroende på pivottabellens utformning.

Fler exempel på hur du kan beräkna förhållanden och procenttal finns i följande avsnitt:

Använda ett värde från en yttre loop

Förutom att använda värden från den aktuella kontexten i beräkningar kan DAX använda ett värde från en tidigare loop för att skapa en uppsättning relaterade beräkningar. Följande avsnitt innehåller en genomgång av hur du skapar en formel som refererar till ett värde från en yttre loop. Funktionen EARLIER har stöd för upp till två nivåer av kapslade loopar.

Mer information om radkontext och relaterade tabeller och hur du använder det här konceptet i formler finns i Kontext i DAX-formler.

Scenarier: Arbeta med text och datum

Det här avsnittet innehåller länkar till DAX-referensavsnitt som innehåller exempel på vanliga scenarier som omfattar att arbeta med text, extrahera och komponera datum- och tidsvärden eller skapa värden baserade på ett villkor.

Skapa en nyckelkolumn genom sammanfogning

Det går inte att använda sammansatta nycklar i Power Pivot. Om du har sammansatta nycklar i datakällan kan du därför behöva kombinera dem till en enda nyckelkolumn. Följande avsnitt innehåller ett exempel på hur du skapar en beräknad kolumn baserat på en sammansatt nyckel.

Komponera ett datum baserat på datumdelar som extraherats från ett textdatum

Power Pivot använder en datatyp för datum/tid i SQL Server för att arbeta med datum. Om dina externa data innehåller datum som är formaterade annorlunda – till exempel om dina datum är skrivna i ett regionalt datumformat som inte känns igen av Power Pivot-datamotorn, eller om dina data använder heltalssurrogatnycklar – kan du behöva använda en DAX-formel för att extrahera datumdelarna och sedan komponera delarna till ett giltigt datum / Tidsrepresentation.

Om du till exempel har en kolumn med datum som har angetts som ett heltal och sedan importerats som en textsträng, kan du konvertera strängen till ett datum/tid-värde med hjälp av följande formel:

=DATUM(HÖGER([Värde1];4);VÄNSTER([Värde1];2);EXTEXT([Värde1];2))

Värde1 Resultat
01032009 1/3/2009
12132008 12/13/2008
06252007 6/25/2007

I följande avsnitt finns mer information om de funktioner som används för att extrahera och skapa datum.

Definiera ett eget datum- eller talformat

Om dina data innehåller datum eller tal som inte visas i något av standardtextformaten i Windows kan du definiera ett anpassat format för att säkerställa att värdena hanteras korrekt. De här formaten används när värden konverteras till strängar eller från strängar. Följande avsnitt innehåller en detaljerad lista över de fördefinierade format som är tillgängliga för arbete med datum och tal.

Ändra datatyper med hjälp av en formel

I Power Pivot bestäms datatypen för utdata av källkolumnerna, och du kan inte uttryckligen ange datatypen för resultatet, eftersom den optimala datatypen bestäms av Power Pivot. Du kan dock använda de implicita datatypskonverteringarna som utförs av Power Pivot för att ändra utdatatypen. 

  • Multiplicera med 1,0 om du vill konvertera ett datum eller en talsträng till ett tal. Följande formel beräknar till exempel dagens datum minus 3 dagar och matar sedan ut motsvarande heltalsvärde.
    =(IDAG()-3)*1.0
  • Om du vill konvertera ett datum-, tal- eller valutavärde till en sträng sammanfogar du värdet med en tom sträng. Följande formel returnerar till exempel dagens datum som en sträng.
    =""& IDAG()

Följande funktioner kan också användas för att säkerställa att en viss datatyp returneras:

Konvertera realtal till heltal

Scenario: villkorsvärden och testning av fel

Precis som Excel har DAX funktioner som gör att du kan testa värden i data och returnera ett annat värde baserat på ett villkor. Du kan till exempel skapa en beräknad kolumn som etiketterar återförsäljare som antingen Önskad eller Värde beroende på den årliga försäljningsmängden. Funktioner som testar värden är också användbara för att kontrollera intervall eller typ av värden, för att förhindra oväntade datafel från att avbryta beräkningar.

Skapa ett värde baserat på ett villkor

Du kan använda kapslade OM-villkor för att testa värden och generera nya värden villkorligt. Följande avsnitt innehåller några enkla exempel på villkorsstyrd bearbetning och villkorsstyrda värden:

Söka efter fel i en formel

Till skillnad från Excel kan du inte ha giltiga värden på en rad i en beräknad kolumn och ogiltiga värden på en annan rad. Om det finns ett fel i någon del av en Power Pivot-kolumn flaggas alltså hela kolumnen med ett fel, så att du alltid måste korrigera formelfel som resulterar i ogiltiga värden.

Om du till exempel skapar en formel som dividerar med noll kan du få oändlighetsresultatet eller ett fel. Vissa formler kommer också att fungera fel om funktionen påträffar ett tomt värde när ett numeriskt värde förväntas. När du utvecklar din datamodell är det bäst att låta felen visas så att du kan klicka på meddelandet och felsöka problemet. Men när du publicerar arbetsböcker bör du ta med felhantering för att förhindra att oväntade värden orsakar att beräkningar misslyckas.

För att undvika att returnera fel i en beräknad kolumn använder du en kombination av logiska funktioner och informationsfunktioner för att söka efter fel och alltid returnera giltiga värden. Följande avsnitt innehåller några enkla exempel på hur du gör detta i DAX:

Scenarier: Använda tidsinformation

I DAX-tidsinformationsfunktionerna finns funktioner som hjälper dig att hämta datum eller datumintervall från dina data. Du kan sedan använda dessa datum eller datumintervall för att beräkna värden för liknande perioder. Tidsinformationsfunktionerna innehåller även funktioner som fungerar med standardiserade datumintervall, så att du kan jämföra värden mellan månader, år eller kvartal. Du kan också skapa en formel som jämför värden för det första och sista datumet i en angiven period.

En lista över alla tidsinformationsfunktioner finns i Tidsinformationsfunktioner (DAX). Tips om hur du använder datum och tider effektivt i en Power Pivot-analys finns i Datum i Power Pivot.

Beräkna ackumulerad försäljning

Följande avsnitt innehåller exempel på hur du beräknar utgående och ingående balanser. I exemplen kan du skapa löpande saldon för olika intervall, till exempel dagar, månader, kvartal eller år.

Jämföra värden över tid

Följande avsnitt innehåller exempel på hur du jämför summor över olika tidsperioder. De standardtidsperioder som stöds i DAX är månader, kvartal och år.

Beräkna ett värde över ett anpassat datumintervall

I följande avsnitt finns exempel på hur du hämtar anpassade datumintervall, till exempel de första 15 dagarna efter att en försäljningskampanj har startat.

Om du använder tidsinformationsfunktioner för att hämta en anpassad uppsättning datum kan du använda den som indata för en funktion som utför beräkningar och skapa anpassade mängder för olika tidsperioder. I följande avsnitt finns ett exempel på hur du gör detta:

  • PARALLELPERIOD, funktion

    Obs

    Om du inte behöver ange ett eget datumintervall men arbetar med vanliga redovisningsenheter som månader, kvartal eller år rekommenderar vi att du utför beräkningar med hjälp av de tidsinformationsfunktioner som är utformade för detta ändamål, till exempel TOTALQTD, TOTALMTD, TOTALQTD osv.

Scenarier: Rangordna och jämföra värden

Om du bara vill visa de n översta elementen i en kolumn eller pivottabell har du flera alternativ:

  • Du kan använda funktionerna i Excel för att skapa ett toppfilter. Du kan också välja ett antal högsta eller lägsta värden i en pivottabell. I den första delen av det här avsnittet beskrivs hur du filtrerar fram de 10 främsta objekten i en pivottabell. Mer information finns i Excel-dokumentationen.
  • Du kan skapa en formel som rangordnar värdena dynamiskt och sedan filtrerar efter rangordningsvärdena, eller använda rangordningsvärdet som ett utsnitt. I den andra delen av det här avsnittet beskrivs hur du skapar formeln och sedan använder den rangordningen i ett utsnitt.

Det finns fördelar och nackdelar med varje metod.

  • Det övre Excel-filtret är enkelt att använda, men filtret är endast till för visning. Om de data som ligger till grund för pivottabellen ändras måste du uppdatera pivottabellen manuellt för att se ändringarna. Om du behöver arbeta dynamiskt med rangordningar kan du använda DAX för att skapa en formel som jämför värden med andra värden i en kolumn.
  • DAX-formeln är mer kraftfull. Genom att lägga till rangordningsvärdet i ett utsnitt kan du dessutom klicka på utsnittet för att ändra antalet toppvärden som visas. Beräkningarna är dock beräkningsmässigt dyra och den här metoden kanske inte lämpar sig för tabeller med många rader.

Visa endast de tio översta elementen i en pivottabell

Visa de högsta eller lägsta värdena i en pivottabell
  1. Klicka på nedpilen i rubriken Radetiketter i pivottabellen.
  2. Välj Värdefilter>Topp 10.
  3. I dialogrutan Kolumnnamn> med ett filter <över de 10 högsta väljer du den kolumn du vill rangordna samt antalet värden enligt följande:
    1. Välj Överkant om du vill se cellerna med de högsta värdena och Nederst om du vill se cellerna med de lägsta värdena.
    2. Ange antalet högsta eller lägsta värden som du vill se. Standardvärdet är 10.
    3. Välj hur du vill att värdena ska visas:
NameDescriptionItemsVälj det här alternativet om du vill filtrera pivottabellen och endast visa listan med de högsta eller lägsta objekten baserat på deras värden. ProcentVälj det här alternativet om du vill filtrera pivottabellen så att endast de objekt som summeras till den angivna procentsatsen visas. SummaVälj det här alternativet om du vill visa summan av värdena för de översta eller lägsta posterna.
  1. Markera den kolumn som innehåller de värden som du vill rangordna.
  2. Klicka på OK.

Ordna objekt dynamiskt med hjälp av en formel

Följande avsnitt innehåller ett exempel på hur du använder DAX för att skapa en rangordning som lagras i en beräknad kolumn. Eftersom DAX-formler beräknas dynamiskt kan du alltid vara säker på att rangordningen är korrekt även om underliggande data har ändrats. Eftersom formeln används i en beräknad kolumn kan du använda rangordningen i ett utsnitt och sedan välja de 5 högsta värdena, tio högsta eller till och med de 100 högsta värdena.