Glidande Medelvärde Power Bi

Rollande 12 månaders genomsnitt i DAXputing det rullande 12-månadersmedlet i DAX ser ut som en enkel uppgift, men det döljer viss komplexitet Denna artikel 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 produkt-, försäljnings - och kalendertabellen Kalenderen har markerats som ett kalenderbord. Det är nödvändigt att arbeta med vilken som helst tidsintelligensfunktion och vi byggde en enkel hierarki årsmånadersdatum. Med denna inställning är det väldigt lätt att skapa en första PivotTable som visar försäljningen över tiden. När en trendanalys görs, om försäljningen är utsatt för säsongsmässighet eller, i allmänhet, om du vill ta bort 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 diagrammen. Given ett datum, vi kan beräkna 12-månaders rullande medelvärde med thi S-formuläret, som fortfarande har några problem som vi kommer att lösa senare. Beteendet hos formeln är enkelt det beräknar värdet av Försäljningen efter att ha skapat ett filter i 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 nedre är. Raddering 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 Då 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 Övre gränsen är helt enkelt LASTDATE, dvs slutet av juli 2007. Om vi ​​använder denna formel i en PivotTable, resultatet ser bra ut, men vi har ett problem för den sista datumen. Faktum är att värdet är korrekt beräknat till 2008, så att värdet är korrekt beräknat till 2008 Då finns det inget värde 2009 som är korrekt, vi gör inte Har försäljning under 2009 men det finns en surpri sjungande 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. I december återkommer LASTDATE sista dagen på året och nästa dag ska returnera 1 januari 2011 men nästa dag är En tid intelligens funktion och det förväntas återställa uppsättningar av befintliga datum Detta faktum är inte mycket uppenbart och det är värt ett par ord more. Time Intelligence funktioner utför inte matte på datum Om du vill ta dagen efter ett visst datum, du kan helt enkelt lägga till 1 till en datumkolumn och resultatet kommer att bli nästa dag I stället förflyttar tidsintelligensen skiftuppsättningarna datum fram och tillbaka över tiden Således tar NEXTDAY inmatningen i vårt fall ett enda radbord med 31 av December 2010 och skiftar den en dag senare Problemet är att resultatet ska vara 1 januari 2011, men eftersom Kalender-tabellen inte innehåller det datumet, är resultatet BLANK. Thus, vårt uttryck beräknar Försäljningen med en tom nedre gräns som betyder början av tid, vilket resulterar i att totalomsättningen av försäljningen För att korrigera formeln räcker det med att ändra utvärderingsordern i den nedre gränsen. Som ni kan se, kallas nu NÄSTA DAG efter övergången på ett år tillbaka. På detta sätt, vi tar 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 Faktum är att i början av perioden inte är 12 månader att samla men ett lägre antal Vi behöver beräkna antal månader för vilka det finns försäljning Detta kan åstadkommas med hjälp av kryssfiltrering av kalenderbordet med försäljningsbordet efter att vi tillämpat det nya 12-månaders-sammanhanget. Vi definierar en ny åtgärd som beräknar antalet befintliga månader under 12 månadersperioden. Kan se i nästa bild att månaderna12 M-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 korsfiltrering är det faktum att det beräknar antalet tillgängliga månader även när du filtrerar med andra attribut Om du till exempel väljer den blå färgen med en skivare , då 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äknar formeln korrekt att i juli 2007 finns en enda månad tillgänglig försäljning för Blue. At denna punkt rullar den rullande Genomsnittet är bara en DIVIDE away. When vi använder det i ett pivottabell, har vi fortfarande en liten fråga faktiskt, värdet beräknas även i månader för vilka det inte finns någon försäljning dvs framtida månader. Detta kan lösas med en IF-uttalande För att förhindra formeln från visar värden när det inte finns någon försäljning jag har inget emot IF men för prestanda-beroende av dig är det alltid värt att komma ihåg att IF kan vara en prestandamördare eftersom det skulle kunna tvinga DAX-formulärsmotorn att sparka in I det här specifika fallet, skillnaden är försumbar men som vanligt ä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å rena lagringsmotorformler som att härmed avbryta ett diagram med hjälp av Avg12M med en annan som visar Försäljning du lätt kan 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 skriv 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, mod e, 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 för 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 medeltalet av alla siffror i en numerisk kolumn. AVERAGEA returnerar genomsnittsvärdet av alla siffror i en kolumn och hanterar både text och icke-numeriska värdena icke-numeriska och tomma textvärden räknas som 0.AVERAGEX beräkna medelvärdet på ett uttryck som utvärderas över en tabell. Moving Average. The rörliga genomsnittet är en beräkning för att analysera datapunkter genom att skapa en serie medeltal av olika delsatser av hela data Set Du kan använda många DAX-tekniker för att genomföra denna beräkning Den enklaste tekniken använder AVERAGEX, itererar ett bord med önskad granularitet och beräknar för varje iteration expres sion som genererar den enkla datapunkten att använda i genomsnittsvärdet. Exempelvis beräknar följande formel det glidande medeltalet de senaste 7 dagarna, förutsatt att du använder en datortabell i din datormodell. Med hjälp av AVERAGEX beräknar du automatiskt åtgärden vid varje granularitetsnivå Vid användning av en åtgärd som kan aggregeras som SUM, kan en annan metod baserad på BERÄKNING vara snabbare. Du kan hitta detta alternativa tillvägagångssätt i det fullständiga mönstret för Moving Average. Du kan använda standard DAX-funktioner för att beräkna variansen av en Uppsättning värden. VAR S returnerar varians av värden i en kolumn som representerar en provpopulation. VAR P returnerar variansen 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ända standard DAX-funktioner för att beräkna e 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 en uttryck utvärderas över en tabell som representerar en provpopulation. STDEVX P returnerar standardavvikelsen för ett uttryck utvärderat ö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 är en udda antal rader, medianen är mittvärdet sorterar raderna från lägsta värde till högsta värde Om det finns ett jämnt antal rader är det genomsnittet av de två mittenvärdena Formeln ignorerar tomma värden, vilka inte anses vara delade 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 som inte anses vara en del av befolkningen. Resultatet är identiskt med MODE och funktioner i Excel, som endast returnerar minimivärdet när det finns flera lägen i den angivna värden. Excel-funktionen skulle returnera alla lägen, men du kan inte implementera det som ett mått i DAX. Figur 2 jämför resultatet returnerat i Excel med motsvarande DAX-formel för lägesberäkningen. Figur 2 Exempel på modberäkning i Excel och DAX. Procentilen är det värde under vilket en viss procentandel av värdena i en grupp faller. Formeln ignorerar tomma värden, vilka inte anses vara delade 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 kvartilerna. Ä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 25: e percentilen. Andra kvartilmedianen 50: e percentilen. Third quartile upper quartile 75th percentileplete Pattern. A några 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 requirements. Moving Average. Usually utvärderar du glidande medelvärde genom att referera till dagen Granularitetsnivå Den allmänna mallen med följande formel har dessa markörer. numberofdays är antalet dagar för det rörliga genomsnittet. Date kolumn är datumkolumnen i datumtabellen om du har en eller kolumnen för datum i tabellen som innehåller värden om det finns ingen separat date table. measure är måttet att beräkna som glidande medel. Det enklaste mönstret använder AVERAGEX func 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 perioden som beaktas . 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 vilka det inte finns några motsvarande transaktioner. Du kan fixa nämnaren för det genomsnittliga med hjälp av Endast antalet dagar för vilka det finns transaktioner med följande mönster, where. facttable är tabellen relaterad till datumtabellen 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 datum tabell, medan du kan använda det ovan beskrivna mönstret också till vanliga datum tabeller och till modeller som inte har en datum tabell. olika resultat 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 så det finns 7 dagar från den 11 september till den 17 september som har endast 6 dagar med data. Figur 3 Exempel på en rörlig genomsnittsberäkning med tanke på och ignorera datum utan försäljning. Åtgärdsgenomsnittet 7 dagar har ett lägre antal mellan 11 september och 17 september, eftersom det anser 11 september som en dag med 0 försäljning Om du vill ignorera dagar utan försäljning ska du använda åtgärden Moving Average 7 Days No Zero Det här kan vara rätt tillvägagångssätt när du har en komplett datortabell men du vill ignorera dagar utan transaktioner Med hjälp av Moving Average 7 Days formel, resultatet är 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 på ett bord med önskad granulär 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 som implementerar parameterns tabellmönster. Medianen motsvarar till 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önstret. Du kan använda detta tillvägagångssätt när du Beräkna medianen för värden som ingår i värdesumman, enligt nedan. För att förbättra prestanda kanske du vill fortsätta värdet av en åtgärd i en beräknad kolumn om du vill få medianen för resultaten 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 d beräknar graden av beräkningen. Exempelvis kan det vara datumdatabasen om du vill beräkna medianen för en åtgärd beräknad på dagsnivån, eller det kan vara värden Datum Årsmonter om du vill beräkna medianen av en åtgärd beräknad till Månaden level. measure är måttet att beräkna för varje rad av granularitytable för median calculation. measuretable är tabellen innehållande data som används enligt mätning. Till exempel, om granularitetstabellen är en dimension som Date, kommer mätbarheten att vara Internet Sales innehållande kolumnen för Internet-försäljningsbelopp summerad av Internet Total Sales-metoden. Till exempel kan du skriva medianen av Internet Total Sales för alla kunder i Adventure Works enligt följande. Tipp Följande pattern. is används för att ta bort rader från granularitytable som inte har någon motsvarande data i det aktuella urvalet Det är ett snabbare sätt än att använda följande uttryck. Dock kan du ersätta hela CALCULATETABLE-uttrycket med bara gr anularitytable 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ålig kan du fortsätta att 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 implementeringsprocentilberäkning med tre funktioner PERCENTILE, och De returnerar alla K-th-procenten av värden, 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 är exklusivt. Alla dessa funktioner och deras DAX-implementeringar får ett procentilvärde som parameter, som vi kallar KK Percentilvärdet ligger inom intervallet 0 till 1. De två DAX-implementeringarna av percentil kräver några åtgärder som är likartade men olika för att kräva två olika uppsättningar av formler. Åtgärden s definieras i varje mönster är. KPerc Det procentuella värdet det motsvarar K. PercPos Positionen för percentilen i den sorterade uppsättningen värden. ValueLösenordet Värdet under percentilpositionen. ValueHögt Värdet över procentuellt läge. 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. Figur 4 visar ett exempel på beräkningarna som gjorts med Excel och DAX-formler, med båda algoritmerna för percentil inklusive och exklusiva. Figur 4 Percentilberäkningar med Excel-formler och motsvarande DAX-beräkning. I följande avsnitt utförs Percentile-formlerna beräkningen på värden som lagras i en tabellkolumn, Data Value, medan PercentileX formler exekverar 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 Implementation PercentileX Inclusive är baserat på följande mall, med hjälp av dessa markörer. Granularitytable är tabellen som definierar beräkningsgrunderna. Till exempel, det kan 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 av en åtgärd vid månadsnivån. measure är åtgärden att beräkna för varje rad av granularitetstabell för percentilberäkning. Measuretable är tabellen innehållande data som används enligt mätning. Till exempel, om granularitetstabellen är en dimension som Date, så är mätvärdet Försäljning innehållande kolumnen Summan summerad med Totalbeloppet. Du kan skriva PercentileXInc av Total försäljningsbelopp för alla datum i datumtabellen enligt följande. PercentileX Exc lusive. The 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. Post navigation. Beräkning av ett rörligt medelvärde i PowerPivot. För två veckor sedan lovade jag att prata om hur man genererar ett glidande medelvärde i PowerPivot, men då förra veckan jag fick 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 tvungen att dela den med er alla. , återvänder tillbaka till ämnet för att beräkna ett glidande medelvärde, kan den första frågan vara vad som ä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 tidsförskjutningar Slutliga värden där summan divideras med antalet värden som används. Om jag till exempel pratade om aktiekurser kanske jag vill 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 Det betyder inte att om en aktie plockar eller suger att jag skulle luta mig tillbaka tills det glidande genomsnittet säger att jag ska agera någon bra 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öpa en viss aktie Men punkten är, och det här är svaret på den andra frågan, ett rörligt medel dämpar slumpen så jag kan Lättare se det övergripande mönstret av de siffror som jag spårar. Så antar jag att 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 åt upp och ner i inget särskilt mönster som hindrar mig från att upptäcka en övergripande trend Följande bild visar Contoso-dagens Contoso-försäljning under en 3 månadersperiod under sommaren 2008. Jag valde att visa data som ett diagram för att visa hur försäljningen fluktuerar om dagen Avslöjande information som jag kanske inte kunde se så lätt hade jag skapat ett bord med samma värden. Naturligtvis kunde jag kartlägga ett helt år eller mer, men för att se enskilda dagar skulle jag behöva bredda tabellen väsentligt. Men, Ä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 densamma. Om jag har ett bra öga kan jag säga att försäljningen spetsar mot slutet av juli och sedan faller Tillbaka lite när diagrammet flyttas in i augusti Men det är inte lika uppenbart som det faktum att det finns en hel del dagliga fluktuationer. Så hur kan jag visuellt visa trender med rörlig genomsnittsförsäljning Nu för denna illustration ska jag gå att skapa en fyra dagars glidande medelvärde men det finns ingen rätt antal perioder i ett glidande medelvärde. 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, utan även i det här fallet där jag visar butiksförsäljning vid säsongsmässiga ändringar. Jag vet redan att om jag visar data om dagen kan jag använda följande formel för 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 att bara använda butiksförsäljningen, 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 dags s-försäljning för vilken dag som helst genom att skapa följande åtgärd. Stora försäljningar1DayAgo BERÄKTA StoreSales, DATEADD DimDate DateKey, -1, day. Du 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ÄKTA StoreSales, DATEADD DimDate DateKey, -2, day. StoreSales3DayAgo BERÄKTA StoreSales, DATEADD Dim Date 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 följande beräknade värde. FörDayAverage StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo 4 0. Nu om jag växlar 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 skapar en andra serie i diagrammet har jag nu både den faktiska Daglig försäljning och det fyra dagars glidande genomsnittet som visas i samma diagram Det enda problemet är att jag också 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 diagramtyp, jag kan välja Combo som diagramtyp som visas i följande figur I det här fallet är det klusterade kolumnlinjediagrammet precis vad jag vill eftersom jag lade till mo Ving genomsnittliga serier till värdena sist, blir det som regel linjen och alla andra dataserier visas som grupperade kolumner. Eftersom jag bara har ett värde för varje dag visar diagrammet en enskild kolumn per dag. Om jag hade skrivit in min dataserie in 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 på 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, finns det ett enklare sätt att göra det här Varför ja det finns Men för att lära sig hur man gör det måste du vänta tills nästa vecka. Positionsnavigering. Mina Archives. Email Subscription. Topics jag pratar om.