Funções Avançadas do Power Pivot – SUMX e AVERAGEX
Você já conhece as funções avançadas do Power Pivot SUMX e AVERAGEX para somar e calcular a média sem colunas auxiliares?
SUMX e AVERAGEX são fórmulas DAX que somam e calculam médias percorrendo a tabela linha a linha com uma expressão, por exemplo quantidade vezes preço. Elas entregam o mesmo resultado de SUM e AVERAGE, só que sem exigir a coluna auxiliar de faturamento, o que deixa a base menor e mais rápida.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
O que você aprende neste vídeo
Resposta rápida: A aula mostra como somar e calcular a média do faturamento dentro do Power Pivot sem depender de uma coluna auxiliar pronta: as funções SUMX e AVERAGEX percorrem a tabela linha a linha, multiplicando quantidade vendida pelo preço do produto, e só depois somam ou tiram a média dos resultados.
Neste vídeo (15 min):
- 1:00 - Adicionando a base de vendas ao Power Pivot pela guia Power Pivot, opção Adicionar ao Modelo de Dados
- 2:00 - Criando uma medida chamada Total Faturado com a função
SUM, somando a coluna de faturamento pronta na base - 4:00 - O problema: removendo a coluna de faturamento da base, a medida com
SUMquebra porque não tem mais o que somar - 6:00 - A função
SUMXé iterativa: para cada linha da tabela, multiplica a quantidade vendida pelo preço do produto, e só depois soma todos os resultados - 8:00 - Editando a medida para usar
SUMX(Tabela1, quantidade_vendida * preço_do_produto), especificando a tabela e a expressão calculada linha a linha - 10:00 - O resultado com
SUMXbate exatamente com o total que a medida antiga comSUMe a coluna auxiliar dava - 11:00 - A vantagem das funções iterativas: evitam empilhar coluna auxiliar para cada nova conta, deixando o modelo mais leve e mais rápido
- 12:00 - Criando a medida Média de Faturamento com
AVERAGEX, usando a mesma expressão de quantidade vendida vezes preço do produto - 14:00 - Além de
SUMXeAVERAGEX, o Power Pivot tem outras funções iterativas, comoMINXeMAXX
Trechos do vídeo:
- A função
SUMXé iterativa: para cada linha da tabela, calcula uma expressão (quantidade vendida vezes preço do produto) e só depois soma todos os resultados - A função
AVERAGEXusa a mesma lógica: percorre a tabela linha a linha calculando o faturamento de cada venda, e depois tira a média desses valores, em vez da média de uma coluna pronta - O resultado de
SUMXbate com o deSUMsobre uma coluna auxiliar, porque as duas fazem a mesma conta, só queSUMXnão precisa da coluna - Funções iterativas como
SUMXeAVERAGEXevitam empilhar coluna auxiliar para cada nova conta, deixando o modelo de dados mais leve
Para receber por e-mail o(s) arquivo(s) que utilizamos na aula, preencha:
Funções Avançadas do Power Pivot Excel
Você já conhece as funções avançadas Power Pivot SUMX e AVERAGEX? É possível que você já tenha ouvido falar dessas funções em Power BI.
Mas hoje nós vamos tratar dessas duas funções dentro do Power Pivot, que também utiliza as fórmulas DAX!
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Power Pivot Funções Avançadas
O primeiro passo é trazer as informações da tabela do Excel para dentro do Power Pivot para que seja possível utilizar as fórmulas DAX.
Nós já vimos aqui no blog também como usar as funções básicas SUM e AVERAGE, só que hoje vamos ver o SUMX e AVERAGEX. Daqui a pouco você vai entender a diferença entre elas.
Vamos inicialmente criar uma medida de soma, para calcular o faturamento total das vendas que temos.
Lembrando que para criar a medida temos que levar os dados até o Power Pivot e depois voltar ao Excel e criar a medida dentro do próprio Excel com as informações do Power Pivot.
Agora imagina que você não tivesse essa coluna de faturamento ou de fato não queira ter essa coluna para não ter mais informações, mas queira mesmo assim calcular a medida do faturamento total, como você faria?
Esse X permite com que você utilize uma expressão para fazer uma operação, que nesse caso é a soma.
Então nesse caso vamos ler da seguinte maneira: “para cada item da Tabela1, vamos fazer a multiplicação da quantidade pelo preço e vamos somar esse valor”.
Aqui vamos ter exatamente o mesmo resultado da fórmula SUM, só que sem a necessidade de criar uma coluna extra para o cálculo do faturamento.
Porque é útil utilizar tanto a função SUMX, AVERAGEX quanto qualquer outra desse mesmo tipo?
Você deve ter notado que com isso não precisamos de uma coluna “auxiliar” para o cálculo do nosso faturamento não é mesmo?
Agora imagine que você tenha 1 milhão de linhas na sua tabela. Cada coluna que você cria está acrescentando mais 1 milhão de informações, então a ideia é não inserir mais informações desnecessárias para deixar sua base de dados mais pesada.
Por mais que o programa suporte isso, quanto menor a base mais rápido será o processamento dela, então se puder evitar inserir mais informações que não vai usar melhor!
A função AVERAGEX vai funcionar da mesma maneira, só que ao invés de soma nós vamos poder fazer a média de acordo com uma expressão.
Agora você já sabe como utilizar tanto a fórmula SUM como a fórmula AVERAGE de uma maneira onde não precisa de colunas extras ou auxiliares para fazer seus cálculos.
E com isso vai ter menos informações para processar dentro da sua base de dados!
Perguntas frequentes
1. Qual a diferença entre SUM e SUMX no Power Pivot?
O SUM soma os valores de uma coluna que já existe, então você precisa criar antes a coluna de faturamento. O SUMX aceita uma expressão: ele lê cada linha da tabela, multiplica quantidade por preço e soma tudo no fim. O número final é o mesmo, mas a coluna extra deixa de ser necessária.
2. Quando usar o AVERAGEX no lugar do AVERAGE?
Use o AVERAGEX quando a média depender de um cálculo que não está pronto em nenhuma coluna. Ele funciona igual ao SUMX: percorre a tabela aplicando a expressão que você escreveu e devolve a média desses resultados, sem obrigar você a materializar o cálculo em uma coluna nova.
3. Por que evitar colunas auxiliares em bases grandes?
Cada coluna criada repete um valor em toda linha da tabela: em uma base de 1 milhão de registros, é 1 milhão de informações a mais para guardar e processar. O arquivo aguenta, mas fica mais pesado e lento. Com SUMX e AVERAGEX o cálculo acontece na hora, sem inflar a base.
4. SUMX e AVERAGEX funcionam também no Power BI?
Sim. As duas são fórmulas DAX, a mesma linguagem usada pelo Power BI, então a sintaxe é idêntica nos dois programas. Quem aprende esses cálculos no Power Pivot já sai sabendo aplicá-los nas medidas do Power BI, e vice-versa: muda apenas onde o resultado é exibido.
Conclusão – Funções Avançadas do Power Pivot
Nessa aula eu te mostrei como usar as fórmulas SUMX e AVERAGEX no Power Pivot. Essas são fórmulas de soma e média, mas permitem com que você utilize uma expressão dentro delas.
Então pode utilizar uma multiplicação para fazer uma soma ou até mesmo a média, sem a necessidade de criar uma ou mais colunas para efetuar esses cálculos!
Dessa maneira você se torna mais eficiente e deixa sua base de dados menor e mais rápida para ser processada!
Hashtag Treinamentos
Para acessar outras publicações de Excel Avançado, clique aqui!
