Moving Genomsnittet Powerpivot

Post navigation. Calculating a Moving Average i PowerPivot. För två veckor sedan lovade jag att prata om hur man genererar ett glidande medelvärde i PowerPivot, men sedan förra veckan fick jag sidospår genom att berätta om ett coolt sätt att visa YouTube-videor på dina SharePoint-sidor med hjälp av en webbdel som hittades på CodePlex som några av mina arbetsgruppsledamöter fann Det var så enkelt att implementera, jag var bara tvungen att dela den med er alla. Men tillbaka till ämnet för att beräkna ett glidande medelvärde kan den första frågan vara vad är ett glidande medelvärde och varför skulle du vilja använda en? Ett glidande medelvärde är helt enkelt summan av två eller flera tidsberoende värden där summan divideras med antalet värden som används. Till exempel om jag pratade om lager priser kan jag vilja använda något som ett 7-dagars glidande medelvärde för att dämpa effekten av enskilda dagspikar eller droppar i aktiekursen som inte indikerar den övergripande aktiestrenden. Några långsiktiga investerare använder ännu längre period glidande medelvärden Tha t betyder inte att om ett lager plummets eller sviker att jag skulle luta sig tillbaka tills det glidande genomsnittet säger att jag ska agera. En god aktieinvesterare kommer att berätta för dig att det finns många andra faktorer, både internt och externt för ett företag som kan tvinga din hand att sälja eller köp någon viss lager Men poängen är, och det här är svaret på den andra frågan, ett rörligt medel dämpar slumpen så att jag lättare kan se det övergripande mönstret av de siffror jag spårar. Okej, så antar jag jobbar för Contoso och ville veta om försäljningen stiger, faller eller vanligtvis platt Om jag tittar på den dagliga försäljningen kommer siffrorna sannolikt att fluktuera upp och ner i inget särskilt mönster som hindrar mig från att spotta en övergripande trend. Följande figur visar Contoso Daily Contoso-försäljning över en 3 månadersperiod under sommaren 2008 valde jag att visa data som ett diagram för att visa hur försäljningen fluktuerar om dagen och avslöja information som jag kanske inte skulle kunna se så enkelt hade jag skapat en tabell med samma värden. Om c vår, jag kunde kartlägga ett helt år eller mer, men för att se enskilda dagar skulle jag behöva bredda tabellen väsentligt. Även med denna mindre tidsperiod kan jag se att försäljningen fluktuerar ganska snyggt. Men jag kanske frågar är försäljningen ökande , Minskar eller håller detsamma Om jag har ett bra öga kanske jag säger att försäljningen spetsar mot slutet av juli och sedan faller lite tillbaka när diagrammet flyttas till augusti men det är inte så uppenbart som det faktum att det finns en bra Hantering av dagliga fluktuationer. Så hur kan jag visuellt visa trender med rörlig genomsnittsförsäljning Nu för denna illustration ska jag skapa ett fyra dagars glidande medelvärde, men ärligt talat finns det inget rätt antal perioder i rörelse I genomsnitt Faktum är att jag ska experimentera med olika tidsperioder för att se vilken tidsperiod som gör att jag inte bara kan upptäcka övergripande trender, men också i det här fallet där jag visar butiksförsäljning, vid säsongsmässiga förändringar. Jag vet redan att om jag visar data efter dag, jag kan använda följande fo rmula att beräkna den dagliga försäljningen av bara vår butikskanal Ja, jag kunde helt enkelt använda SalesAmount och tillämpa en kanalskärare för att bara använda butiksförsäljning, men låt oss hålla fast vid exemplet. Jag kan sedan använda denna beräknade åtgärd för att beräkna föregående dag s försäljning för vilken dag som helst genom att skapa följande åtgärd. StoreSales1DayAgo CALCULATE StoreSales, DATEADD DimDate DateKey, -1, day. You kanske kan gissa att formeln för beräkning av försäljningen för två dagar sedan respektive tre dagar sedan är. StoreSales2DayAgo BERÄKER StoreSales, DATEADD DimDate DateKey, -2, day. StoreSales3DayAgo BERÄKTA StoreSales, DATEADD DimDate DateKey, -3, day. With dessa fyra värden beräknas för varje dag kan jag beräkna summan av dessa värden och dela med 4 för att få ett 4 dagars glidande medelvärde med hjälp av följande beräknade värde. FourDayAverage StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo 4 0.Nå om jag byter tillbaka till min kartsida, ska jag se att Excel uppdaterar fältlistan för att inkludera de nya beräknade åtgärderna Om jag sedan lägger till fältet FourDayAverage i rutan Värden skapa en andra serie i diagrammet har jag nu både den faktiska dagliga försäljningen och det fyra dagars glidande medlet som visas i samma diagram Det enda problemet är att jag också skulle vill ändra diagramformatet för att visa den dagliga försäljningen min första dataserie som kolumner och mitt glidande medelvärde min andra dataserie som en linje När jag högerklickar på diagrammet och väljer Ändra kartortyp kan jag välja Combo som kartortypen som Visas i följande figur I det här fallet är linjediagrammet Clustered Column precis vad jag vill. Eftersom jag lade till den rörliga genomsnittsserien till värdena sist, blir det som regel linjen och alla andra dataserier visas som sammanslagna kolumner Eftersom jag bara har ett värde för varje dag, diagrammet visar en enskild kolumn per dag. Om jag hade skrivit in min dataserie i Values-området i fel ordning kunde jag helt enkelt använda den här dialogrutan för att välja diagramtyp för varje serie När jag klickar OK i den här dialogrutan ser mitt diagram nu ut som följande tydligare visar mer av den övergripande trenden och mindre dagliga fluktuationer. Men vänta är det ett enklare sätt att göra detta Varför ja det finns Men för att lära sig hur man gör det, Du måste vänta tills nästa vecka. Post navigation. My Archives. Email Subscription. Topics jag pratar om. Att tolka 12 månader i DAXputing det rullande 12-månadersmedlet i DAX ser ut som en enkel uppgift, men det döljer lite komplexitet. förklarar hur man skriver den bästa formeln för att undvika vanliga fallgropar med hjälp av tidsintelligensfunktioner. Vi börjar med den vanliga AdventureWorks-datamodellen, med produkter, försäljnings - och kalendertabellen. Kalenderen har markerats som ett kalenderbord. Det är nödvändigt att arbeta med vilken som helst intelligensfunktion och vi byggde en enkel hierarki årsmånad-datum. Med denna uppställning är det väldigt lätt att skapa en första pivottabell som visar försäljning över tiden. När vi gör trendanalys, om försäljningen är utsatt för säsongsmässighet eller, i allmänhet, om du vill eliminera effekten av toppar och droppar i försäljningen, är en vanlig teknik att beräkna värdet under en viss period, vanligtvis 12 månader och genomsnittet. Det rullande genomsnittet över 12 månader ger en jämn indikator på trenden och det är mycket användbart i diagram. Given ett datum kan vi beräkna det 12-månaders rullande genomsnittet med denna formel, som fortfarande har några problem som vi kommer att lösa senare. Formelns beteende är enkelt, det beräknar värdet av Försäljningen efter att ha skapat ett filter på kalendern som visar exakt ett helt år med data Kärnan i formeln är DATESBETWEEN, som returnerar en inkluderande uppsättning datum mellan de två gränserna. Den lägre är. Read den från det innersta om vi visar data i en månad, säg Juli 2007 tar vi det sista synliga datumet med LASTDATE, som returnerar den sista dagen i juli 2007. Sedan använder vi NÄSTA DAG för att ta 1 augusti 2007 och vi använder slutligen SAMEPERIODLASTYEAR för att flytta tillbaka det ett år, vilket ger 1 augusti 2006 Den övre Gränsen är helt enkelt LASTDATE, dvs slutet av juli 2007. Om vi ​​använder den här formeln i en PivotTable ser resultatet ut bra, men vi har ett problem för den sista datumen. Faktum är att värdet är korrekt, som du kan se i figuren. Beräknat fram till 2008 Då finns det inget värde 2009 som är korrekt, vi har inte försäljning under 2009 men det finns ett överraskande värde i december 2010 där vår formel visar totalvärdet istället för ett tomt värde, vilket vi skulle förvänta oss. Faktum är att i december skickar LASTDATE sista dagen på året och nästa dag ska återvända den 1 januari 2011 men nästa dag är en tidsinlysningsfunktion och det förväntas returnera uppsättningar av befintliga datum. Detta faktum är inte särskilt uppenbart och det är värt Några ord more. Time Intelligence funktioner utför inte matte på datum Om du vill ta dagen efter ett visst datum kan du helt enkelt lägga till 1 till en datumkolumn och resultatet blir nästa dag istället Uppsättningar datum fram och tillbaka över tiden Således, NEXTDAY t Akes sin inmatning i vårt fall ett enda radbord med 31 december 2010 och skiftar det en dag senare Problemet är att resultatet ska vara 1 januari 2011 men eftersom kalendertabellen inte innehåller det datumet är resultatet BLANK. Thus, vårt uttryck beräknar Försäljningen med en tom nedre gräns, vilket betyder början av tiden, vilket resulterar i total försäljning. För att korrigera formeln är det tillräckligt att ändra utvärderingsordningen för den nedre gränsen. Som du kan se, nu är NEXTDAY kallad efter ett års återgång. På så sätt tar vi 31 december 2010, flyttar den till 31 december 2009 och tar nästa dag, vilket är den 1 januari 2010 ett befintligt datum i kalenderbordet . Resultatet är nu den förväntade. På den här punkten behöver vi bara dela upp det numret med 12 för att få det rullande genomsnittet. Men som du lätt kan föreställa oss kan vi inte alltid dela upp det med 12 I själva verket i början av Period är det inte 12 månader att samla, men ett lägre antal vi n Eed för att beräkna antalet månader för vilka det finns försäljning Detta kan uppnås genom att kryssfiltrera kalenderbordet med försäljningsbordet efter att vi tillämpat det nya 12-månaders-kontextet. Vi definierar en ny åtgärd som beräknar antalet befintliga månader i de 12 Månader. Du kan se i nästa figur att Months12M-mätningen beräknar ett korrekt värde. Det är värt att notera att formeln inte fungerar om du väljer en period längre än 12 månader, eftersom kalendermånadnamnet bara har 12 värden. Om du behöver längre perioder måste du använda en YYYYMM-kolumn för att kunna räkna mer än 12. Den intressanta delen av denna formel som använder kryssfiltrering är det faktum att det beräknar antalet tillgängliga månader även när du filtrerar med andra attribut Om , till exempel väljer du den blå färgen med en skivare och sedan börjar försäljningen i juli 2007 inte 2005, vilket händer för många andra färger Med hjälp av korsfiltret vid försäljning beräknas formeln korrekt i juli 20 07 det finns en enda månad tillgänglig försäljning för Blue. At denna punkt är det rullande genomsnittet bara en DIVIDE away. When vi använder det i ett pivottabell, har vi fortfarande en liten fråga faktiskt, värdet beräknas också i månader för vilka det inte finns några försäljningar dvs framtida månader. Det kan lösas med ett IF-uttalande för att förhindra att formuläret visar värden när det inte finns någon försäljning jag har inget emot IF men för prestationen beroende av dig är det alltid värt att komma ihåg Att IF kan vara en prestanda mördare, eftersom det skulle kunna tvinga DAX formelmotor att sparka in. I detta specifika fall är skillnaden försumbar, men som regel är det bästa sättet att ta bort värdet när det inte finns någon försäljning att förlita sig på På rena lagringsmotorformler som detta avviker ett diagram med hjälp av Avg12M med en annan som visar Försäljning kan du enkelt uppskatta hur det rullande genomsnittet skisserar trender på ett mycket renare sätt. Håll mig informerad om kommande artiklar nyhetsbrev Avmarkera för att ladda ner filen fritt. DAX innehåller några statistiska aggregeringsfunktioner, såsom medelvärde, varians och standardavvikelse. Andra typiska statistiska beräkningar kräver att du skriver längre DAX-uttryck. Excel, från denna synvinkel, har ett mycket rikare språk. Statistiska mönster är en samling av gemensamma statistiska beräkningar median, mode, glidande medelvärde, percentil och kvartil Vi tackar Colin Banfield, Gerard Brueckl och Javier Guilln, vars bloggar inspirerade några av följande mönster. Baskiskt exempel. Formlerna i detta mönster är lösningarna till specifika statistiska beräkningar. Du kan använda standard DAX-funktioner för att beräkna det genomsnittliga aritmetiska genomsnittet av en uppsättning värden. AVVERAGE returnerar genomsnittsvärdet av alla siffror i en numerisk kolumn. AVERAGEA returnerar genomsnittsvärdet av alla siffror i en kolumn och hanterar båda text och icke-numeriska värden icke-numeriska och tomma textvärden räknas som 0.AVERAGEX beräkna medelvärdet på ett uttryck som utvärderas över en tabell. verage. The moving average är en beräkning för att analysera datapunkter genom att skapa en serie medeltal av olika delsatser av den fullständiga datamängden. Du kan använda många DAX-tekniker för att genomföra denna beräkning. Den enklaste tekniken använder AVERAGEX, detererar ett bord med önskad granularitet och beräknar för varje iteration det uttryck som genererar den enkla datapunkten att använda i medelvärdet. Exempelvis beräknar följande formel det rörliga genomsnittet för de senaste 7 dagarna, förutsatt att du använder en datortabell i din datormodell. Användning av AVERAGEX, du beräknar automatiskt åtgärden på varje granulärnivå När du använder en åtgärd som kan aggregeras som SUM, kan en annan metod baserad på BERÄKNING vara snabbare. Du kan hitta denna alternativa inställning i det fullständiga mönstret Moving Average. Du kan använda vanliga DAX-funktioner för att beräkna variansen av en uppsättning värden. VAR S returnerar värdenas variation i en kolumn som representerar en provpopulation. VAR P returnerar Varians av värden i en kolumn som representerar hela populationen. VARX S returnerar variansen av ett uttryck utvärderat över en tabell som representerar en provpopulation. VARX P returnerar variansen av ett uttryck utvärderat över en tabell som representerar hela populationen. Standardavvikelse. Du kan använd standard DAX-funktioner för att beräkna standardavvikelsen för en uppsättning värden. STDEV S returnerar standardavvikelsen för värden i en kolumn som representerar en provpopulation. STDEV P returnerar standardavvikelsen för värden i en kolumn som representerar hela populationen. STDEVX S returnerar standardavvikelsen för ett uttryck utvärderas över en tabell som representerar en provpopulation. STDEVX P returnerar standardavvikelsen för ett uttryck som utvärderas över en tabell som representerar hela populationen. Median är det numeriska värdet som skiljer den högre halvan av en population från den undre halvan Om det finns ett udda antal rader är medianen medelvärdet sortera raderna från lägsta värdet t o det högsta värdet Om det finns ett jämnt antal rader är det genomsnittet av de två mittenvärdena Formeln ignorerar tomma värden som inte anses vara en del av befolkningen Resultatet är identiskt med MEDIAN-funktionen i Excel. Figur 1 visar En jämförelse mellan resultatet returnerat av Excel och motsvarande DAX formel för medianberäkningen. Figur 1 Exempel på medianberäkning i Excel och DAX. Läget är det värde som oftast förekommer i en uppsättning data. Formeln ignorerar tomma värden, vilket anses inte vara en del av befolkningen Resultatet är identiskt med MODE och funktioner i Excel, vilket returnerar endast minimivärdet när det finns flera lägen i den uppsatta värden som beaktas. Excel-funktionen skulle returnera alla lägen, men du kan inte implementera det som en åtgärd i DAX. Figur 2 jämför resultatet som returneras av Excel med motsvarande DAX-formel för lägesberäkningen. Figur 2 Exempel på modberäkning i Excel och DAX. Procentilen är t han värderar under vilken en viss procent av värdena i en grupp faller. Formeln ignorerar tomma värden som inte anses vara en del av befolkningen. Beräkningen i DAX kräver flera steg, som beskrivs i avsnittet Komplett mönster, som visar hur man får samma resultat av Excel-funktionerna PERCENTILE, och kvartierna är tre punkter som delar upp en uppsättning värden i fyra lika grupper. Varje grupp omfattar en fjärdedel av data. Du kan beräkna kvartilerna med hjälp av percentilmönstret, efter dessa motsvarigheter. Första kvartil lägre kvartil 25th percentile. Second kvartilmedian 50th percentile. Third quartile upper quartile 75th percentileplete Pattern. En få statistiska beräkningar har en längre beskrivning av det fullständiga mönstret, eftersom du kanske har olika implementeringar beroende på datamodeller och andra krav. Moving Average. Vanligtvis utvärderar du det rörliga genomsnittet genom att referera till daggranulationsnivån Den allmänna mallen för followin g formel har dessa markörer. numberofdays är antalet dagar för det rörliga genomsnittet. Datecolumn är datumkolumnen i datumtabellen om du har en eller datumkolumnen i tabellen innehållande värden om det inte finns någon separat datumtabell. Åtgärden att beräkna som det glidande medelvärdet. Det enklaste mönstret använder AVERAGEX-funktionen i DAX, som automatiskt endast tar hänsyn till de dagar för vilka det finns ett värde. Som ett alternativ kan du använda följande mall i datamodeller utan en datortabell och med en åtgärd som kan aggregeras som SUM under hela skadeundersökningsperioden. Den tidigare formeln anser att en dag saknar motsvarande data som en åtgärd som har 0 värde. Detta kan bara hända när du har en separat datumtabell, som kan innehålla dagar för som det inte finns några motsvarande transaktioner Du kan fixa nämnaren för det genomsnittliga med endast antalet dagar för vilka det finns transaktioner med följande mönster, where. facttable är tabellen relaterad till Datortabellen och innehåller värden som beräknas av åtgärden. Du kan använda funktionerna DATESBETWEEN eller DATESINPERIOD istället för FILTER, men dessa fungerar bara i en vanlig datumtabell, medan du kan använda det ovan beskrivna mönstret också till vanliga datumtabeller och till modeller som inte har en datortabell. Till exempel, överväga de olika resultaten som produceras av följande två åtgärder. I figur 3 kan du se att det inte finns någon försäljning den 11 september 2005. Detta datum ingår dock i datumtabellen Det finns således 7 dagar från den 11 september till den 17 september som endast har 6 dagar med data. Figur 3 Exempel på en rörlig medelberäkning med tanke på och ignorerande datum utan försäljning. Åtgärdsgenomsnittet 7 dagar har ett lägre antal mellan 11 september och 17 september eftersom den anser 11 september som en dag med 0 försäljning Om du vill ignorera dagar utan försäljning, använd sedan åtgärden Moving Average 7 Days No Zero Det kan vara rätt sätt när du har ett fullständigt datum t kan men du vill ignorera dagar utan transaktioner Med hjälp av den rörliga genomsnittliga 7-dagarsformeln är resultatet korrekt eftersom AVERAGEX automatiskt endast tar hänsyn till icke-tomma värden. Tänk på att du kan förbättra prestanda för ett glidande medelvärde genom att fortsätta värdet i En beräknad kolumn av ett bord med önskad granularitet, såsom datum eller datum och produkt. Det dynamiska beräkningsförfarandet med en åtgärd erbjuder emellertid möjligheten att använda en parameter för antalet dagar i det glidande medlet, t. ex. ersätta antal dagar med ett mått Genomförandet av parameterns tabellmönster. Medianen motsvarar den 50: e procentilen, som du kan beräkna med hjälp av percentilmönstret. Medianmönstret låter dig optimera och förenkla medianberäkningen med en enda åtgärd, i stället för de flera åtgärder som krävs av Procentmönster Du kan använda detta tillvägagångssätt när du beräknar medianen för värden som ingår i värdesumman, som visas nedan. För att förbättra perforen mance, kanske du vill fortsätta värdet av en åtgärd i en beräknad kolumn om du vill få medianen för resultatet av en åtgärd i datamodellen. Innan du gör denna optimering bör du genomföra MedianX-beräkningen baserat på följande mall, med hjälp av dessa markörer. granularitytable är tabellen som definierar beräkningsgrunderna. Exempelvis kan det vara datumtabellen om du vill beräkna medianen för en åtgärd beräknad på dagsnivån, eller det kan vara värden Datum Årsmonthet om du vill beräkna medianen av en mått som beräknas på månadsnivån. mätning är mätningen att beräkna för varje rad av granularitetstabell för medianberäkningen. mätvärdet är tabellen innehållande data som används av måtten till exempel om granularitetstabellen är en dimension som Datum, då är mätvärdet Internet-försäljning som innehåller kolumnen för Internet-försäljningsbelopp summerad av Internet Total Sales-metoden. Till exempel kan du skriva medianen av Internet Total Försäljning för alla kunder i Adventure Works enligt följande. Tip Följande pattern. is användes för att ta bort rader från granularitytable som inte har motsvarande data i det aktuella valet. Det är en snabbare väg än att använda följande uttryck. Men du kanske Ersätt hela CALCULATETABLE-uttrycket med bara granularitetstabell om du vill överväga tomma värden för åtgärden som 0. Prestanda för MedianX-formel beror på antalet rader i tabellen iterated och på måttets komplexitet. Om prestanda är dåligt kan kvarstå måttet resulterar i en beräknad kolumn i tabellen men detta kommer att ta bort möjligheten att tillämpa filter på medianberäkningen vid frågan. Excel har två olika implementeringar av percentilberäkning med tre funktioner PERCENTILE, och De returnerar alla K - den procentuella värdet, där K ligger inom intervallet 0 till 1 Skillnaden är den PERCENTILE och anser K som ett inkluderande intervall, samtidigt som K intervallet 0 till 1 som exklusiv. Alla dessa funktioner och deras DAX-implementeringar får ett procentilvärde som parameter, vilket vi kallar KK-percentilvärdet ligger i intervallet 0 till 1. De två DAX-implementeringarna av percentil kräver några åtgärder som liknar varandra, men olika tillräckligt för att kräva två olika uppsättningar av formler De åtgärder som definieras i varje mönster är. KPerc Det procentuella värdet motsvarar K. PercPos Positionen för percentilen i den sorterade uppsättningen värden. ValueLösenordet Värdet under procentilställningen. ValueHögt Värdet över percentilpositionen. Percentil Den slutliga beräkningen av percentilen. Du behöver ValueLow och ValueHigh-åtgärderna om PercPos innehåller en decimaldel, för då måste du interpolera mellan ValueLow och ValueHigh för att returnera rätt percentilvärde. 4 visar ett exempel på beräkningarna gjorda med Excel - och DAX-formler, med användning av båda algoritmerna för percentil inkluderande och exklusiv. Figur 4 Procentuell beräkning Lations med Excel-formler och motsvarande DAX-beräkning. I följande avsnitt utförs Percentile-formulären beräkningen på värden som lagras i en tabellkolumn, Data Value, medan PercentileX-formlerna utför beräkningen på värden som returneras av en åtgärd beräknad vid en given granularitet. Percentile Inclusive. The Percentile Inclusive implementation är följande. Percentile Exclusive. The Percentile Exclusive implementation är följande. PercentileX Inclusive. The PercentileX Inkluderande implementering baseras på följande mall, med hjälp av dessa markörer. granularitytable är tabellen som definierar granulariteten av beräkningen Till exempel kan det vara datumtabellen om du vill beräkna procentilen för en åtgärd på dagsnivån, eller det kan vara värden Date YearMonth om du vill beräkna procentilen för en åtgärd på månadsnivån. åtgärden att beräkna för varje rad av granularitetstabell för percentilberäkning. metasuretabla är ta ble innehåller data som används enligt måtten Till exempel, om granularitetstabellen är en dimension som Datum, blir mätvärdet Försäljning innehållande kolumnen Uppräkning summerad med Totalbeloppet. Till exempel kan du skriva PercentileXInc av Total försäljningsvolym för Alla datum i datumtabellen enligt följande. PercentileX Exklusiv. PercentileX Exklusiv implementering baseras på följande mall, med samma markörer som används i PercentileX Inclusive. Till exempel kan du skriva PercentileXExc av Total försäljningsbelopp för alla datum i datumtabellen enligt följande. Håll mig informerad om kommande mönster nyhetsbrev Avmarkera för att ladda ner filen fritt. Publicerad 17 mars 2014 av.