Agregações no Power Pivot

Importante :  Este artigo foi traduzido por um sistema de tradução automática, leia o aviso de isenção de responsabilidade. Para sua referência, veja a versão em inglês deste artigo aqui.

As agregações são um modo de recolher, resumir ou agrupar dados. Quando você começa com dados raw de tabelas ou outras fontes de dados, eles costumam ser simples, o que significa haver muitos detalhes, mas não organizados ou agrupados de nenhuma forma. Essa falta de resumos ou de estrutura pode dificultar a descoberta de padrões nos dados. Uma parte importante da modelagem de dados é definir agregações que simplifiquem, separem ou resumam padrões em resposta a uma pergunta comercial específica.

Agregações mais comuns, como aqueles usando média, contagem, DISTINCTCOUNT, MAX, MINou soma podem ser criadas em uma medida automaticamente usando AutoSoma. Outros tipos de agregações, como AVERAGEX, COUNTX, COUNTROWSou SUMXretornam uma tabela e exigem uma fórmula criada usando Expressões de análise de dados (DAX).

Noções básicas sobre agregações no Power Pivot

Escolhendo os grupos para agregação

Ao agregar dados, você os agrupa por atributos como produto, preço, região ou data e, em seguida, define uma fórmula que funciona em todos os dados do grupo. Por exemplo, quando você cria um total para um ano, está criando uma agregação. Se, em seguida, você criar uma taxa deste ano sobre o ano anterior e apresentá-la como percentual, a agregação será de um tipo diferente.

A decisão de como agrupar os dados é orientada pela pergunta comercial. Por exemplo, as agregações podem responder às seguintes perguntas:

Contagens   Quantas transações havia em um mês?

Médias    Qual foi a média de vendas este mês, por vendedor?

Valores mínimo e máximo    Quais foram os cinco principais distritos de vendas em termos de unidades vendidas?

Para criar um cálculo que responda essas perguntas, você deve ter dados detalhados que contenham os números a serem contados ou somados, e esses dados numéricos devem estar relacionados de alguma forma aos grupos que você usará para organizar os resultados.

Se os dados ainda não contiver valores que você pode usar para agrupamento, como uma categoria de produto ou o nome da região geográfica onde se encontra o armazenamento, talvez você queira apresentar grupos aos seus dados, adicionando categorias. Quando você cria grupos no Excel, você deve manualmente digite ou selecione os grupos que você deseja usar entre as colunas da planilha. No entanto, em um sistema relacional, hierarquias como categorias de produtos geralmente são armazenadas em uma tabela diferente do fato ou tabela de valor. Normalmente a tabela de categorias está vinculada aos dados de fatos por algum tipo de chave. Por exemplo, suponha que você achar que seus dados contém IDs de produto, mas não os nomes de produtos ou suas categorias. Para adicionar a categoria para uma planilha do Excel simples, você teria que copiar na coluna que continha os nomes de categoria. Com Power Pivot, você pode importar a tabela de categoria de produto para o seu modelo de dados, criar uma relação entre a tabela com os dados de número e a lista de categorias de produto e, em seguida, use as categorias para agrupar dados. Para obter mais informações, consulte criar uma relação entre tabelas.

Escolhendo uma função para agregação

Depois que você tiver identificado e adicionado os agrupamentos a serem usados, será necessário decidir quais funções matemáticas serão usadas na agregação. Frequentemente, a palavra agregação é usada como sinônimo para operações matemáticas ou estatísticas usadas em agregações, como somas, médias, mínimo ou contagens. No entanto, o Power Pivot permite criar fórmulas personalizadas para agregação, além das agregações padrão encontradas tanto no Power Pivot quanto no Excel.

Por exemplo, com o mesmo conjunto de valores e os agrupamentos que foram usados nos exemplos anteriores, você poderia criar agregações personalizadas que respondessem as seguintes perguntas:

Contagens filtradas   Quantas transações havia em um mês, excluindo a janela de manutenção de final de mês?

Taxas que usam médias com o passar do tempo    Qual foi o percentual de aumento ou diminuição das vendas em comparação ao mesmo período no ano passado?

Valores mínimo e máximo agrupados    Quais distritos de vendas foram classificados como primeiros em cada categoria de produto ou em cada promoção de vendas?

Adicionando agregações a fórmulas e Tabelas Dinâmicas

Quando você tem uma ideia geral de como seus dados devem ser agrupados para terem significado, e os valores com os quais você quer trabalhar, é possível criar tanto uma Tabela Dinâmica quanto cálculos dentro de uma tabela. O Power Pivot estende e melhora a capacidade nativa do Excel de criar agregações como somas, contagens ou médias. Você pode criar agregações personalizadas no Power Pivot, seja dentro da janela do Power Pivot ou da área de Tabela dinâmica do Excel.

  • Em uma coluna calculada, você pode criar agregações que levem em conta o contexto da linha atual para recuperar linhas relacionadas de outra tabela e, em seguida, efetuar a soma, a contagem ou a média desses valores nas linhas relacionadas.

  • Em uma medida, você pode criar agregações dinâmicas que usam os filtros definidos dentro da fórmula e filtros impostos pelo design da tabela dinâmica e a seleção das segmentações de dados, títulos de coluna e títulos de linha. Medidas usando agregações padrão podem ser criadas no Power Pivot usando AutoSoma ou criando uma fórmula. Você também pode criar medidas implícitas usando agregações padrão em uma tabela dinâmica no Excel.

Adicionando agrupamentos a uma Tabela Dinâmica

Ao criar uma Tabela Dinâmica, você arrasta campos que representam agrupamentos, categorias ou hierarquias para a seção de colunas e linhas da Tabela Dinâmica a fim de agrupar os dados. Em seguida, você arrasta campos que contêm valores numéricos para a área de valores, a fim de que eles sejam contados, somados e passem pelo cálculo da média.

Se adicionar categorias a uma Tabela Dinâmica, mas os dados da categoria não estiverem relacionados aos dados de fatos, você pode obter um erro ou resultados peculiares. Normalmente, o Power Pivot vai tentar corrigir o problema por detecção automática e sugestão de relações. Para saber mais, consulte Trabalhar com relações em Tabelas dinâmicas.

Você também pode arrastar campos para Segmentações de Dados a fim de selecionar determinados grupos para exibição. As Segmentações de Dados permitem a você agrupar, classificar e filtrar interativamente os resultados em uma Tabela Dinâmica.

Trabalhando com agrupamentos em uma fórmula

Você também pode usar agrupamentos e categorias para agregar dados armazenados em tabelas, criando relações entre tabelas e, em seguida, criando fórmulas que aproveitam essas relações para pesquisar valores relacionados.

Em outra palavras, se quisesse criar uma fórmula que agrupasse valores por categoria, você primeiro usaria uma relação para conectar a tabela que contivesse os dados detalhados e as tabelas que contivessem as categorias e, em seguida, compilaria a fórmula.

Para saber mais sobre como criar fórmulas que usam pesquisas, consulte Pesquisas em fórmulas do Power Pivot.

Usando filtros em agregações

Um novo recurso no Power Pivot é a capacidade de aplicar filtros a colunas e tabelas de dados, não apenas na interface do usuário e dentro de uma tabela dinâmica ou gráfico, mas também nas fórmulas muito que você usa para calcular as agregações. Filtros podem ser usados em fórmulas em colunas calculadas e em s.

Por exemplo, nas novas funções de agregação DAX, em vez de especificar valores sobre os quais somar ou contar, você poderá especificar uma tabela inteira como argumento. Se você não aplicar nenhum filtro a essa tabela, a função de agregação funcionará com base em todos os valores da coluna especificada da tabela. No entanto, no DAX, você pode criar um filtro dinâmico ou estático na tabela, a fim de que a agregação opere com base em um subconjunto de dados diferente que dependa da condição de filtro e do contexto atual.

Ao combinar condições e filtros em fórmulas, você pode criar agregações que mudam de acordo com os valores fornecidos em fórmulas ou que mudam de acordo com a seleção de títulos de linha e de títulos de coluna em uma Tabela Dinâmica.

Para saber mais, consulte Filtrar dados em fórmulas.

Comparação de funções de agregação do Excel e funções de agregação DAX

A tabela a seguir lista algumas das funções de agregação padrão fornecidas pelo Excel, e fornece links para a implementação dessas funções no Power Pivot. A versão DAX dessas funções se comporta de forma semelhante à versão do Excel, com pequenas diferenças na sintaxe e na manipulação de determinados tipos de dados.

Funções de agregação padrão

Função

Uso

MÉDIA

Retorna a média (aritmética) de todos os números de uma coluna.

MÉDIAA

Retorna a média (aritmética) de todos os valores de uma coluna. Manipula texto e valores não numéricos.

CONT.NÚM

Conta o número de valores numéricos de uma coluna.

CONT.VALORES

Conta o número de valores de uma coluna que não está vazia.

MÁXIMO

Retorna o maior valor numérico de uma coluna.

MAXX

Retorna o maior valor de um conjunto de expressões avaliadas em uma tabela.

MÍNIMO

Retorna o menor valor numérico de uma coluna.

MINX

Retorna o menor valor de um conjunto de expressões avaliadas em uma tabela.

SOMA

Adiciona todos os números de uma coluna.

Funções de agregação DAX

O DAX inclui funções de agregação que permitem especificar uma tabela na qual a agregação será executada. Portanto, em vez de apenas adicionar ou calcular a média dos valores de uma coluna, essas funções permitem criar uma expressão que define dinamicamente os dados a serem agregados.

A tabela a seguir lista as funções de agregação disponíveis no DAX.

Função

Uso

AVERAGEX

Calcula a média de um conjunto de expressões avaliadas em uma tabela.

COUNTAX

Conta um conjunto de expressões avaliadas em uma tabela.

CONTAR.VAZIO

Conta o número de valores em branco em uma coluna.

COUNTX

Conta o número total de linhas em uma tabela.

COUNTROWS

Conta o número de linhas retornadas de uma função de tabela aninhada, como a função de filtro.

SOMAX

Retorna a soma de um conjunto de expressões avaliadas em uma tabela.

Diferenças entre funções de agregação do DAX e do Excel

Embora essas funções tenham os mesmos nomes que seus equivalentes do Excel, elas usam o mecanismo analítico carregado na memória do Power Pivot e foram reescritas para trabalhar com tabelas e colunas. Não é possível usar uma fórmula DAX em uma pasta de trabalho do Excel e vice-versa. Elas só podem ser usadas na janela do Power Pivot e em Tabelas Dinâmicas baseadas em dados do Power Pivot. Além disso, embora as funções tenham nomes idênticos, o comportamento pode ser um pouco diferente. Para saber mais, veja os tópico de referência da função.

A maneira como as colunas são avaliadas em uma agregação também é diferente da maneira como o Excel manipula as agregações. Um exemplo pode ajudar a ilustrar.

Suponhamos que você queira obter uma soma dos valores da coluna Amount na tabela Sales, logo, você cria a seguinte fórmula:

=SUM('Sales'[Amount])

No caso mais simples, a função obtém os valores de uma única coluna não filtrada e o resultado é o mesmo do Excel, o qual sempre adiciona apenas os valores na coluna, Amount. Entretanto, no Power Pivot, a fórmula é interpretada como "Obter o valor em Amount para cada linha da tabela Sales e somar esses valores individuais”. O Power Pivot avalia cada linha sobre a qual a agregação é executada e calcula um único valor escalar para cada linha, desempenhando uma agregação nesses valores. Por isso, o resultado de uma fórmula pode ser diferente se os filtros tiverem sido aplicados a uma tabela, ou se os valores forem calculados com base em outras agregações que podem ter sido filtradas. Para saber mais, consulte Contexto em fórmulas DAX.

Funções de inteligência de dados temporais DAX

Além das funções de agregação da tabela descritas na seção anterior, o DAX tem funções de agregação que trabalham com datas e horas especificadas por você, para fornecer inteligência de tempo interna. Essas funções usam intervalos de datas para relacionar e agregar os valores. Você também pode comparar valores em intervalos de datas.

A tabela a seguir lista as funções de inteligência de dados temporais que podem ser usadas para agregação.

Função

Uso

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Calcula um valor ao final do calendário do período determinado.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Calcula um valor ao final do calendário do período anterior ao período fornecido.

TOTALMTD

TOTALYTD

TOTALQTD

Calcula um valor no intervalo que se inicia no primeiro dia do período e termina na última data da coluna de data especificada.

As outras funções na seção Função de inteligência de tempo (Time Intelligence Functions) são funções que podem ser usadas para recuperar datas ou intervalos personalizados de datas a serem usados na agregação. Por exemplo, é possível usar a função DATESINPERIOD para retornar um intervalo de datas e usa esse conjunto de datas como um argumento para outra função a fim de calcular uma agregação personalizada apenas para essas datas.

Observação : Aviso de Isenção de Tradução Automática: Este artigo foi traduzido por computador, sem intervenção humana. A Microsoft oferece essas traduções automáticas para ajudar as pessoas que não falam inglês a aproveitar os textos escritos sobre produtos, serviços e tecnologias da Microsoft. Como este artigo foi traduzido automaticamente, é possível que contenha erros de vocabulário, sintaxe ou gramática.

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

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×