Quando usar SUM e SUMX no Power BI (Linguagem DAX)

A função de soma (SUM) com certeza é uma das mais utilizadas no Power BI, se não for a mais utilizada. Não só no Power BI mas no Excel também. Neste artigo trataremos do comportamento das funções SUM e SUMX no Power BI. Tudo que falarmos aqui se aplica também ao Power Pivot do excel.

Entendemos que não há necessidade de falar muito sobre a função de somar em si. É uma função simples, provavelmente a primeira que aprendemos devido à grande aplicação que ela tem. Mas se tiver alguma dúvida leia o nosso artigo: 4 Funções DAX simples mas que você usará em todos seus modelos.

Antes de entrarmos nas explicações das funções SUM e SUMX no Power BI é importante entender alguns detalhes do comportamento das funções dentro da Linguagem DAX.

Para melhor acompanhar este artigo baixe nosso exemplo em Power BI.

15 funções DAX mais usadas

Comportamento dos filtros

Quando se cria uma fórmula em DAX, o resultado dela depende dos filtros que estão aplicados no relatório. Tenha em mente que DAX não é o mesmo que as fórmulas em Excel.

No Excel é possível criar várias fórmulas em cada célula de um relatório e cada fórmula pode apontar para células criando resultados diferentes. Cada célula é única. Mas não é assim que funciona na linguagem DAX.

No DAX se cria uma única fórmula, exemplo SUM(Vendas[Total Venda]), utilizando os filtros do relatório se modifica os resultados retornados desta fórmula.

Os filtros são aqueles criados em qualquer local da área de relatório no Power BI, tanto as segmentações de dados quanto qualquer elemento clicável nos gráficos e tabelas do relatório. Neste sentido é considerado como filtro também, influenciando no resultado, aqueles adicionados na função CALCULATE. Esta é a única função que pode alterar o comportamento dos filtros iniciais em um relatório.

O termo técnico para este comportamento dos filtros em relação ao resultado é “Contexto de Filtro” ou “Filter Context”. Provavelmente é um termo que você já ouviu nos seus estudos sobre Power BI. Se não ouviu ainda irá ouvir muito aqui no nosso blog e nossos cursos.

Veja como é o comportamento do filtro inicial no Power BI.

Power BI Filtros Iniciais

Note que a linha de “Bikes” (1), é um filtro inicial, somando todos os valores de vendas de bicicletas alí. Mas há também um outro filtro no gráfico, a coluna da cidade (2) filtra também todas as vendas desta cidade. Assim o valor na linha “Bikes” é a soma desta categoria para a cidade de “São Paulo” no exemplo. Os dois filtros aplicados são filtros iniciais.

Filtros podem vir de colunas, segmentação de dados e também da seção de Filtros na direita da visualização de Relatório abaixo da seção Visualizações.

 

Contexto de Linha

O segundo ponto importante a se compreender é a análise linha a linha. Não é toda fórmula DAX que é capaz de calcular linha a linha. Em algumas áreas do DAX, por exemplo numa coluna calculada, consegue avaliar linha a linha o resultado, mas em outras áreas não.

Por exemplo, pode-se escrever uma fórmula simples numa coluna calculada, tal como, Vendas[PedidoQtde] * Vendas[Preço Unit.] que a fórmula irá avaliar uma linha por vez. Mas se fizer esta mesma fórmula como uma Medida acontecerá um erro.

O termo técnico para este comportamento é “Contexto de Linha” ou “Row Context”. Representa então a análise linha a linha para a fórmula.

 

SUM e SUMX no Power BI

Após apresentado alguns conceitos bases, vamos agora falar da razão deste artigo ser escrito. As fórmulas SUM e SUMX no Power BI.

SUM()

É uma função agregadora, ou seja, adiciona todos os valores em uma coluna. O resultado se dá após aplicados ou não todos os filtros que influenciam na fórmula. SUM() não considera a existência das linha (não avalia linha a linha). Tudo que ela faz é agregar todas as linhas dentro de uma única coluna.

SUMX()

É um “iterador”*, ou seja, trabalha na tabela, linha a linha para completar a avaliação. O resultado se dá após aplicados ou não todos os filtros que influenciam na fórmula. SUMX() leva em conta cada linha da tabela e, portanto, pode referenciar a interseção de cada linha com qualquer coluna na tabela. É uma fórmula que opera numa única coluna, mas consegue também operar em múltiplas colunas, pois trabalha linha a linha na tabela.

Em resumo, SUM() trabalha com uma única coluna e não considera as linhas individualmente. SUMX() opera em múltiplas colunas na tabela e pode avalia linha por linha destas colunas.

As duas fórmulas podem dar o mesmo resultado (pode dar ou podem não dar), mas este resultado vem por caminhos totalmente diferentes. Geralmente elas chegam nos mesmos resultados por linha de uma tabela ou gráfico, mas geralmente os resultados são diferentes nos subtotais ou totais das seções de um visual.

* Iterador, iteração, falaremos destes dois termos bastante neste artigo. Significa tratar item a item, ou seja, agrega um item de cada fez no resultado da função.

 

Função SUM()

 

Estrutura da fórmula: SUM (<coluna>)

Exemplo:

A função SUM() opera em uma coluna única e age para agregar todos os dados desta coluna, levando em conta os filtros aplicados. Primeiro considera o filtro, depois o resultado.

 

Função SUMX()

 

Estrutura da fórmula: SUMX (<tabela>; <expressão>)

Exemplo:

O SUMX() itera (agrega item a item), em uma tabela especificada no primeiro parâmetro, uma linha por vez. No segundo parâmetro a função completa o cálculo. No caso Quantidade x Preço Unitário, levando em conta os filtros aplicados. Assim como na SUM, primeiro considera o filtro, depois o resultado.

A função adiciona até chegar ao total, linha por linha. É como se fosse uma soma ponderada pelo valor próprio de cada linha. No exemplo, a linha que tiver maior quantidade terá maior peso no resultado a linha com menos quantidade.

 

Qual função devo usar, SUM ou SUMX?

Na hora de decidir qual das duas funções utilizar, SUM ou SUMX, dependerá da sua preferência e da estrutura dos seus dados. Vejam abaixo alguns exemplos:

Exemplo 1: Quantidade e Preço Unitário

Tabela SUMX

Se você tem uma tabela de vendas que contenha uma coluna de “Quantidade” e outra de “Preço Unitário” (como mostramos acima), então será necessário multiplicar uma coluna pela outra para chegar no resultado do Total de Vendas. O Resultado não seria bom se fizéssemos a multiplicação da soma da “Quantidade” com a média do “Preço Unitário”. Fazendo isto traria um resultado indesejado.

No caso da imagem acima, o melhor é utilizar SUMX(). É justamente para estes casos que este tipo de função foi criado (iteração). Abaixo a fórmula que deve ser usada, no arquivo que disponibilizamos tem esta fórmula.

Funções de iteração sempre tem seu primeiro parâmetro uma tabela. Esta é a tabela que será iterada pela função.

Muitos usuários cometem um erro comum neste caso. Ao invés de utilizar a função acima acabam criando uma coluna calculada para resolver o problema. De fato, o problema do resultado será resolvido, mas acaba criando outro com isso. Ter uma coluna calculada grava o cálculo linha a linha no arquivo. Mas isto deve ser evitado sempre que possível, pois aumenta a quantidade de memória utilizada e o tamanho do arquivo desnecessariamente.

 

Exemplo 2: Valor Total

Se seus dados contém uma coluna com o Valor Total de Vendas por linha, então pode usar a função SUM() para a soma dos valores.

Tabela SUM Power BI

Neste caso não há necessidade de iteração. É apenas um cálculo simples que agrega todos os valores num total, sem necessidade de execução linha a linha. Se quiser poderá usar o SUMX(), conforme abaixo. O resultado será o mesmo.

Considerando em termos de performance, esta fórmula é idêntica a SUM() em eficiência e uso de memória. Mais sobre esse ponto no final do artigo.

 

Exemplo 3: Quando o Total não pode ser somado

Há outro caso em que é necessário usar SUMX(), mas é menos óbvio. Quando se encontra um problema em que o Total não soma conforme a necessidade ou esperado. Assim é necessário usar um “iterador”, exemplo SUMX, para corrigir este problema. Observe a tabela abaixo:

Tabela SUMX Power BI

Na tabela acima temos 6 clientes com uma média gasta em cada visita. Tem também o número de visitas que tiveram na loja. Se tentarmos carregar estes dados no Power BI utilizando funções de agregação para determinar uma média de gastos e um total gastos, na linha de total teremos o valor errado. Veja abaixo:

Tabela Power BI SUM não usar

As medidas utilizadas na tabela acima foram:

Media por Visita Errado = AVERAGE( ClientesUso[Gasto por Visita] )

Total de Visitas = SUM( ClientesUso[Nro Visitas] )

Total Gasto Errado = [Total de Visitas] * Medidas[Media por Visita]

Na “Média por Visita Errado” o resultado da linha total está errado, é uma média simples sem considerar o total de visitas. O “Total de Visitas” está correto, pois é apenas a soma da coluna. “Total Gasto Errado” está errado também pois não considera linha a linha.

Para este tipo de estrutura de dados a única forma de calcular corretamente os totais é considerando linha a linha. Veja abaixo o resultado correto:

Tabela Power BI SUM correto

Nesta tabela acima utilizamos a função SUMX() para criar o “Total Gasto Correto” primeiro. Somente depois desta medida criada que calculamos a “Média por Visita Correto” na fórmula final.

Total de Visitas = SUM( ClientesUso[Nro Visitas] )

Media por Visita Correto = DIVIDE( [Total Gasto Correto]; [Total de Visitas]; 0 )

Total Gasto Correto = SUMX( ClientesUso; ClientesUso[Nro Visitas] * ClientesUso[Gasto por Visita] )

Perceba que para ter na média ponderada simplesmente dividimos o “Total Gasto Correto” pelo “Total de Visitas”.

 

Impactos na Performance

Fórmulas em geral tem implicação direta na performance do modelo de dados, ou seja, tem fórmulas que usam mais memória e espaço do que outras. Falaremos então qual das duas funções (SUM e SUMX) tem a melhor eficiência na performance do modelo.

Podemos até pensar que o SUMX, por ser um “iterador” seria menos eficiente que o SUM. Mas isto não é necessariamente verdade, o Power BI é otimizado para trabalhar com cenários. O que pode causar ineficiência neste caso é um DAX mal escrito.

A linguagem DAX tem duas engines de cálculo, a Storage Engine (SE) e a Formula Engine (FE). A mais rápida é a SE, pois é multidirecional e armazena em cache. A FE é mais lenta porque é unidirecional e não armazena em cache.

Temos que buscar fórmulas que sejam SE sempre que possível. Não é algo que seja fácil de saber se não temos uma vivência diária com estas fórmulas. Mas felizmente temos um bom material disponível na web, principalmente porque os experts estão buscando cada vez mais eficiência na utilização das fórmulas no Power BI.

Vamos explicar a seguir melhor em relação ao nosso exemplo.

  • SUM() sempre utiliza SE para seus cálculos.
  • Para a maioria dos cálculos simples (por exemplo, Vendas[PedidoQtde] * Vendas[Preço Unit.]), SUMX() utilizará SE.
  • Em algumas circunstâncias, SUMX() pode utilizar FE para alguns ou todos os cálculos, principalmente se tiver comparações complexas na fórmula. Neste caso a performance cai muito.

Não comprometer a performance

Aconselhamos, assim, evitar utilizar a função IF juntamente com SUMX(). Abaixo damos um exemplo de como substituir a função IF trazendo o mesmo resultado e com isso não comprometendo a performance.

Ao invés de:

Utilize:

A primeira fórmula (Total Vendas < 100 NÃO) tem a função IF juntamente com a SUMX. Esta função IF força o cálculo SE passar para uma a forma FE para que seja comparado as linhas individualmente se for maior ou menor que 100, antes de decidir incluir ou não no cálculo. Pelo fato da função ter que avaliar linha a linha o cálculo fica lento e ineficiente.

A segunda fórmula (Total Vendas < 100 SIM) primeiramente modifica o filtro inicial, vindo do visual, utilizando CALCULATE() para colocar um filtro adicional (Vendas[Total Venda] < 100). Este filtro é aplicado por “Storage Engine” (SE) de forma bem eficiente. Após o CALCULATE modificar o filtro a função SUMX pode então fazer seu trabalho e somar as linhas restantes, uma a uma, através do SE. Nos nossos testes realizados com as duas fórmulas verificamos que a primeira fórmula (NÃO) levou até 5 vezes mais para processar.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto. Ajude-nos também a espalhar bons conteúdos sobre o Power BI.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

Compartilhe