Agregações no Power Pivot

As agregações são uma forma de fechar, 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.

As agregações mais comuns, como as que utilizam AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN ou SUM , podem ser criadas automaticamente numa medida com a Soma Automática. Outros tipos de agregações, como AVERAGEX, COUNTX, COUNTROWS ou SUMX, devolvem uma tabela e requerem uma fórmula criada com o DAX (Data Analysis Expressions).

Compreender as 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 houve num mês?

Médias Quais foram as vendas médias deste mês, por vendedor?

Valores mínimos e máximos Que distritos de vendas foram os cinco primeiros 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 contiverem valores que possam ser usados para agrupamento, como uma categoria de produto ou o nome da região geográfica onde a loja está localizada, talvez você queira introduzir grupos nos dados adicionando categorias. Ao compilar grupos no Excel, você deve digitar ou selecionar manualmente os grupos que deseja usar entre as colunas na planilha. No entanto, em um sistema relacional, hierarquias como categorias de produtos costumam ser armazenadas em uma tabela diferente da tabela de fatos ou valores. Normalmente, a tabela de categoria é vinculada aos dados de fatos por algum tipo de chave. Por exemplo, suponhamos que você descubra que os dados contêm identificações de produto (Product ID), mas não nomes de produto ou categorias. Para adicionar a categoria a uma planilha simples do Excel, você precisaria copiar a coluna que apresentasse os nomes de categoria. Com o Power Pivot, pode importar a tabela de categorias de produtos para o seu modelo de dados, criar uma relação entre a tabela com os dados numéricos e a lista de categorias de produtos e, em seguida, utilizar as categorias para agrupar dados. Para obter mais informações, veja 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-lhe criar fórmulas personalizadas para agregação, para além das agregações padrão encontradas no Power Pivot e 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 existiam num mês, excluindo a janela de manutenção do fim do mês?

Rácios que utilizam médias ao longo do tempo Qual foi o crescimento percentual ou a diminuição das vendas em comparação com o mesmo período do ano passado?

Valores mínimos e máximos agrupados Que distritos de vendas foram classificados como os principais para cada categoria de produto ou para 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 expande e melhora a capacidade nativa do Excel de criar agregações, como somas, contagens ou médias. Pode criar agregações personalizadas no Power Pivot na janela do Power Pivot ou na área 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.
• Numa medida, pode criar agregações dinâmicas que utilizam ambos os filtros definidos na fórmula e filtros impostos pela estrutura da Tabela Dinâmica e pela seleção de Segmentações de Dados, cabeçalhos de coluna e cabeçalhos de linha. As medidas que utilizam agregações padrão podem ser criadas no Power Pivot com a Soma Automática ou criando uma fórmula. Também pode criar medidas implícitas com agregações padrão numa 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 tenta corrigir o problema ao detetar e sugerir relações automaticamente. 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

Uma nova funcionalidade no Power Pivot é a capacidade de aplicar filtros a colunas e tabelas de dados, não só na interface de utilizador e numa tabela dinâmica ou gráfico, mas também nas fórmulas que utiliza para calcular agregações. Os filtros podem ser utilizados 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 seguinte lista algumas das funções de agregação padrão fornecidas pelo Excel e fornece ligações para a implementação destas 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 estas funções tenham os mesmos nomes que os seus homólogos do Excel, utilizam o motor de análise dentro da 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. Só podem ser utilizadas 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. No entanto, no Power Pivot, a fórmula é interpretada como "Obter o valor em Montante para cada linha da tabela Vendas e, em seguida, 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 e, em seguida, efetua 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.