Powerpivotpro Média Móvel

Médias móveis avançadas Sempre acreditei que as médias móveis provavelmente dão uma melhor visão das tendências dentro de uma empresa do que uma linha de tendência simples associada a um conjunto de valores, como as vendas mensais (embora eu tende a rever esses dois valores juntos). A razão para isso é que uma tendência pode ser desviada por um ou dois valores que podem não ser representativos do negócio subjacente, como picos associados à sazonalidade ou a um evento específico. Quando BillD destacou uma consulta sobre esse conceito em seus comentários sobre perda de amplificador de lucro (parte 2), compara e analisa. Eu pensei que seria uma ótima idéia flexionar nosso conjunto de dados PampL para fornecer uma capacidade de Mover em Mudança. Nesta publicação, vou explicar o que as médias móveis destinam-se a fornecer e explicar como calculá-las usando os elementos de vendas dos dados de exemplo usados ​​na série Perituário de perda de postagem. Em seguida, adicionarei a flexibilidade para que os usuários selecionem o período de tempo que o cálculo da média móvel deve considerar, o número de períodos de tendência a serem exibidos e a data de término do relatório. O que é uma média móvel A medida média móvel mais comum é geralmente referida como uma média móvel de 12 meses. No caso de nossos dados de vendas, para um determinado período, esta medida somaria os últimos 12 meses de vendas anteriores e inclusive o mês em análise e depois dividir em 12 para mostrar um valor médio de vendas para esse período. Em termos financeiros, a equação é, portanto, bastante simples: Soma média móvel de 12 meses para os últimos 12 meses 12 Isso parece muito direto, mas há muita complexidade envolvida se quisermos colocar o prazo médio em movimento (representado como 12 em O exemplo acima) nas mãos do usuário, dê-lhes o poder de selecionar o número de períodos de tendência a serem exibidos e o mês em que o relatório deve ser exibido. O conjunto de dados O conjunto de dados que estava sendo usado parece algo abaixo. Tenho em atenção que estou usando o PowerPivot V1. O visualizador de design está disponível no V2, mas Ive trouxe isso em conjunto, nada inteligente. Você notará que o FACTTran (nosso conjunto de dados a ser analisado) está vinculado ao DIMHeading1, DIMHeading2 e DIMDataType para fornecer uma categorização em nosso conjunto de dados. Eu também liguei para Datas, que é um conjunto seqüencial de datas que mais do que cobre o período de tempo de nosso conjunto de dados. Esta tabela contém algumas informações adicionais estáticas com base na data: Mais uma vez, não estavam se registrando na escala picante de Robs. Tenha certeza de que você estará recebendo um treino DAX mais intenso à medida que avançamos. Como essas medidas de data não devem ser dinâmicas, eu as codifiquei na janela do PowerPivot. Isso permite que eles sejam calculados na atualização do arquivo, mas eles não precisarão recalcular para cada operação de slicer que remova a sobrecarga de desempenho da nossa medida dinâmica final. Por razões que acabei de acontecer, também preciso da data de término do mês na minha tabela de fatos, pois não uso a Data de término do mês na tabela Datas nas minhas medidas. No entanto, posso puxar o mesmo valor para a minha tabela FACTTran usando a seguinte medida: Então, quais são essas tabelas de MA não vinculadas O motivo para essas tabelas deve tornar-se aparente à medida que avançamos. Em resumo, eles serão usados ​​como parâmetros ou títulos em nosso relatório. A razão pela qual eles existem e que eles não estão ligados ao resto de nossos dados é simplesmente porque eu não quero que eles sejam filtrados por nossas medidas. Em vez disso, eu quero que eles façam a filtragem. Configuração inicial da tabela dinâmica Será exibida uma série de dados organizados em colunas mensais. O usuário receberá trituradores para definir a Data de término do mês (o último período a ser exibido no relatório), o número de períodos para a média móvel (isso, em última análise, será parte do cálculo do divisor) e o número de períodos da Tendência (isto será O número de colunas mensais que exibiremos em nossa tendência). Podemos estabelecer esses trituradores de imediato e ligá-los ao pivô. Eu, obviamente, preciso de uma data de término do mês como um título de coluna, mas em que, até certo ponto, eu tenho dado isso anteriormente. Em suma, eu preciso usar o meu campo MADatesMonthEndDate. A razão é que este campo não está vinculado ao nosso conjunto de dados e, portanto, não será afetado por nenhum outro filtro. Se eu usar um campo de data que faz parte do meu conjunto de dados ou parte de uma tabela vinculada, os valores disponíveis podem ser filtrados pelas seleções dos usuários. Eu posso contornar isso usando uma expressão ALL () para me dar os valores corretos, mas o problema é que a coluna ainda está filtrada e meus resultados serão todos exibidos em uma coluna. É difícil explicar até você vê-lo, então, vá em frente e tente valer a pena bater na parede de tijolos para realmente entender. Cálculo da Soma de Vendas para os Últimos X Meses. A primeira parte da nossa equação é calcular o valor total das vendas em todos os períodos dentro Um período de tempo dinâmico a ser selecionado pelo usuário. Para isso eu uso uma função de cálculo que se parece com isso: estou usando uma medida de base chamada CascadeValueAll que foi criada na perda do amplificador de lucro A arte do subtotal em cascata. Im, então, filtrando essa medida para limitar meu conjunto de dados para registros relacionados a Vendas e um tipo de dados de Real (ou seja, eliminar o Orçamento). Esta é uma simples filtragem de uma função CALCULATE. No entanto, fica um pouco mais saboroso com o terceiro filtro que limita o conjunto de dados a uma série de datas que dependem das seleções de usuários nos slicers e nosso cabeçalho de coluna da data. A função DATESBETWEEN possui a sintaxe DATESBETWEEN (datas, startdate, enddate) e funciona assim: configure o campo que requer filtragem (DatesData). Descobri que isso funciona melhor se esta é uma tabela vinculada de datas seqüenciais sem quebras. Se você tiver algum intervalo, há uma chance de você não conseguir uma resposta, pois a resposta que você avalia deve estar disponível na tabela. Minha data de início é uma função DATEADD que calcula a data do título da coluna, menos o número de meses que o usuário selecionou no sicerista da Mudança da média média não. Eu uso a função LASTDATE (VALUES (MADatesNextMonthStartDate)) para recuperar o valor NextMonthStartDate da tabela MADates que se relaciona com a data representada no cabeçalho da coluna. Depois, rebobino pelo número de meses selecionado no cortador usando MAX (MAFunctionPeriodsMovingAverageNoPeriods) -1. O -1 é usado para voltar no tempo. A razão pela qual eu uso NextMonthStartDate e um múltiplo de 1 é mais claramente explicado em Slicers para selecionar os últimos períodos de X. Minha data final é simplesmente o MonthEndDate, conforme mostrado no cabeçalho da coluna do relatório. Isso é calculado usando LASTDATE (VALUES (MADatesMonthEndDate). Isso é ótimo, mas minha medida não está levando conta de meus Períodos de Exibição para Seleção e da Tendência de Períodos que eu selecionei. Portanto, precisamos limitar a medida para executar somente quando certo Os parâmetros mantêm-se como verdadeiros com base nessas seleções. Eu só quero que os valores sejam exibidos quando a minha data do título da coluna for: Menor ou igual à Data de término do mês selecionada nos meus períodos de exibição até o cortador ET Maior ou igual ao final do mês selecionado Data MENOS o número de períodos selecionados no meu Trend No of Period slicer. Para fazer isso, eu uso uma instrução IF para determinar quando minha função CALCULATE deve ser executada. Ligue essa medida SalesMovingAverageTotalValue A instrução IF funciona da seguinte maneira: primeiro preciso determinar Que eu estou avaliando apenas onde eu tenho um valor para MADateMonthEndDate. Se eu não fizer isso, eu recebo esse antigo erro favorito na minha avaliação subseqüente que diz que uma tabela de múltiplos valores foi fornecida I Em seguida, avalie para determinar se a minha data de título da coluna (VALUES (MADatesMonthEndDate) é menor ou igual à data selecionada no cortador do Período final do mês (LASTDATE (datasDateMonthEnd) AND (ampamp) A minha data do título da coluna é maior ou igual a uma calculada Data que é X períodos anteriores aos Períodos de exibição selecionados até como selecionados no Slicer. Eu uso uma função DATEADD por isso semelhante à usada na minha função CALCULATE, exceto se ajustando a data pelo valor selecionado no Trend No of Periods slicer. Com isso, temos as vendas totais do período selecionado em relação às seleções dos usuários. Então, minha tabela agora está limitada ao número de períodos de tendência selecionados e representa a data de término do mês selecionada. Então, agora, apenas dividimos por meio da média móvel dos períodos direito eh NÃO Weve calculou nossas vendas totais no período referente às seleções dos usuários. Você seria perdoado por sugerir que simplesmente dividimos pelo número de períodos médios móveis selecionados. Dependendo dos seus dados, você pode fazer isso, mas o problema é que o conjunto de dados pode não conter o número selecionado de períodos, especialmente se o usuário pode selecionar uma data de término do mês que volte no tempo. Como resultado, precisamos descobrir como os períodos estão presentes na nossa medida SalesMovingAverageTotalValue. Essa medida é essencialmente a mesma da minha medida SalesMovingAverageTotal. A única diferença real é que contamos os valores de data distintos em nosso conjunto de dados ao invés de chamar a medida CascadeValueAll. Eu mencionei anteriormente que havia uma razão pela qual eu precisava da data final do mês para ser realizada na minha tabela FACTTran e é por isso que. Se eu usar qualquer outra tabela segurando a data final do mês, essa tabela não será filtrada da maneira como o conjunto de dados principal foi filtrado. Como exemplo, minha tabela Datas possui uma série de datas que abrange o prazo do meu conjunto de dados e muito mais. Como resultado, a avaliação em relação a essa tabela irá deduzir que a tabela possui datas que precedem meu conjunto de dados e, portanto, não há avaliação quanto à existência de uma transação realizada no conjunto de dados para essa data. Como você pode ver, desde o meu conjunto de dados a partir de 1 de julho de 2009, eu só tenho 9 períodos de dados para avaliar a minha coluna 31032010. Se eu tivesse dividido por 12 (de acordo com a minha seleção de cortador de períodos médios móveis), eu teria uma resposta muito errada. Obviamente, isso é levemente inventado, mas é digno de consideração. E agora o bit simples Eu posso entender que as duas últimas medidas levaram algum absorção, especialmente trabalhando quando os campos de data específicos deveriam ser usados. Para um pouco de alívio leve, a próxima medida não irá taxá-lo. Esta é uma divisão simples com um pouco de verificação de erros para evitar qualquer surto. Quando tudo é colocado juntos. Todas essas medidas são portáteis, eu posso criar outra tabela dinâmica na mesma base que a acima (com SalesMovingAverageValue dado um alias da média móvel), mover algumas coisas, adicionar uma medida para as vendas reais Valor para o mês (eu não vou entrar agora, mas é uma medida de cálculo simples com algum tempo de inteligência) e eu reconfigurar para parecer o seguinte: Posso então dirigir um gráfico de linha simples e aplicar uma linha de tendência para minha medida real Com o gráfico convenientemente escondendo minha grade de dados que o dirige. Como você pode ver, uma tendência na minha medida real mostra um declínio constante. Minha média móvel, no entanto, mostra uma tendência relativamente estável, se não ligeiramente melhorando. Normalmente, a sazonalidade de alguns outros picos está envolvida e a realidade é que ambas as medidas provavelmente precisam ser revisadas lado a lado. Para aqueles que lêem isso que estão interessados ​​em ver a pasta de trabalho deste exemplo, vou olhar para publicar isso em uma publicação futura, quando eu levar essa análise um passo adiante para cobrir todo o PampL. Desculpe fazer você esperar. Espero que isso ajude você a descobrir o BillD One More Point. Essas profissões de DAX com águia lá fora, provavelmente perceberam que minhas funções IF apenas contêm um cálculo para avaliar quando o teste lógico atinge uma resposta verdadeira. A razão é que a função assume BLANK () quando uma condição de avaliação falsa não é fornecida. Eu não trabalhei se houver algum impacto no desempenho usando este método em grandes conjuntos de dados. Depende de você o que você escolheu fazer e se alguém puder me convencer por que codificar a condição Falso como BLANK () é a melhor prática, mudarei rapidamente meus hábitos. Este post tem 6 comentários Renato Lyke diz: acabei de encontrar essa postagem no blog Por Bill Anton, onde ele discute várias abordagens para calcular a Mediana de um determinado conjunto em T-SQL, MDX e DAX. No final de sua postagem quando se trata do cálculo DAX, ele faz referência a várias postagens de Marco. Alberto e Javier (post1. Post2) que já abordam esse tipo de cálculo no DAX. Mas ele também afirma que não das soluções é elegante. Bem, motivo suficiente para eu tentar por conta própria e aqui está o que eu criei. Depende de você decidir se esta solução é mais elegante do que os outros ou não. Em geral, o cálculo mediano sempre varia de acordo com o número de itens e se este é igual ou impar. Para uma população uniforme, a mediana é a média dos valores no meio: a mediana de 3. 5. 7. 9 é is (5 7) 2 6 Para uma população estranha, a mediana é o valor no meio: a mediana de é 5 Em ambos os casos, os valores devem ser ordenados antes do cálculo. Observe que não faz diferença se os valores são classificados em ordem ascendente ou decrescente. Neste exemplo, nosso conjunto contém 12 itens (meses), então nós temos que encontrar os 2 itens no meio do conjunto ordenado de dezembro e fevereiro e calcular a média. Então, como podemos abordar este problema usando o DAX. A maioria das postagens que mencionei acima usam algum tipo de combinação de classificação RANKX () e filtro FILTRO (). Para minha abordagem, eu usarei nenhum destes, mas use TOPN em vez disso (sim, eu realmente gosto dessa função, como você provavelmente sabe se você seguiu o meu blog há algum tempo). Neste caso especial, o TOPN () pode fazer os dois, classificar e filtrar para nós. Mas, em primeiro lugar, precisamos saber quantos itens existem no nosso set: CntMonths: DISTINCTCOUNT (8216Date8217 Month) Este valor será posteriormente usado em nossos próximos cálculos. Para encontrar o (s) valor (s) no meio eu uso TOPN () duas vezes, primeiro para obter a primeira metade dos itens (semelhante ao TopCount) e, em seguida, uma segunda vez para obter os últimos valores que precisamos para o nosso cálculo médio (similar Para BottomCount): Como o cálculo mediano é diferente para conjuntos pares e ímpares, isso também deve ser considerado em nosso cálculo. Para ambos os cálculos, MOD () - a função é usada para distinguir os dois casos: ItemsTopCount: IF (MOD (CntMonths, 2) 0, (CntMonths 2) 1, (CntMonths 1) 2) Para um número par de itens (por exemplo, 12) nós Simplesmente divida a contagem de itens em 2 e adicione 1, o que nos dá um (12 2) 1 7 para a nossa amostra. Para um número ímpar de itens (por exemplo, 5), primeiro adicionamos 1 à nossa contagem de itens e, em seguida, divida por 2, o que nos dá (5 1) 2 3 ItemsBottomCount: IF (MOD (CntMonsths, 2) 0, 2, 1) Para Um número par de itens que temos para considerar os últimos 2 valores, enquanto que para um número ímpar de itens, só temos que considerar o último valor. Estes cálculos são utilizados em nosso cálculo mediano: Median SA Months: CALCULATE (SumSA, TOPN (ItemsBottomCount, TOPN (ItemsTopCount, VALUES (8216Date8217 Month), SumSA), SumSA -1)) ItemsBottomCount Como o DAX não possui BOTTOMN incorporado ( ), Precisamos abusar da função TOPN () e multiplicar o valor OrderBy por 1 para obter a funcionalidade BOTTOMN (). Como você pode ver, a maioria da lógica já é tratada pelas nossas medidas ItemsTopCount e ItemsBottomCount e esse padrão pode ser reutilizado com muita facilidade. Claro que todos esses cálculos também podem ser combinados e o uso de IF () pode ser evitado: Median SA Months v2: CALCULATE (SumSA, TOPN (2 8211 MOD (CntMonths, 2), TOPN ((CntMonths 1) 2, VALUES ( 8216Date8217 Month), SumSA), SumSA -1)) (2 8211 MOD (CntMonsths, 2)) Nota: para uma população uniforme (CntMonths 1) 2 retorna X.5 que é automaticamente arredondado quando é usado em uma função que Espera um número inteiro. No nosso exemplo, isto é o que acontece: (12 1) 2 6,5 gt 7 Estes são os resultados finais: Podemos usar AVERAGEX () para calcular nossa mediana, mas acho que é algum tipo de sobrecarga para usar AVERAGEX () apenas para Divida em 1 ou 2, dependendo do número de itens que nossas funções TOPN retornam: Median SA Months AvgX: AVERAGEX (TOPN (2-MOD (CntMonths, 2), TOPN ((CntMonsths 1) 2, VALUES (8216Date8217 Month), SumSA), SumSA -1), SumSA) Como você pode ver, existem várias abordagens para calcular a mediana, depende de você que você mais gosta. Não testei nenhum deles em termos de desempenho em conjuntos maiores, isso pode ser tópico para uma próxima publicação. Download do modelo final (Office 2013) Isso funciona apenas para a dimensão do tempo, eu fiz a solução funcionar para outras dimensões e agregações: Trick é usar o ID da tabela de fatos, IFFACT e SUMMARIZE (8216FFACTS82178217FFACTSIDFACT8217FFACTSMMEASURE) Mediana de MMEASURE: CALCULATE (SUM (MMEASURE) TopN (ItemsBottomCount TopN (ItemsTopCount SUMMARIZE (8216FFACTS82178217FFACTSIDFACT8217FFACTSMMEASURE) MMEASURE) MMEASURE 1)) ItemsBottomCount E isso evitar duplicações de MMEASURE: mediana de medida: Calcule (SUM (MMEASURE) TopN (ItemsBottomCount TopN (ItemsTopCount SUMMARIZE (8216FFACTS82178217FFACTSIDFACT8221tmpMMEASURE8221SUM (8216FFACTSMMEASURE)) tmpMMEASURE) tmpMMEASURE Oi Kazo, obrigado por seus comentários sobre isso, existem dois problemas que vejo com sua solução: 1) usar o colum da tabela de fatos dá resultados diferentes, apenas assumir que você deseja calcular a Mediana por um determinado ano e o mês atual é junho, então Você só terá 6 meses na sua tabela de fato, enquanto você terá 12 meses na sua Horário e, claro, calcular a Mediana ao longo de 6 meses retorna resultados muito diferentes em comparação com 12 meses, eu diria que isso depende muito do requisito de negócios, em geral, ambos os cálculos são válidos 2) fazer a Mediana em uma coluna em seu fato - Tabela exigiria que você movesse esta coluna para a tabela de fatos (por exemplo, Usando RELATED (), o que pode resultar em uma sobrecarga em seu fator e eu substitui o SUMMARIZE () por um ADDCOLUMNS (): ADDCOLUMNS (VALORES (8216FFactsIDFACT), 8220tmpMMEASURE8221, MMEASURE) também você não precisa usar SUM () Novamente se MMEASURES já calcula a Soma sobre sua mesa de fato Oi Gerhard Eu sou um pouco novo para DAX e também preciso de uma função mediana. Não tenho certeza de que as soluções sugeridas aqui possam funcionar para mim. Posso ter um cálculo mediano para uma célula (durante a navegação pivotável do Excel) que funcionaria de forma semelhante a uma função soma ou média (não depende de outra coluna, mas apenas das colunas filtradas na célula excluindo valores em branco). Isto é para calcular medianas em campo de quantidade de hipoteca, qualquer seleção que o usuário tenha feito que deseje ter a mediana para essa seleção. A função precisa lidar com o nó vazio 8216 e também muitas duplicações na quantidade (parece que a função TOPN traz os laços com ele, pode haver milhares de duplicações em campo de quantidade para hipotecas nos EUA etc.) Obrigado Olá Gokhan, a Abordagem I Descrito aqui calcula a mediana com base em uma determinada coluna e a quantidade agregada de uma medida se eu entendi você corretamente. Você precisa que esse cálculo funcione para cada coluna que você pode puxar para a tabela dinâmica. Isso só seria possível se o cálculo for feito Na mais baixa graedrain da mesa de fato é o que você precisa Obrigado pela solução para a Mediana. É possível exibir a mediana final em todos os meses8217 linhas. Ou seja, janeiro a dezembro para todas as 12 linhas 8211 A mediana deve ser 564.035, este cálculo retornará o valor do ano para todos os meses: Median SA Year: CALCULATE (Median SA Months v2, ALL (8216DateMonth), ALL (8216DateMonth Name)). Para você. Obrigado Gerhard pela resposta imediata. Funciona quando não existem duplicatas. Mas meus dados têm muitas duplas que precisam ser levadas em consideração. O que você quer dizer com duplicatas O mesmo valor para dois ou mais meses, isso não deve fazer diferença para o seu cálculo da Mediana. O problema que I8217m tem é que quando existe um empate, ele soma o total dos laços como o valor médio. Por exemplo: 1, 1, 2, 2, 3, 3, a mediana seria 4 em vez de 2. Talvez it8217s porque eu substituí os VALORES da data com toda a minha tabela CALCULAR (SumSA, TOPN (ItemsBottomCount, TOPN (ItemsTopCount, VALUES ( DATA8217), SumSA), SumSA -1)) ItemsBottomCount sim, algumas pessoas já relataram problemas com valores duplicados se você estiver usando Power BI, você também pode usar a função MEDIANA nativa em vez de saudações gentis, - eu tenho um desafio desafiando que eu possa obter Algumas respostas. Eu tenho uma tabela com 3 colunas e a 4ª coluna deve ser uma coluna calculada com base nos dados em 3 colunas. SO Data PO Arr (campo calculado) 123 212013 PO1 123 234 412013 PO1 123 345 512013 PO1 123 Arr Para o SO que tem o mesmo PO, Arr será a primeira data de SO. Você poderia me ajudar a fazer isso usando o DAX. Sua ajuda é grandemente apreciada. Continuaremos a verificar esta página web para quaisquer respostas. Obrigado novamente oi, isso deve funcionar CALCULAR (VALORES (Tabela1SO), TOPN (1, FILTRO (TODOS (Tabela1), PO EARLIER (PO)), Data)), porém acho que também deve haver uma solução usando FIRSTDATE () mas Eu não tenho tempo no momento Gerhard, Posso rematar para o email de Gokhans 11 de agosto de 2013 às 19:07 Ele estava perguntando se ele poderia ter uma versão celular da mediana, e você respondeu apenas possível no menor graedrain da mesa de fato Talvez eu possa expandir sobre isso um pouco que eu tenho uma tabela de fatos (totalmente desnormalizada) que tem força relativa de ações na NYSE. Tem campos para indústria industrial e força relativa. Em uma tabela dinâmica muito simples, eu teria uma hierarquia de 3 camadas para a linha, a força relativa como um valor, e a força avg rel para a coluna. No nível do grão, um relogio de relógios é o str original. No nível da indústria, o avg rel str a média de todos os rel-str dos tickers na indústria. No nível do setor, faça como para a indústria, mas agora para todos os tickers de indústrias no setor. O que você tem para a Mediana. Usando os meses de contagem TOPN, daria o seguinte: 1. Relian original rel str rel Relante original no nível de grãos do ticker Sim 2. median industry rel str. No nível 1 da indústria a partir do nível de grão 3. relin do setor médio. No nível do setor 2 do nível de grãos, a função de hierarquia poderia expandir o escopo para contar os tickers adicionais contar os meses adicionais. É assim que as tabelas dinâmicas obtêm a média para ampliar o alcance, eu acho. Então, acho que estou perguntando se isso é factível Ou é basicamente a razão pela qual a mediana não faz parte das opções básicas de valores da tabela dinâmica E não faz parte do dax Oi Ron, o principal problema é que o DAX não tem muito a fazer cálculos diferentes em diferentes Colunas ou hierarquias de manipulação. A solicitação de Gokhans era fazê-lo dinamicamente para que ele funcione para todas as colunas, o que não é realmente possível, a menos que você defina o cálculo para cada coluna separadamente. No seu caso, você só tem 3 colunas que também estão em uma hierarquia que torna possível a abordagem já descrita por Alberto Ferrari: sqlbiarticlesclever-hierarchy-handling-in-dax Verifique o cálculo do RatioToParent no final da publicação. Você basicamente teria que fazer o mesmo: 1) use ISFILTERED () para descobrir qual nível você está atualmente 2) adote o cálculo para usar VALUES (o próprio DAX não tem conhecimento de nenhuma hierarquia