Cenários DAX no Power Pivot

Esta seção fornece links para exemplos que demonstram o uso de fórmulas DAX nos cenários a seguir.

  • Realizar cálculos complexos

  • Trabalhando com texto e datas

  • Valores condicionais e testes para erros

  • Usando inteligência de tempo

  • Classificação e comparação de valores

Neste artigo

Introdução

Visite o Wiki da Central de Recursos do DAX, onde você pode encontrar todos os tipos de informações sobre o DAX, incluindo blogs, exemplos, whitepapers e vídeos fornecidos por profissionais líderes do setor e pela Microsoft.

Cenários: realizando cálculos complexos

Fórmulas DAX podem realizar cálculos complexos que envolvem agregação personalizadas, filtragem e o uso de valores condicionais. Esta seção fornece exemplos de como começar com cálculos personalizados.

Criar cálculos personalizados para uma tabela dinâmica

CALCULATE e CALCULATETABLE são funções poderosas e flexíveis que são úteis para definir campos calculados. Essas funções permitem alterar o contexto no qual o cálculo será executado. Você também pode personalizar o tipo de agregação ou operação matemática a ser realizada. Consulte os tópicos a seguir para ver exemplos.

Aplicar um filtro a uma fórmula

Na maioria dos locais onde uma função DAX usa uma tabela como argumento, geralmente você pode passar em uma tabela filtrada, usando a função FILTER em vez do nome da tabela ou especificando uma expressão de filtro como um dos argumentos de função. Os tópicos a seguir fornecem exemplos de como criar filtros e como os filtros afetam os resultados das fórmulas. Para obter mais informações, consulte Filter Data in DAX Formulas.

A função FILTER permite especificar critérios de filtro usando uma expressão, enquanto as outras funções são projetadas especificamente para filtrar valores em branco.

Remover filtros seletivamente para criar uma taxa dinâmica

Ao criar filtros dinâmicos em fórmulas, você pode responder facilmente a perguntas como:

  • Qual foi a contribuição das vendas do produto atual para o total de vendas do ano?

  • Quanto essa divisão contribuiu para o lucro total de todos os anos operacionais, em comparação com outras divisões?

As fórmulas que você usa em uma Tabela Dinâmica podem ser afetadas pelo contexto de tabela dinâmica, mas você pode alterar seletivamente o contexto adicionando ou removendo filtros. O exemplo no tópico ALL mostra como fazer isso. Para encontrar a taxa de vendas de um revendedor específico sobre as vendas de todos os revendedores, crie uma medida que calcula o valor do contexto atual dividido pelo valor do contexto ALL.

O tópico ALLEXCEPT fornece um exemplo de como limpar seletivamente filtros em uma fórmula. Ambos os exemplos explicam como os resultados mudam dependendo do design da Tabela Dinâmica.

Para outros exemplos de como calcular taxas e porcentagens, consulte os seguintes tópicos:

Usando um valor de um loop externo

Além de usar valores do contexto atual nos cálculos, o DAX pode usar um valor de um loop anterior na criação de um conjunto de cálculos relacionados. O tópico a seguir fornece um passo a passo de como criar uma fórmula que referencia um valor de um loop externo. A função EARLIER dá suporte a até dois níveis de loops aninhados.

Para saber mais sobre o contexto de linhas e tabelas relacionadas e como usar esse conceito em fórmulas, consulte Context in DAX Formulas.

Cenários: Trabalhando com Texto e Datas

Esta seção fornece links para tópicos de referência do DAX que contêm exemplos de cenários comuns envolvendo trabalhar com texto, extrair e compor valores de data e hora ou criar valores com base em uma condição.

Criar uma coluna de chave por concatenação

Power Pivot não permite chaves compostas; portanto, se você tiver chaves compostas em sua fonte de dados, talvez seja necessário combiná-las em uma única coluna de chave. O tópico a seguir fornece um exemplo de como criar uma coluna calculada com base em uma chave composta.

Compor uma data com base nas partes de data extraídas de uma data de texto

Power Pivot usa um tipo SQL Server data/hora para trabalhar com datas; portanto, se seus dados externos contiver datas formatadas de forma diferente, por exemplo, se suas datas são escritas em um formato de data regional que não seja reconhecido pelo mecanismo de dados do Power Pivot ou se seus dados usarem chaves substitutas inteiros, talvez seja necessário usar uma fórmula DAX para extrair as partes de data e, em seguida, compor as partes em uma representação válida de data/hora.

Por exemplo, se você tiver uma coluna de datas que foram representadas como um inteiro e importadas como uma cadeia de caracteres de texto, você poderá converter a cadeia de caracteres em um valor de data/hora usando a seguinte fórmula:

=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))

Valor1

Resultado

01032009

1/3/2009

12132008

12/13/2008

06252007

6/25/2007

Os tópicos a seguir fornecem mais informações sobre as funções usadas para extrair e redigir datas.

Definir um formato de data ou número personalizado

Se seus dados contiver datas ou números que não são representados em um dos formatos de texto Windows padrão, você poderá definir um formato personalizado para garantir que os valores sejam tratados corretamente. Esses formatos são usados ao converter valores em cadeias de caracteres ou em cadeias de caracteres. Os tópicos a seguir também fornecem uma lista detalhada dos formatos predefinidos que estão disponíveis para trabalhar com datas e números.

Alterar tipos de dados usando uma fórmula

Em Power Pivot, o tipo de dados da saída é determinado pelas colunas de origem e você não pode especificar explicitamente o tipo de dados do resultado, pois o tipo de dados ideal é determinado por Power Pivot. No entanto, você pode usar as conversões implícitas de tipo de dados executadas Power Pivot manipular o tipo de dados de saída. 

  • Para converter uma data ou uma cadeia de caracteres de número em um número, multiplique por 1,0. Por exemplo, a fórmula a seguir calcula a data atual menos 3 dias e, em seguida, o valor inteiro correspondente.

    =(TODAY()-3)*1.0

  • Para converter um valor de data, número ou moeda em uma cadeia de caracteres, concatene o valor com uma cadeia de caracteres vazia. Por exemplo, a fórmula a seguir retorna a data de hoje como uma cadeia de caracteres.

    =""& TODAY()

As seguintes funções também podem ser usadas para garantir que um determinado tipo de dados seja retornado:

Converter números reais em inteiros

Cenário: Valores condicionais e testes para erros

Como Excel, o DAX tem funções que permitem testar valores nos dados e retornar um valor diferente com base em uma condição. Por exemplo, você pode criar uma coluna calculada que rotule revendedores como Preferred ou Value, dependendo do valor anual de vendas. Funções que testam valores também são úteis para verificar o intervalo ou o tipo de valores, para evitar erros inesperados de dados de quebra de cálculos.

Criar um valor com base em uma condição

Você pode usar condições IF aninhadas para testar valores e gerar novos valores condicionalmente. Os tópicos a seguir contêm alguns exemplos simples de processamento condicional e valores condicionais:

Testar erros em uma fórmula

Ao Excel, você não pode ter valores válidos em uma linha de uma coluna calculada e valores inválidos em outra linha. Ou seja, se houver um erro em qualquer parte de uma coluna Power Pivot, a coluna inteira será sinalizada com um erro, de modo que você sempre deve corrigir erros de fórmula que resultem em valores inválidos.

Por exemplo, se você criar uma fórmula dividida por zero, poderá obter o resultado infinito ou um erro. Algumas fórmulas também falharão se a função encontrar um valor em branco quando espera um valor numérico. Enquanto você está desenvolvendo seu modelo de dados, é melhor permitir que os erros apareçam para que você possa clicar na mensagem e solucionar o problema. No entanto, ao publicar as guias de trabalho, você deve incorporar o tratamento de erros para evitar que os valores inesperados causem falha nos cálculos.

Para evitar o retorno de erros em uma coluna calculada, você usa uma combinação de funções lógicas e de informações para testar erros e sempre retornar valores válidos. Os tópicos a seguir fornecem alguns exemplos simples de como fazer isso no DAX:

Cenários: Usando Inteligência de Tempo

As funções de inteligência de tempo do DAX incluem funções para ajudar você a recuperar datas ou intervalos de datas de seus dados. Em seguida, você pode usar essas datas ou intervalos de datas para calcular valores em períodos semelhantes. As funções de inteligência de tempo também incluem funções que funcionam com intervalos de data padrão, para permitir que você compare valores entre meses, anos ou trimestres. Você também pode criar uma fórmula que compare valores para a primeira e última data de um período especificado.

Para uma lista de todas as funções de inteligência de tempo, consulte Funções de Inteligência de Tempo (DAX). Para saber mais sobre como usar datas e horas efetivamente em uma análise Power Pivot, consulte Datas no Power Pivot.

Calcular vendas cumulativas

Os tópicos a seguir contêm exemplos de como calcular saldos de fechamento e abertura. Os exemplos permitem que você crie saldos em execução em intervalos diferentes, como dias, meses, trimestres ou anos.

Comparar valores ao longo do tempo

Os tópicos a seguir contêm exemplos de como comparar somas em diferentes períodos de tempo. Os períodos de tempo padrão suportados pelo DAX são meses, trimestres e anos.

Calcular um valor em um intervalo de datas personalizado

Consulte os tópicos a seguir para obter exemplos de como recuperar intervalos de datas personalizados, como os primeiros 15 dias após o início de uma promoção de vendas.

Se você usar funções de inteligência de tempo para recuperar um conjunto personalizado de datas, poderá usar esse conjunto de datas como entrada para uma função que executa cálculos, para criar agregados personalizados entre períodos de tempo. Consulte o tópico a seguir para ver um exemplo de como fazer isso:

  • Função PARALLELPERIOD

    Observação: Se você não precisar especificar um intervalo de datas personalizado, mas estiver trabalhando com unidades de contabilidade padrão, como meses, trimestres ou anos, recomendamos que você execute cálculos usando as funções de inteligência de tempo projetadas para essa finalidade, como TOTALQTD, TOTALMTD, TOTALQTD, etc.

Cenários: Classificação e comparação de valores

Para mostrar apenas o número n superior de itens em uma coluna ou tabela dinâmica, você tem várias opções:

  • Você pode usar os recursos no Excel 2010 para criar um filtro Top. Você também pode selecionar um número de valores superior ou inferior em uma Tabela Dinâmica. A primeira parte desta seção descreve como filtrar os 10 principais itens em uma tabela dinâmica. Para obter mais informações, consulte Excel documentação.

  • Você pode criar uma fórmula que classifica dinamicamente valores e filtrar pelos valores de classificação ou usar o valor de classificação como uma Slicer. A segunda parte desta seção descreve como criar essa fórmula e, em seguida, usar essa classificação em uma Slicer.

Há vantagens e desvantagens para cada método.

  • O Excel Superior é fácil de usar, mas o filtro é exclusivamente para fins de exibição. Se os dados subjacentes à Tabela Dinâmica mudarem, você deverá atualizar manualmente a Tabela Dinâmica para ver as alterações. Se você precisar trabalhar dinamicamente com classificações, poderá usar o DAX para criar uma fórmula que compare valores a outros valores em uma coluna.

  • A fórmula DAX é mais poderosa; além disso, adicionando o valor de classificação a uma Slicer, você pode clicar na Slicer para alterar o número de valores principais exibidos. No entanto, os cálculos são computaticamente caros e esse método pode não ser adequado para tabelas com muitas linhas.

Mostrar apenas os dez principais itens em uma tabela dinâmica

Para mostrar os valores superior ou inferior em uma tabela dinâmica

  1. Na tabela dinâmica, clique na seta para baixo no título Rótulos de Linha.

  2. Selecione Filtros de Valor> Top 10.

  3. Na caixa de diálogo < nome da coluna>, escolha a coluna a ser classificação e o número de valores, da seguinte forma:

    1. Selecione Superior para ver as células com os valores mais altos ou Bottom para ver as células com os valores mais baixos.

    2. Digite o número de valores superior ou inferior que você deseja ver. O padrão é 10.

    3. Selecione como você deseja que os valores exibidos:

Nome

Descrição

Itens

Selecione essa opção para filtrar a Tabela Dinâmica para exibir apenas a lista dos itens superior ou inferior por seus valores.

Porcentagem

Selecione essa opção para filtrar a Tabela Dinâmica para exibir apenas os itens que adicionam à porcentagem especificada.

Soma

Selecione essa opção para exibir a soma dos valores dos itens superior ou inferior.

  1. Selecione a coluna que contém os valores que você deseja classificar.

  2. Clique em OK.

Solicitar itens dinamicamente usando uma fórmula

O tópico a seguir contém um exemplo de como usar o DAX para criar uma classificação armazenada em uma coluna calculada. Como as fórmulas DAX são calculadas dinamicamente, você sempre pode ter certeza de que a classificação está correta, mesmo se os dados subjacentes foram alterados. Além disso, como a fórmula é usada em uma coluna calculada, você pode usar a classificação em uma Slicer e selecionar os 5 primeiros, os 10 primeiros ou até os 100 principais valores.

Precisa de mais ajuda?

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

×