Agregações no Power Pivot

As agregações são uma forma de recolher, resumir ou agrupar dados. Quando começa com dados não processados de tabelas ou outras origens de dados, os dados são geralmente planos, o que significa que existem muitos detalhes, mas não foram organizados ou agrupados de qualquer forma. A 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, abstraam ou ressumam padrões em resposta a uma pergunta comercial específica.

As agregações mais comuns, como as que utilizam MÉDIA,CONTAR,CONTARDISTa, MAX, MÍN ou SOMA , podem ser criadas numa medida automaticamente através da Soma Automática. Outros tipos de agregações, tais como AVERAGEX, COUNTX, COUNTROWS ou SUMX devolvem uma tabela e requerem uma fórmula criada através de DAX (Data Analysis Expressions).

Compreender as Agregações no Power Pivot

Selecionar Grupos para Agregação

Ao agregar dados, agrupa os dados por atributos, como produto, preço, região ou data e, em seguida, define uma fórmula que funciona com todos os dados do grupo. Por exemplo, quando cria um total para um ano, está a criar uma agregação. Se depois criarmos um rácio deste ano em relação ao ano anterior e os apresentarmos como percentagens, é um tipo diferente de agregação.

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

Contagens Quantas transações existiram num mês?

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

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

Para criar um cálculo que responda a estas perguntas, tem de ter dados detalhados que contenham os números a contar ou somar e esses dados numéricos têm de estar relacionados de alguma forma com os grupos que irá utilizar para organizar os resultados.

Se os dados ainda não contiverem valores que possa utilizar para agrupar, tal como uma categoria de produto ou o nome da região geográfica onde se situa o arquivo, poderá querer introduzir grupos nos seus dados adicionando categorias. Quando cria grupos no Excel, tem de escrever ou selecionar manualmente os grupos que pretende utilizar a partir das colunas da sua folha de cálculo. No entanto, num sistema relacional, as hierarquias, como as categorias para produtos, são muitas vezes armazenadas numa tabela diferente da tabela de factos ou de valores. Normalmente, a tabela de categorias está ligada aos dados de factos através de algum tipo de chave. Por exemplo, suponhamos que descobre que os seus dados contêm IDs de produtos, mas não os nomes dos produtos ou as respetivas categorias. Para adicionar a categoria a uma folha de cálculo simples do Excel, teria de copiar a coluna que continha os nomes das categorias. 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 de números e a lista de categorias de produtos e, em seguida, utilizar as categorias para agrupar dados. Para obter mais informações, consulte Criar uma relação entre tabelas.

Escolher uma função para agregação

Depois de identificar e adicionar os agrupamentos a utilizar, tem de decidir que funções matemáticas utilizar para a agregação. Muitas vezes, a palavra agregação é utilizada como sinónimo das operações matemáticas ou estatísticas utilizadas nas agregações, tais como somas, médias, mínimos ou contas. No entanto, o Power Pivot permite-lhe criar fórmulas personalizadas para agregação, além das agregações padrão encontradas no Power Pivot e no Excel.

Por exemplo, tendo em conta o mesmo conjunto de valores e agrupamentos utilizados nos exemplos anteriores, pode criar agregações personalizadas que respondam às seguintes perguntas:

Contagens filtradas Quantas transações existiram num mês, excluindo a janela de manutenção de fim de mês?

Rácios que utilizam médias ao longo do tempo Qual foi o percentual de crescimento ou queda nas 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 no topo para cada categoria de produto ou para cada promoção de vendas?

Adicionar agregações a fórmulas e tabelas dinâmicas

Quando tem uma ideia geral de como os seus dados devem ser agrupados para serem significativos e dos valores com que pretende trabalhar, pode decidir se quer construir uma Tabela Dinâmica ou criar cálculos numa 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.

• Numa coluna calculada, pode criar agregações que têm em conta o contexto da linha atual para obter linhas relacionadas de outra tabela e, em seguida, somar, contar ou calcular a média desses valores nas linhas relacionadas.
• Em certa medida, pode criar agregações dinâmicas que utilizem 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 através da Soma Automática ou através da criação de uma fórmula. Também pode criar medidas implícitas através de agregações padrão numa tabela dinâmica no Excel.

Adicionar agrupamentos a uma Tabela Dinâmica

Quando cria uma Tabela Dinâmica, arrasta campos que representam agrupamentos, categorias ou hierarquias para a secção de colunas e linhas da Tabela Dinâmica para agrupar os dados. Em seguida, arraste os campos que contêm valores numéricos para a área de valores para que possam ser contados, calculados médios ou somados.

Se adicionar categorias a uma Tabela Dinâmica, mas os dados das categorias não estiverem relacionados com os dados de factos, poderá obter um erro ou resultados peculiares. Normalmente, o Power Pivot tentará corrigir o problema ao detetar e sugerir relações automaticamente. Para obter mais informações, consulte Trabalhar com relações em tabelas dinâmicas.

Também pode arrastar campos para as Segmentações de Dados para selecionar determinados grupos de dados para visualização. As segmentações de dados permitem-lhe agrupar, ordenar e filtrar os resultados de forma interativa numa tabela dinâmica.

Trabalhar com Agrupamentos numa Fórmula

Também pode utilizar agrupamentos e categorias para agregar dados armazenados em tabelas ao criar relações entre tabelas e, em seguida, criar fórmulas que tiram partido dessas relações para pesquisar valores relacionados.

Noutras palavras, se pretendesse criar uma fórmula que agrupasse valores por uma categoria, utilizaria primeiro uma relação para ligar a tabela que contém os dados de detalhe e as tabelas que contêm as categorias e, em seguida, criaria a fórmula.

Para obter mais informações sobre como criar fórmulas que utilizem pesquisas, consulte Pesquisas em Fórmulas do Power Pivot.

Utilizar Filtros em Agregações

Uma nova funcionalidade do 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 próprias fórmulas que utiliza para calcular agregações. Os filtros podem ser utilizados em fórmulas tanto em colunas calculadas como em s.

Por exemplo, nas novas funções de agregação DAX, em vez de especificar valores para somar ou contar, pode especificar uma tabela completa como argumento. Se não aplicasse quaisquer filtros a essa tabela, a função de agregação funcionaria em relação a todos os valores na coluna especificada da tabela. No entanto, no DAX pode criar um filtro dinâmico ou estático na tabela, para que a agregação funcione em relação a um subconjunto de dados diferente, dependendo da condição do filtro e do contexto atual.

Ao combinar condições e filtros em fórmulas, pode criar agregações que mudam consoante os valores fornecidos nas fórmulas ou que mudam consoante a seleção de cabeçalhos de linhas e cabeçalhos de coluna numa Tabela Dinâmica.

Para obter mais informações, consulte Filtrar Dados em Fórmulas.

Comparação das Funções de Agregação do Excel e das 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 destas funções comporta-se da mesma forma que a versão do Excel, com algumas pequenas diferenças na sintaxe e no processamento de determinados tipos de dados.

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

Função	Utilize
MÉDIA	Devolve a média aritmética de todos os números de uma coluna.
MÉDIAA	Devolve a média aritmética de todos os valores de uma coluna. Lida com texto e valores não numéricos.
COUNT	Conta o número de valores numéricos numa coluna.
CONTAR.VAL	Conta o número de valores numa coluna que não estão vazios.
MAX	Devolve o maior valor numérico de uma coluna.
MÁXX	Devolve o maior valor de um conjunto de expressões avaliadas numa tabela.
MIN	Devolve o menor valor numérico numa coluna.
MINX	Devolve o menor valor de um conjunto de expressões avaliadas numa tabela.
SOMA	Soma todos os números numa coluna.

Funções de Agregação DAX

O DAX inclui funções de agregação que lhe permitem especificar uma tabela sobre a qual a agregação será efetuada. Assim, em vez de apenas adicionar ou calcular a média dos valores numa coluna, estas funções permitem-lhe criar uma expressão que define dinamicamente os dados a agregar.

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

Função	Utilize
MÉDIAX	Calcula a média de um conjunto de expressões avaliadas numa tabela.
COUNTAX	Conta um conjunto de expressões avaliadas numa tabela.
CONTAR.VAZIO	Conta o número de valores em branco numa coluna.
CONTAGEMX	Conta o número total de linhas numa tabela.
COUNTROWS	Conta o número de linhas devolvidas a partir de uma função de tabela aninhada, como a função filtrar.
SUMX	Devolve a soma de um conjunto de expressões avaliadas numa tabela.

Diferenças entre as Funções de Agregação DAX e Excel

Embora estas funções tenham os mesmos nomes que as suas homólogas no Excel, utilizam o motor de análise na memória do Power Pivot e foram reescritas para funcionarem com tabelas e colunas. Não é possível utilizar uma fórmula DAX num livro do Excel e vice-versa. Só podem ser utilizados 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 ligeiramente diferente. Para obter mais informações, consulte os tópicos de referência de funções individuais.

A forma como as colunas são avaliadas numa agregação também é diferente da forma como o Excel lida com as agregações. Um exemplo pode ajudar a ilustrar.

Suponha que pretende obter uma soma dos valores na coluna Valor na tabela Vendas, criando assim 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 que no Excel, que soma sempre apenas os valores na coluna Amount. No entanto, no Power Pivot, a fórmula é interpretada como "Obtenha o valor em Montante para cada linha da tabela Vendas e, em seguida, some esses valores individuais. O Power Pivot avalia cada linha em que a agregação é efetuada, calcula um valor escalar único para cada linha e, em seguida, efetua uma agregação nesses valores. Por conseguinte, o resultado de uma fórmula pode ser diferente se tiverem sido aplicados filtros a uma tabela ou se os valores forem calculados com base noutras agregações filtradas. Para obter mais informações, consulte o artigo Contexto em Fórmulas DAX.

Funções de Análise de Tempo do DAX

Além das funções de agregação de tabelas descritas na secção anterior, o DAX tem funções de agregação que funcionam com datas e horas que especifica, para fornecer análise de tempo incorporada. Estas funções utilizam intervalos de datas para obter valores relacionados e agregar os valores. Também pode comparar valores em intervalos de datas.

A tabela seguinte apresenta as funções de análise de tempo que podem ser utilizadas para agregação.

Função	Utilize
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Calcula um valor no final do calendário de um determinado período.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER ABERTURABALANCEANO	Calcula um valor no final do calendário do período anterior ao período determinado.
TOTALMTD TOTALYTD TOTALQTD	Calcula um valor ao longo do intervalo que começa no primeiro dia do período e termina na data mais recente na coluna de datas especificada.

As outras funções na secção de funções de Análise de Tempo (Funções de Análise de Tempo) são funções que podem ser utilizadas para obter datas ou intervalos de datas personalizados para utilizar na agregação. Por exemplo, pode utilizar a função DATASENPERÍODO para devolver um intervalo de datas e utilizar esse conjunto de datas como argumento para outra função para calcular uma agregação personalizada apenas para essas datas.