Moving Media Powerpivot

Messaggio navigation. Calculating una media mobile a PowerPivot. Two settimane fa ho promesso di parlare di come generare una media mobile a PowerPivot, ma poi la settimana scorsa ho ottenuto sviato da raccontandovi un modo simpatico per visualizzare i video di YouTube nelle tue pagine di SharePoint utilizzando una parte web trovato su CodePlex che alcuni dei membri del mio team di lavoro ho trovato che fosse così facile da implementare, ho dovuto condividere con voi all. However, tornando di nuovo al tema di calcolare una media mobile, la prima domanda potrebbe essere quello è una media mobile e poi perché si vuole utilizzare uno una media mobile è semplicemente la somma di due o più valori dipendenti dal tempo in cui la somma viene poi diviso per il numero di valori utilizzati per esempio, se io parlavo di magazzino i prezzi, mi potrebbero voler usare qualcosa come una 7 giorni di media mobile a smorzare l'effetto dei singoli picchi giorno o gocce nel prezzo delle azioni che non sono indicative della tendenza generale magazzino Alcuni investitori a lungo termine usare anche d'epoca in movimento più lunghe medie che fa non significa che se un titolo precipita o vola che avrei sedersi fino a quando la media mobile mi dice di agire Qualsiasi buon investitore azionario vi dirà ci sono molti altri fattori sia interni che esterni di una società che potrebbe costringere la mano per vendere o comprare qualsiasi azione particolare ma il punto è, e questa è la risposta alla seconda domanda, una media mobile smorza casualità in modo da poter vedere più facilmente il modello generale dei numeri che io sono tracking. Ok, quindi supponiamo io lavoro per Contoso e volevo sapere se le vendite sono in aumento, di caduta o generalmente piatta Se guardo vendite giornaliere, i numeri sono suscettibili di variare su e giù in nessun modello particolare mi impedisce di individuare una tendenza generale La figura seguente mostra le vendite Contoso quotidiana Contoso nel corso di un 3 mesi periodo durante l'estate del 2008 ho scelto di mostrare i dati come una tabella per mostrare come le vendite oscillano di giorno informazioni rivelando che potrei non essere in grado di vedere la stessa facilità avevo creato una tabella con lo stesso corso values. Of, ho potuto tracciare un intero anno o più, ma per vedere i singoli giorni, avrei dovuto allargare il grafico sostanzialmente Tuttavia, anche con questo periodo di tempo più piccolo, posso vedere che le vendite oscillano abbastanza bene, ma potrei chiedere sono vendite in aumento, in diminuzione o restare lo stesso Se ho un buon occhio, mi verrebbe da dire che il picco delle vendite verso la fine di luglio e poi ripiegare un po 'come il grafico si muove in agosto, ma che non è così evidente come il fatto che vi è una grande quantità di fluttuazione giornaliera. Quindi come posso visivamente le tendenze di visualizzazione con lo spostamento di vendite medie Ora, ai fini di questa illustrazione, ho intenzione di creare una media mobile di quattro giorni, ma onestamente, non c'è nessuno giusto numero di periodi in una media mobile, infatti, dovrei sperimentare con diversi periodi di tempo per vedere quale periodo di tempo mi permette di individuare non solo le tendenze generali, ma anche in questo caso in cui mi sono la visualizzazione vendite dei negozi, a changes. I stagionale già sapere che se visualizzare i dati di giorno, non posso utilizzare la seguente formula per calcolare le vendite giornaliere del solo nostro canale negozio Sì, si potrebbe semplicemente usare SalesAmount e applicare una affettatrice canale da utilizzare solo le vendite dei negozi, ma lasciare che s rimanere con il example. I può quindi utilizzare questa misura calcolata per calcolare il giorno precedente s vendite per ogni giorno creando la seguente measure. StoreSales1DayAgo CALCULATE StoreSales, DATEADD DimDate DateKey, -1, day. You potrebbero essere in grado di indovinare che la formula per calcolare le vendite di due giorni fa e tre giorni fa, rispettivamente are. StoreSales2DayAgo cALCOLARE StoreSales, DATEADD DimDate DateKey, -2, day. StoreSales3DayAgo cALCOLARE StoreSales, DATEADD DimDate DateKey, -3, day. With questi quattro valori calcolati per ogni giorno, posso calcolare la somma di questi valori e dividere per 4 per ottenere un 4 giorni di media mobile calcolata utilizzando la seguente value. FourDayAverage StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo 4 0.Now se passo indietro alla mia pagina grafico, dovrei vedere che Excel aggiorna l'elenco campo per includere le nuove misure calcolate Se quindi aggiungere il campo per FourDayAverage casella valori creazione di una seconda serie nel grafico, ora ho sia l'attuale vendite giornaliere e la media mobile a quattro giorni visualizzati nella stessa tabella l'unico problema è, vorrei anche voler cambiare il formato grafico per visualizzare i vendite giornaliere mia prima serie di dati come colonne e la mia media mobile la mia seconda serie di dati come linea Quando mi fate clic destro sul grafico e selezionare Cambia tipo di grafico, posso selezionare Combo come il tipo di grafico, come mostrato nella figura seguente in questo caso, la Colonna cluster grafico a linee è esattamente quello che voglio perché ho aggiunto la serie media mobile per la zona ultimi valori, diventa per default la linea e tutte le altre serie di dati vengono visualizzate come colonne raggruppate dal momento che ho un solo valore per ogni giorno, il grafico mostra un individuo colonna per day. If ero entrato nella mia serie di dati nella zona valori nell'ordine sbagliato, potrei semplicemente usare questa finestra di dialogo per selezionare il tipo di grafico per ogni serie quando clicco su OK in questa finestra di dialogo, il mio grafico ora sembra seguenti elementi più chiaramente mostra la maggiore della tendenza generale e fluctuation. But meno quotidiane aspettare, c'è un modo più semplice per fare questo perché sì, c'è, ma per imparare a fare questo, si dovrà attendere fino al prossimo week. Post navigation. My Archives. Email Subscription. Topics parlo about. Rolling 12 mesi media in DAXputing la media mobile di 12 mesi in DAX si presenta come un compito semplice, ma nasconde una certa complessità Questo articolo spiega come scrivere la formula migliore per evitare gli errori più comuni che utilizzano l'intelligenza tempo functions. We iniziamo con il solito modello di dati AdventureWorks, con prodotti, vendite e tavolo Calendario il Calendario è stato contrassegnato come un tavolo di calendario, è necessario lavorare con qualsiasi funzione di tempo intelligenza e abbiamo costruito un semplice gerarchia di anno-mese-data con questo istituito, è molto facile creare una prima tabella pivot che mostra le vendite oltre time. When fare analisi di tendenza, se le vendite sono soggette a stagionalità o, più in generale, se si desidera rimuovere l'effetto di picchi e cali di vendite, una tecnica comune è quello di calcolare il valore in un dato periodo, di solito 12 mesi, e la media è la media mobile su 12 mesi fornisce una superficie liscia indicatore della tendenza ed è molto utile in charts. Given una data, siamo in grado di calcolare il 12 mesi media mobile con questa formula, che ha ancora qualche problema che risolveremo comportamento later. The della formula è semplice calcola il valore delle vendite dopo la creazione di un filtro sul calendario che mostra esattamente un intero anno di dati il ​​nucleo della formula è il DATESBETWEEN, che restituisce un set comprensivo di date tra i due confini quello inferiore is. Reading dal più interno se stiamo dimostrando di dati per un mese, dicono luglio 2007 prendiamo l'ultima data visibile utilizzando ultimoData, che restituisce il ultimo giorno nel luglio 2007 Poi usiamo NEXTDAY di prendere il 1 ° agosto 2007 e, infine, utilizzare SAMEPERIODLASTYEAR per spostare indietro un anno, cedendo 1 Agosto 2006 il limite superiore è semplicemente ultimoData, cioè fine luglio 2007.If usiamo questo formula in una tabella pivot, il risultato appare bene, ma abbiamo un problema per l'ultimo fatto date. In, come si può vedere in figura, il valore viene calcolato correttamente fino al 2008 poi, non vi è alcun valore nel 2009 che è corretto, non ci sono i le vendite nel 2009, ma non vi è un valore sorprendente dicembre 2010, dove la nostra formula mostra il totale complessivo, invece di un valore vuoto, come ci expect. In infatti, dicembre, ultimoData restituisce l'ultimo giorno dell'anno e NEXTDAY dovrebbe restituire dal 1 gennaio 2011, ma NEXTDAY è una funzione del tempo di intelligence e si prevede di restituire set di date esistenti Questo fatto non è molto evidente e vale la pena qualche parola funzioni di intelligence more. Time non si esegue la matematica in date If si vuole prendere il giorno dopo una certa data, si può semplicemente aggiungere 1 a qualsiasi colonna della data, e il risultato sarà il giorno successivo, invece, le funzioni di intelligence in tempo spostano set di data avanti e indietro nel tempo Così, NEXTDAY prende il suo ingresso in nostro caso un tavolo singolo-fila con il 31 dicembre 2010 e lo sposta di un giorno in più il problema è che il risultato dovrebbe essere 1 gennaio 2011, ma, perché la tabella calendario non contiene tale data, il risultato è BLANK. Thus, la nostra espressione calcola vendite con un limite inferiore vuoto, il che significa l'inizio del tempo, ottenendo come risultato il totale delle vendite per correggere la formula è sufficiente cambiare l'ordine di valutazione dei boundary. As inferiori si può vedere, ora NEXTDAY è chiamato dopo il turno di un anno indietro in questo modo, prendiamo il 31 dicembre 2010, spostarlo al 31 dicembre 2009 e prendere il giorno successivo, che è 1 Gennaio 2010 una data esistente nel risultato calendario table. The è ora l'atteso one. At questo punto, abbiamo bisogno solo di dividere quel numero per 12 per ottenere la media mobile, ma, come si può facilmente immaginare, non possiamo sempre dividiamo per 12 infatti, all'inizio del periodo non ci sono 12 mesi di aggregare, ma un numero inferiore abbiamo bisogno di calcolare il numero di mesi per i quali ci sono le vendite questo può essere realizzato utilizzando il filtro trasversale del tavolo calendario con la tabella di vendita dopo abbiamo applicato il nuovo contesto di 12 mesi definiamo una nuova misura che calcola il numero di mesi esistenti nei 12 mesi period. You può vedere nella figura successiva che la misura Months12M calcola un corretto value. It vale la pena notare che la formula non funziona se si sceglie un periodo superiore a 12 mesi, perché il MonthName calendario ha solo 12 valori Se avete bisogno di periodi più lunghi, è necessario utilizzare una colonna AAAAMM essere in grado di contare più di 12. il parte interessante di questa formula che utilizza il filtraggio croce è il fatto che si calcola il numero di mesi disponibili anche quando si filtro con altri attributi Se, ad esempio, si seleziona il colore blu con una affettatrice, quindi vendite iniziano nel luglio 2007, non nel 2005, come accade per molti altri colori utilizzando il filtro croce sulle vendite, la formula corretta calcola che nel luglio 2007 vi è un solo mese di vendita disponibili per Blue. At questo punto, la media mobile è solo una divisione away. When usiamo in una tabella pivot, abbiamo ancora un piccolo problema, infatti, il valore è calcolato anche per i mesi per i quali non ci sono vendite cioè months. This future possono essere risolti usando un'istruzione IF per evitare che la formula di mostrare i valori quando non ci sono le vendite non ho nulla contro IF, ma, per la prestazione-addicted in mezzo a voi, che è sempre la pena ricordare che se potrebbe essere un killer di prestazioni, perché potrebbe costringere motore formula DAX a calci in questo caso specifico, la differenza è trascurabile, ma, come regola generale, il modo migliore per rimuovere il valore quando non ci sono vendite è di affidarsi a formule motore di memorizzazione puri come questo oneparing un grafico utilizzando il Avg12M con un altro che mostra le vendite si può facilmente apprezzare come le dolci tendenze medie contorni in una molto più pulito way. Keep me informato sulle prossime articoli newsletter Deselezionare per scaricare gratuitamente il file. DAX include alcune funzioni di aggregazione statistici, come la media, la varianza e deviazione standard Altri calcoli statistici tipici richiede di scrivere più a lungo DAX espressioni Excel, da questo punto di vista, ha una molto più ricca Patterns language. The statistici sono un raccolta di comuni calcoli statistici mediana, moda, media mobile, percentile, e quartile Vorremmo ringraziare Colin Banfield, Gerard Brueckl, e Javier Guilln, il cui blog ispirato alcune delle seguenti pattern patterns. Basic formule Example. The in questo modello sono le soluzioni specifiche calculations. You statistica possono utilizzare funzioni standard DAX per calcolare la media aritmetica dei valori di un insieme di values. AVERAGE restituisce la media di tutti i numeri in una column. AVERAGEA numerica restituisce la media di tutti i numeri in una colonna, la gestione dei valori testo e non numerici valori di testo non numerici e vuoti contano come 0.AVERAGEX calcolare la media su un'espressione valutata su una media mobile table. Moving Average. The è un calcolo per analizzare i punti di dati con la creazione di una serie di medie di diversi sottoinsiemi di dati set completo è possibile utilizzare molte tecniche DAX per implementare questo calcolo la tecnica più semplice sta usando AVERAGEX, l'iterazione di una tabella di granularità desiderato e calcolando per ogni iterazione l'espressione che genera il punto di dati singolo da utilizzare nel medio ad esempio, la seguente formula calcola la media mobile degli ultimi 7 giorni, partendo dal presupposto che si sta utilizzando una tabella data data i dati model. Using AVERAGEX, si calcola automaticamente la misura ad ogni livello di granularità Quando si utilizza una misura che può essere aggregata tale come SUM, poi un altro approccio basato sulla cALCOLARE può essere più veloce È possibile trovare questo approccio alternativo nel modello completo di Moving Average. You può utilizzare le funzioni standard DAX per calcolare la varianza di un insieme di values. VAR S restituisce la varianza dei valori in una colonna che rappresenta un campione population. VAR P restituisce la varianza dei valori di una colonna che rappresenta l'intero population. VARX S restituisce la varianza di un'espressione valutata su una tabella che rappresenta un campione population. VARX P restituisce la varianza di un'espressione valutata su una tavolo che rappresenta l'intero population. Standard Deviation. You può utilizzare le funzioni standard DAX per calcolare la deviazione standard di un insieme di values. STDEV S restituisce la deviazione standard dei valori in una colonna che rappresenta un campione population. STDEV P restituisce la deviazione standard dei valori in una colonna che rappresenta l'intero population. STDEVX S restituisce la deviazione standard di un'espressione valutata su una tabella che rappresenta un campione population. STDEVX P restituisce la deviazione standard di un'espressione valutata su una tabella che rappresenta l'intero mediana population. The è il valore numerico separando la metà superiore di una popolazione dalla metà inferiore Se vi è un numero dispari di righe, la mediana è il valore centrale classificare le righe dal valore più basso al valore più alto Se c'è un numero di righe, è la media dei due valori intermedi la formula ignora valori vuoti, che non sono considerati parte della popolazione il risultato è identica alla funzione mediano Excel. Figure 1 mostra un confronto tra il risultato restituito da Excel e la corrispondente formula DAX per il calcolo mediana. figure 1 Esempio di calcolo mediana in modalità Excel e DAX. The è il valore visualizzato più spesso in un insieme di dati la formula ignora valori vuoti, che non sono considerati parte della popolazione il risultato è identico al MODE e funzioni Excel, che restituiscono solo il valore minimo quando ci sono più modi nel set di valori considerati la funzione di Excel restituirebbe tutti i modi, ma non è possibile implementare come una misura in DAX. Figure 2 confronta il risultato restituito da Excel con il corrispondente formula DAX per la modalità calculation. Figure 2 Esempio di calcolo modalità in Excel e DAX. The percentile è il valore sotto al quale una data percentuale di valori in un gruppo scende la formula ignora valori vuoti, che non sono considerati parte della popolazione il calcolo DAX richiede diversi passaggi, descritti nella sezione motivo completo, che mostra come ottenere gli stessi risultati delle funzioni PERCENTILE Excel, and. The quartili sono tre punti che dividono un insieme di valori in quattro gruppi uguali, ciascun gruppo comprendendo un quarto dei dati è possibile calcolare la quartili utilizzando il modello percentile, seguendo questi quartile correspondences. First basso quartile 25 percentile. Second quartile mediana 50 ° percentile. Third quartile quartile superiore 75 ° percentileplete Pattern. A alcuni calcoli statistici hanno una descrizione più del modello completo, perché si potrebbe avere diverse implementazioni a seconda dei modelli di dati e altri requirements. Moving Average. Usually a valutare la media mobile facendo riferimento al livello di granularità giorno il modello generale della seguente formula ha queste markers. numberofdays è il numero di giorni per il average. datecolumn in movimento è la colonna della data della tabella data, se ne avete uno, o la colonna data della tabella contenente i valori se non vi è alcuna data table. measure separata è la misura per calcolare il movimento average. The più semplice modello utilizza la funzione AVERAGEX in DAX, che considera automaticamente solo i giorni per i quali esiste un value. As alternativa, è possibile utilizzare il seguente modello in modelli di dati senza una tabella data e con una misura che può essere aggregati come somma su l'intero periodo considered. The formula precedente considera una giornata senza dati corrispondenti come una misura che ha valore 0 Questo può accadere solo quando si dispone di una tabella data separato, che potrebbe contenere giorni per i quali non vi sono operazioni corrispondenti è possibile risolvere il denominatore per la media utilizzando solo il numero di giorni per i quali vi sono operazioni utilizzando il seguente schema, where. facttable è la tabella correlata alla tabella data e valori contenenti eseguite dalla measure. You potrebbe utilizzare le funzioni DATESBETWEEN o DATESINPERIOD anziché FILTER, ma questi funzionano solo in una tabella appuntamento fisso, mentre è possibile applicare il modello sopra descritto anche alle tabelle di data non regolari e di modelli che non hanno una data table. For esempio, prendere in considerazione i diversi risultati prodotti dai seguenti due misure. nella Figura 3, si può vedere che non ci sono vendite su 11 settembre 2005, tuttavia, questa data è incluso nella tabella data così, ci sono 7 giorni dall'11 settembre al 17 settembre, che hanno solo 6 giorni con data. Figure 3 Esempio di un calcolo della media mobile in considerazione e ignorando le date con nessuna misura sales. The Moving media 7 giorni ha un numero minore tra 11 settembre e il 17 settembre, in quanto considera dell'11 settembre come un giorno con 0 vendite Se si desidera ignorare giorni con nessuna vendita , quindi utilizzare la misura media mobile 7 giorni nessuna Zero Questa potrebbe essere l'approccio giusto quando si dispone di una tabella data completa, ma si desidera ignorare giorni senza transazioni utilizzando la media mobile 7 giorni formula, il risultato è corretto perché AVERAGEX considera automaticamente solo values. Keep non vuota in mente che si potrebbe migliorare le prestazioni di una media mobile persistendo il valore in una colonna calcolata di un tavolo con la granularità desiderato, come ad esempio la data, o la data e il prodotto Tuttavia, l'approccio di calcolo dinamico con un misura offre la possibilità di utilizzare un parametro per il numero di giorni di media mobile ad esempio sostituire NUMBEROFDAYS con una misura di esecuzione della mediana Parametri Tabella pattern. The corrisponde al 50 ° percentile, che si può calcolare utilizzando il modello percentile Tuttavia, la mediana modello consente di ottimizzare e semplificare il calcolo mediana utilizzando una singola misura, al posto delle diverse misure previste dal modello percentile È possibile utilizzare questo metodo quando si calcola la mediana dei valori inclusi nel valuecolumn, come mostrato below. To migliorare le prestazioni, è potrebbe voler persistere il valore di una misura in una colonna calcolata, se si vuole ottenere il mediano per i risultati di una misura del modello di dati, tuttavia, prima di fare questa ottimizzazione, è necessario implementare il calcolo MedianX basato sul seguente modello, utilizzando questi markers. granularitytable è la tabella che definisce la granularità del calcolo, ad esempio, potrebbe essere la tabella di Data se si desidera calcolare la mediana di una misura calcolata a livello di giorno, o potrebbe essere valori di data annoMese se si desidera calcolare la mediana di una misura calcolata al mese level. measure è la misura di calcolare per ogni riga granularitytable per la calculation. measuretable mediana è la tabella contenente dati utilizzata dal provvedimento ad esempio, se il granularitytable è una dimensione come la Data , allora il measuretable sarà Internet Sales contenenti la colonna Importo Internet Sales riassunta con l'esempio di Internet totale vendite measure. For, è possibile scrivere la mediana di Internet totale vendite per tutti i clienti di Adventure Works come follows. Tip i seguenti pattern. is utilizzato per rimuovere le righe da granularitytable che non dispongono di dati corrispondenti nella selezione corrente è un modo più veloce di utilizzare il seguente expression. However, si potrebbe sostituire l'intera espressione CALCULATETABLE con appena granularitytable se si vuole considerare valori vuoti della misura come 0.il prestazioni della formula MedianX dipende dal numero di righe della tabella iterato e sulla complessità della misura Se le prestazioni sono male, si potrebbe persistere il risultato misura in una colonna calcolata della tabella, ma questo rimuoverà la capacità di applicare filtri per il calcolo mediana alla interrogazione time. Excel ha due diverse implementazioni di calcolo del percentile con tre funzioni percentile, e tutti restituire il percentile K-esimo di valori, dove K è compreso nell'intervallo 0-1 la differenza è che percentile e considerare K come un intervallo compreso, mentre considera la gamma K 0 a 1 come exclusive. All di queste funzioni e implementate DAX ricevono un valore percentile come parametro, che noi chiamiamo KK percentile è nel range da 0 a 1. due DAX implementazioni di percentile richiedono alcune misure che sono simili, ma abbastanza differente da richiedere due differenti serie di formule le misure definite in ciascun modello are. KPerc il valore percentile corrisponde K. PercPos la posizione del percentile nel set ordinato di valori. ValueLow il valore al di sotto del percentile position. ValueHigh il valore al di sopra del percentile position. Percentile il calcolo finale del percentile. You bisogno delle misure ValueLow e ValueHigh nel caso in cui i PercPos contiene una parte decimale, perché poi si deve interpolare tra ValueLow e ValueHigh al fine di restituire la giusta percentile value. Figure 4 mostra un esempio di calcoli effettuati con formule di Excel e Dax, utilizzando entrambi gli algoritmi di calcolo percentile inclusivi e exclusive. Figure 4 percentili utilizzando formule di Excel e l'equivalente DAX calculation. In il seguente le sezioni, le formule percentili eseguire il calcolo su valori memorizzati in una colonna della tabella, dati valore, mentre le formule PercentileX eseguire il calcolo sui valori restituiti da una misura calcolata in un dato esecuzione granularity. Percentile Inclusive. The percentile Inclusive è la following. Percentile attuazione esclusive. Completano percentile Exclusive è l'attuazione following. PercentileX Inclusive. The PercentileX compreso si basa sulla seguente modello, utilizzando questi markers. granularitytable è la tabella che definisce la granularità del calcolo Ad esempio, potrebbe essere la tabella Data se vuole calcolare il percentile di una misura a livello di giorno, o potrebbe essere valori di data annoMese se si vuole calcolare il percentile di un provvedimento al mese level. measure è la misura di calcolare per ogni fila di granularitytable per il calcolo percentile. measuretable è la tabella contenente i dati utilizzati da misura Ad esempio, se il granularitytable è una dimensione come la data, allora il measuretable sarà di vendita contenenti la colonna Importo riassunta con l'esempio Importo totale measure. For, è possibile scrivere la PercentileXInc della somma totale delle vendite per tutte le date nella tabella Data in attuazione follows. PercentileX esclusive. Completano PercentileX Exclusive si basa sulla seguente modello, utilizzando gli stessi marcatori usati nella PercentileX Inclusive. For esempio, è possibile scrivere la PercentileXExc di importo totale delle vendite per tutte le date nella tabella Data come follows. Keep mi ha informato sui modelli di prossima newsletter Deselezionare per scaricare liberamente il file. Published il 17 marzo 2014.