Mover Média Powerpivot

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 31 03 2010. 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 pode me convencer por que codificar a condição Falso como BLANK () é a melhor prática, vou mudar rapidamente meus hábitos. Este post tem 6 comentários Renato Lyke diz: Post navigation Calculando uma média móvel em PowerPivot Duas semanas atrás, prometi falar sobre como gerar uma média móvel no PowerPivot, mas na semana passada eu me desviei informando sobre uma ótima maneira de exibir vídeos do YouTube em suas páginas do SharePoint usando uma parte da web encontrada no CodePlex. Os membros da minha equipe de trabalho encontraram. Era tão fácil de implementar, eu só tinha que compartilhá-lo com todos vocês. No entanto, retornando ao tópico do cálculo de uma média móvel, a primeira pergunta pode ser o que é uma média móvel e, em seguida, por que você quer usar uma. Uma média móvel é simplesmente a soma de dois ou mais valores dependentes do tempo em que a soma é então dividida pelo número de valores utilizados. Por exemplo, se eu estivesse falando sobre os preços das ações, talvez queira usar algo como uma média móvel de 7 dias para atenuar o efeito de picos de dia individuais ou quedas no preço das ações que não são indicativos da tendência geral de estoque. (Alguns investidores de longo prazo usam médias móveis ainda mais longas.) Isso não significa que, se um estoque cair ou diminuir, eu me sentaria até que a média móvel me diga para agir. Qualquer investidor de boa ação irá dizer-lhe que existem muitos outros fatores internos e externos a uma empresa que poderia forçar sua mão a vender ou a comprar qualquer estoque específico. Mas o ponto é, e esta é a resposta para a segunda pergunta, uma média móvel diminui a aleatoriedade para que eu possa ver mais facilmente o padrão geral dos números que estou rastreando. Ok, então suponho que eu trabalho para a Contoso e queria saber se as vendas estão subindo, caindo ou geralmente planas. Se eu olhar para as vendas diárias, é provável que os números flutuem para cima e para baixo em nenhum padrão específico impedindo-me de detectar uma tendência geral. A figura a seguir mostra as vendas contadas diariamente da Contoso durante um período de 3 meses durante o verão de 2008. Eu escolhi mostrar os dados como um gráfico para ajudar a mostrar como as vendas flutuam ao dia revelando informações que eu não poderia ver tão facilmente que eu Criou uma tabela dos mesmos valores. Claro, eu poderia traçar um ano inteiro ou mais, mas para ver dias individuais, eu teria que ampliar o gráfico substancialmente. No entanto, mesmo com este período de tempo menor, posso ver que as vendas flutuam bastante bem. Mas eu posso pedir que as vendas aumentem, diminuam ou permaneçam iguais. Se eu tiver um bom olho, posso dizer que as vendas atingem o pico em relação ao final de julho e, em seguida, recuam um pouco enquanto o gráfico se move em agosto. Mas isso não é tão óbvio quanto o fato de que há uma grande quantidade de flutuações diárias. Então, como posso exibir as tendências visualmente com Moving Average Sales. Agora, com o objetivo desta ilustração, vou criar uma média móvel de quatro dias, mas honestamente, não há um número certo de períodos em uma média móvel. Na verdade, eu deveria experimentar diferentes períodos de tempo para ver qual período de tempo me permite detectar não apenas as tendências gerais, mas também neste caso, onde estou exibindo vendas na loja, nas mudanças sazonais. Eu já sei que se eu exibir dados por dia, posso usar a seguinte fórmula para calcular as vendas diárias de apenas nosso canal de loja. (Sim, eu poderia simplesmente usar o SalesAmount e aplicar um slicer de canais para usar apenas as vendas da loja, mas deixa o exemplo.) Posso usar essa medida calculada para calcular as vendas de dias anteriores para qualquer dia criando a seguinte medida. StoreSales1DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -1, dia)) Você pode adivinhar que a fórmula para calcular as vendas há dois dias e três dias, respectivamente, são: StoreSales2DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, - 2, dia)) StoreSales3DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -3, dia)) Com estes quatro valores calculados para cada dia, posso calcular a soma destes valores e dividir por 4 para obter uma média móvel de 4 dias usando O seguinte valor calculado: FourDayAverage: (StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo) 4.0 Agora, se eu voltar para a minha página de gráfico, eu deveria ver que o Excel atualiza a lista de campos para incluir as novas medidas calculadas. Se eu adicionar o campo FourDayAverage à caixa Valores criando uma segunda série no gráfico, agora tenho as vendas diárias reais e a média móvel de quatro dias exibida no mesmo gráfico. O único problema é que eu também gostaria de alterar o formato do gráfico para exibir as vendas diárias (minha primeira série de dados) como colunas e minha média móvel (minha segunda série de dados) como uma linha. Quando eu clicar com o botão direito do mouse no gráfico e selecionar Alterar tipo de gráfico, eu posso selecionar Combo como o tipo de gráfico como mostrado na figura a seguir. Nesse caso, o gráfico da Linha de Colunas em Cluster é exatamente o que eu quero. Como adicionei a série de média móvel à última área de Valores, torna-se, por padrão, a Linha e todas as outras séries de dados aparecem como colunas em cluster. Como eu só tenho um valor para cada dia, o gráfico mostra uma coluna individual por dia. Se eu tivesse inserido minhas séries de dados na área de Valores na ordem errada, eu poderia simplesmente usar essa caixa de diálogo para selecionar o tipo de gráfico para cada série. Quando eu clico em OK nesta caixa de diálogo, meu gráfico agora se parece com o seguinte, o que mostra mais claramente a maior tendência geral e menor flutuação diária. Mas espere, existe uma maneira mais fácil de fazer isso. Por que sim existe. Mas para aprender a fazer isso, você terá que esperar até a semana que vem. Postar navegação Meus arquivos Assinatura de e-mail Tópicos sobre os quais eu falo