Fala galera!! Tudo bem?
No post de hoje, vou falar sobre contexto de avaliação e a importantíssima função CALCULATE! No final, iremos realizar um case com a situação do cotidiano de analistas/consultores. Então, acompanhando o guia completo você irá saber tudo de:
- Contexto de Filtro
- Contexto de Linha
- Função CALCULATE
Como eu sempre falo, é importante lembrar as fases do nosso processo de BI:
ETL → Modelagem (Relacionamento + DAX) → Relatório → Publicação
O foco hoje para seguirmos com nosso guia será na fase de Modelagem (Relacionamento + DAX).
| Importante: Gostaria que vocês fixassem que mais importante que DAX é entender o contexto de avaliação. Isso é primordial para você saber o que vai apresentar nos seus relatórios e se questionado os valores saber explicá-los! |
Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.
Contexto de Filtro
Vou explicar o contexto de filtro de forma prática! Para isso, vamos usar um modelo simples no Power BI, com 3 tabelas “dimensão” e 2 tabelas “fato”. No relacionamento entre tabelas vemos que a tabela dimensão dProduto filtra a tabela fato fVendas pelo relacionamento entre elas (coluna ID Produto).
Figura 1: Relacionamento entre as tabelas dProduto e fVendas
A forma prática de entender o comportamento desse relacionamento é pelo visual. Iremos utilizar a medida Total Vendas e a coluna Produtos para criar uma tabela.
Figura 2: Medida Total de Vendas
Medida: Total Vendas = SUM ( fVendas[Valor Venda] )
Figura 3: Tabela Produto x Total de Vendas
A fórmula DAX da medida é igual em todas as linhas de Produto, porém ela respeita o filtro de cada linha. O que acontece é que cada uma das linhas (Produto) tem um diferente contexto. Vou adicionar a coluna ID Produto na nossa tabela para facilitar a visualização. Agora, vamos fazer um filtro na tabela fVendas em Dados utilizando o produto 39 de exemplo.
Figura 4: Filtro do ID Produto 39 em Dados
Veja que das 690 linhas originais da tabela temos 27 linhas filtradas (para o item ID 39). Conseguimos ver essa informação na parte de baixo da tela. Infelizmente, ainda não temos o recurso de visualizar a soma, contagem, etc no canto inferior direito (igual no Excel) no ambiente de Dados. Se tivéssemos, teríamos o resultado de R$ 63.694, 10 que é o valor na tabela que criamos com a medida.
Figura 5: Destaque para a soma dos 29 items do IDO Produto 39
O Total, é avaliado independente dos outros valores. No caso, ele calcula como se não houve-se nenhum filtro aplicado. O valor desse total, não é a soma de todas as linhas! O que ele faz, é limpar todos filtros para fazer o cálculo, ou seja, soma o valor da tabela de vendas inteira.
Bom, vamos fixar com mais um exemplo. Iremos utilizar o Ano, a medida de Total de Vendas e a medida de Qtd Clientes Compraram em uma nova tabela.
Figura 6: Medida Qtd Clientes Compararam
Medida: Qtd Clientes Compraram = DISTINCTCOUNT ( fVendas[ID Cliente] )
Figura 7: Tabela para análise de Qtd de Clientes
Veja que o valor de total para a quantidade de cliente não é a soma das outras linhas! A contagem distinta é uma medida não aditiva. Temos cliente que compraram em 2017 e também em 2018. Se olharmos o total não podemos contabilizar esses clientes mais de uma vez, ou seja, está correto. Concorda?
Outro exemplo, agora com uma matriz. País em linhas, Ano em colunas e o Total de Vendas em valores.
Figura 8: Tabela para análise de composição de contextos
Me diz, aí. Como o Power BI faz para calcular o valor de R$ 9.718,56 ?? Quantos contextos de filtro temos aplicados?? Se você respondeu 2 (País = “Germany” e Ano = “2019”), está correto! O contexto de filtro é uma composição do conjunto de filtros aplicado ao relatório. No total, temos 16 possibilidades de filtro gerado nessa matriz.
| Importante: O contexto de filtro final é resultante da combinação de cada um dos filtros aplicados. |
Contexto de Linha
Vamos ver na prática da mesma maneira que fizemos para contexto de filtro! No nosso exemplo, faremos um ajuste nos valores de venda. Para isso, iremos criar a coluna Valor Ajustado na tabela fVendas.
Figura 9: Medida Valor Ajustado
Medida: Valor Ajustado = fVendas[Valor Venda] * 1,5
Figura 10: Coluna de valor ajustado adicionado na tabela fVendas
A conta é feita linha a linha na nossa tabela. Para cada linha é feita uma iteração. Vamos também criar uma nova coluna Total Vendas na tabela dCliente
Figura 11: Fórmula para Coluna de Total de Vendas
Total Vendas = SUM ( fVendas[Valor Venda] )
Figura 12: Coluna de Total de Vendas Vazia
Qual o valor que você imagina que será calculado para cada linha?
Figura 13: Coluna de Total de Vendas Preenchida
Se você falou que seria um valor igual para todos, acertou! É o valor total da tabela de vendas. O que acontece é que a tabela de clientes não está fazendo nenhum filtro. Esse é o contexto de linha! Para conseguirmos aplicar esse filtro, devemos utilizar a função CALCULATE.
Função CALCULATE
É uma das principais (se não a principal) função que utilizamos no Power BI. Ela é capaz de modificar o contexto original (aplicando filtros ou utilizando funções que modificam o contexto). Além disso, pode realizar a transição de um contexto de linha para um contexto de filtro.
Bom, vamos pegar o exemplo da coluna que acabamos de criar. Agora, ao invés de calcularmos direto o valor do Total de Vendas com a função SUM, iremos utilizar também a função CALCULATE:
Figura 14: Fórmula Coluna de Total de Vendas com CALCULATE
Total Vendas = CALCULATE ( SUM ( fVendas[Valor Venda] ) )
Figura 15: Coluna de Total de Vendas com CALCULATE
Vamos fazer alguns exemplos com a função CALCULATE utilizando em forma de medida. A sintaxe da função é relativamente simples Medida = CALCULATE( [Medida], Filtro 1, Filtro 2, Filtro 3, …, Filtro N ).
Vamos criar a medida Vendas 2018 e utilizar em uma matriz com País e Total Vendas.
Figura 16: Medida Vendas 2018
Medida: Vendas 2018 = //"Shift + Enter" → para utilizar a identação padronizada CALCULATE( [Total Vendas]; dCalendario[Ano] = 2018 )
Figura 16: Matriz País, Total de Vendas e Vendas 2018
Aqui temos o contexto de filtro original (por País) e o contexto de linha criado pela CALCULATE na medida (Ano = 2018). O contexto final é a composição dos outros dois (País + Ano = 2018).
Agora outro exemplo, para isso vamos criar a medida Vendas France:
Figura 16: Medida Vendas France
Medida: Vendas France = CALCULATE( [Total Vendas]; dCliente[País] = "France" )
Se colocarmos a medida na matriz, o que vai acontecer?
Figura 17: Matriz País, total de Vendas, Vendas 2018 e Vendas France
Por que o Power BI faz isso? Porque o filtro do CALCULATE sobrescreveu o filtro de contexto. Isso acontece, porque, por trás do que vemos escrito na medida o Power BI adiciona o ALL que retira os filtros de contexto aplicado. Assim, a mesma medida que escrevemos poderia ser rescrita da seguinte forma:
Figura 18: Medida Vendas France com CALCULATE
Medida:
Vendas France =
CALCULATE(
[Total Vendas];
//dCliente[País] = "France"
FILTER(
ALL(dCliente[País])
dCliente[País] = "France"
)
)
As duas medidas, são exatamente iguais em relação a cálculos. Eu , pessoalmente, acho a primeira forma mais fácil de ler (é mais resumida).
E se eu quisesse que o valor aparecesse somente na linha de França? Para isso, podemos usar a função KEEPFILTERS:
Figura 19: Medida Vendas France com Keepfilters
Medida: Vendas France = CALCULATE( [Total Vendas]; KEEPFILTERS( dCliente[País] = "France" ) )
Dessa forma ela não sobrescreve o filtro de contexto. Olhando na linha de Alemanha, por exemplo, iremos ver que não tem nenhum valor. Isso ocorre, pois não existe nenhum total de venda para a intersecção de País = “Germany” e País = “France”.
Figura 20: Matriz com Medida Vendas France Modificada
Outra forma de obtermos o mesmo valor é utilizando a função VALUES. Mas segundo Marco Russo e Alberto Ferrari, para otimizar processamento é recomendado utilizar KEEPFILTERS.
Medida:
Vendas France =
CALCULATE(
[Total Vendas];
//KEEPFILTERS( dCliente[País] = "France")
FILTER(
VALUES(dCliente[País])
dCliente[País] = "France"
)
)
Você deve estar se perguntando quais as diferenças entre as funções VALUES, ALL e ALLSELECTED! Fiz um resumo para vocês, fica assim:
– ALL: retorna uma tabela removendo/ignorando os filtros aplicados na coluna/tabela passado como argumento
– ALLSELECTED: retorna uma tabela removendo os filtros aplicados na coluna/tabela passada como argumento, porém respeito os filtros externos
– VALUES: retorna uma tabela considerando/devolvendo os filtros aplicados na coluna/tabela passado como argumento
Para diferenciar elas de forma prática, vamos fazer um exemplo criando a Qtde de Clientes utilizando VALUES que irá retornar uma tabela de clientes. Após isso, faremos a contagem de linha dessa tabela.
Figura 21: Medida Qtd Clientes VALUES
Medida: Qtd Clientes VALUES = VAR vClientes = VALUES ( fVendas[ID Cliente] ) // Tabela com os clientes RETURN COUNTROWS ( vClientes )
Isso é exatamente igual a utilizar DISTINCTCOUNT (tanto resultado quanto performance). Agora, se utilizarmos a função ALL teremos:
Figura 22: Medida Qtd Clientes ALL
Medida: Qtd Clientes ALL = VAR vClientes = ALL ( fVendas[ID Cliente] ) // Tabela com os clientes RETURN COUNTROWS ( vClientes )
Figura 23: Medida Qtd Clientes VALUES
Então, para VALUES respeitamos o contexto de filtro e para ALL não.
Vamos fazer um novo exemplo, para explicar o funcionamneto de ALLSELECTED. Para isso iremos criar uma nova matriz com País e Total Venda, Vendas ALL País e Vendas ALLSELECTED.
Figura 24: Medida Vendas ALL País
Medida:
Vendas ALL País =
CALCULATE(
[Total Vendas];
ALL ( dClientes[País])
)
Figura 25: Medida Vendas ALLSELECTED País
Medida:
Vendas ALLSELECTED País =
CALCULATE(
[Total Vendas];
ALLSELECTED ( dClientes[País])
)
Figura 26: Resultado sem aplicação de filtro de segmentação
Figura 27: Resultado com aplicação de filtro de segmentação
A diferença entre ALL e ALLSELECTED é que o ALLSELECTED respeita filtros aplicados a outros visuais no relatório.
Vou criar agora a medida Vendas ALL Produto e colocar na nossa matriz. O que você acha que irá acontecer?
Figura 28: Medida Vendas ALL Produto
Medida:
Vendas ALL Produto =
CALCULATE(
[Total Vendas];
ALL ( dProduto)
)
Figura 29: Matriz País, Total Vendas, Vendas ALL Produto
Veja que ele respeitou o filtro de país! Isso ocorre porque não falamos para remover os contextos de País, o que formulamos foi para remomer filtros de Produto. Para funcionar corretamente esse tipo de filtro, precisamos escolher corretamente o argumento que iremos filtrar e em qual visual iremos utilizar a medida.
Mais um exemplo para fixarmos bem esse assunto, vamos colocar uma segmentação de Subcategoria e utilizar a medida Vendas ALL Produto
Figura 30: Matriz País, Total Vendas, Vendas ALL Produto e Segmentação por Subcategoria de Produto
Como Subcategoria pertence a tabela dProduto a remoção do filtro feita por ALL funciona nesse caso.
| Importante: Cuidado com os argumentos e visuais utilizados para ALL, ALLSELECTED e VALUES. Teste e veja se o que você pensou está sendo executado de forma correta. |
Em outro exemplo, vamos remover o filtro de Produto e considerar o de Subcategoria. Para isso, vamos utilizar o conjunto de ALL com VALUES.
Figura 31: Medida Vendas ALL Produto Alterada
Medida:
Vendas ALL Produto =
CALCULATE(
[Total Vendas];
ALL(dProduto);
VALUES(dProduto[Subcategoria]
)
Figura 32: Matriz com Subcategoria, Total Vendas e Vendas ALL Produto
E se eu retirar a função ALL, o que acontece?
Figura 33: Nova medida sem o ALL
Medida:
Vendas ALL Produto =
CALCULATE(
[Total Vendas];
ALL(dProduto);
)
Figura 34: Matriz com a Medida Modificada
Ele vai continuar considerando de Subcategoria. O que é exatamente igual o original.
O filtro seria: (Subcategoria Bluetooth + Produto NT Active Headphone E202 Red) Visual + (Subcategoria Bluetooth) Medida com VALUES
Outro exemplo, vamos calcular o total de vendas para os clientes que compraram mais de R$ 10.000,00. Como fazer? Para isso, devemos definir a granularidade do filtro (função FILTER) em conjunto com a função CALCULATE.
Figura 35: Medida Total Vendas Clientes > 10.0000
Medida:
Total Vendas Clientes > 10.000 =
CALCULATE(
[Total Vendas];
FILTER(
dCliente;
SUM ( fVendas [Valor Venda]) > 10000
)
Figura 35: Matriz Ano, Total Vendas, Total Vendas Clientes > 10.000
Será que está correto? Os valores estão iguais, então se fizemos de forma correta, espera-se que nenhum cliente tenha comprado menos de R$ 10.000,00. Vamos analisar as tabelas de cliente para conferir:
Figura 36: Verificação dos Valores
Não deu certo! O que aconteceu? Na forma que escrevemos o filtro foi para SUM (fVendas[Valor Venda]). Esse valor é o total do Ano! Para corrigirmos temos que usar um novo CALCULATE dentro da fórmulas, que fica assim:
Figura 37: Medida Total Vendas Clientes > 10.000 Corrigida
Medida:
Total Vendas Clientes > 10.000 =
CALCULATE(
[Total Vendas];
FILTER(
dCliente;
CALCULATE(SUM ( fVendas [Valor Venda])) > 10000
)
Figura 38: Tabela com Valores Corrigidos
Isso é o mesmo que:
Figura 39: Medidas Total Vendas Clientes > 10.000 com [Total Vendas]
Medida:
Total Vendas Clientes > 10.000 =
CALCULATE(
[Total Vendas];
FILTER(
dCliente;
[Total de Vendas] > 10000
)
Veja que o segundo termo de [Total Vendas] não está colorido como no primeiro. Isso aconteceu, pois o Power BI está entendendo como a coluna que criamos lá no inicio com o nome Total Vendas. Para darmos sequência, vamos renomear a coluna criada para Vendas Cliente e criar a medida novamente.
Figura 40: Coluna Total Vendas Renomeada para Vendas Cliente
Corrigindo a coluna [Vendas Cliente] para a medida [Total Vendas]:
Figura 41: Medida Corrigida
Isso acontece, pois uma medida sempre possui um CALCULATE por fora dela. Assim, ela sempre realiza a transição do contexto de linha para filtro.
Figura 42: Matriz Ano, Total Vendas e Total Vendas Clientes > 10000 utilizando a Medida [Total Vendas] para filtrar
Porque os valores das somas da linha não batem com o total? Imagine que um cliente comprou R$ 9.000,00 em 2017, o mesmo valor em 2018 e 2019. Nesse caso, ele não entra no somatório em cada um dos anos se avaliarmos separadamente, mas no total que não temos filtro de ano ele comprou 3 x R$ 9.000,00 = R$ 27.000,00 daí então entraria para esses contexto.
E se eu quiser que o total seja a soma das linhas de ano? Tem como fazer? Sim, para isso usamos a função SUMX. Essa fórmula é uma iteradora, e para obtermos o resultado temos que iterar pelo ano.
Figura 43: Medida Total Vendas Clientes >10.000 Ajustado
Medida:
Total Vendas Clientes > 10000 Ajustado =
SUMX(
VALUES(dCalendario[Ano]);
[Total Vendas > 10.000]
)
Figura 44: Matriz com a Medida Ajusta para Somar as Linhas
Case
O case que vou fazer com você é um problema do cotidiano de analista/consultor. Imagine que você precisa calcular o tempo médio entre a venda e devolução do produto, mas não possui as datas na mesma tabela. Além disso, precisamos calcular a soma de valor devoluções de produtos por pré vendedor. Porém, não temos a informação de pré vendedor na tabela de devoluções.
O que fazer ?
O primeiro passo é: Não desesperar! Vamos olhar os relacionamento e como estão nossas tabelas em Modelo:
Figura 45: Modelo com os Relacionamentos das Tabelas do Case
Vamos trabalhar primeiro em encontrar quem é o pré vendedor para cada venda. Para isso temos algumas soluções:
– Conseguir na consulta do Sistema de devoluções quem o pré vendedor referente aquela venda. Isso está descartado, pois queremos resolver com as informações que temos em mão;
– No Power Query, mesclar vendas e devoluções pelo número do pedido;
– Em dados, criar uma nova coluna e utilizar LOOKUPVALUE para trazer a informação via DAX;
– Por medidas (relacionamento virtual);
– Tentar fazer o relacionamento entre as tabelas no modelo (fica ambíguo/sentido duplo/tracejado). Esse é o pior cenário, e você deve evitar ao máximo isso!
| Dica: Em testes, o LOOKUPVALUE via DAX se mostrou mais performático do que mesclar as consultas no Power Query! Além disso, um relacionamento “físico” (LOOKUPVALUE e Mesclar) é sempre melhor que um relacionamento feito por medidas (virtual). Eu pessoalmente, só utilizei uma vez o relacionamento virtual nesses tipos de problemas. Nas outra vezes, consegui fazer via relacionamento “físico”. |
Aqui, iremos utilizar a opção de criar uma nova coluna cdPreVendedor na tabela de Devolucoes utilizando LOOKUPVALUE :
Figura 46: Criando Nova Coluna para Adicionar a Informação de Pré Vendedor
Figura 47: Fórmula da Nova Coluna
Fórmula: cdPreVendedor = LOOKUPVALUE( Vendas [cdPrevendedor]; Vendas [numPedido]; devolucoes [numPedidoOriginal])
Figura 48: Resultado da Tabela
Com o relacionamento criado conseguiríamos fazer o cálculo por Pré Vendedor com a medida:
Figura 49: Medida Valor Devolvido
Medida: Valor Devolvido = SUM( Devolucoes [Valor] )
Já por medidas, temos as seguintes maneiras:
Figura 50: Medida Valor Devolvido V1
Medida:
Valor Devolvido v1 =
CALCULATE(
SUM( Devolucoes [Valor] );
TREATAS(
VALUES( Vendas[numPedido]);
Devolucoes [ numPedidoOriginal]
)
)
Figura 51: Medida Valor Devolvido V2
Medida:
Valor Devolvido v2 =
CALCULATE(
SUM( Devolucoes [Valor] );
INTERSECT(
VALUES( Devolucao[numPedidoOriginal]);
VALUES( Vendas [numPedido])
)
)
Figura 52: Medida Valor Devolvido V3
Medida:
Valor Devolvido v3 =
CALCULATE(
SUM( Devolucoes [Valor] );
Devolucoes [ numPedidoOriginal] IN VALUES (Vendas[numPedido])
)
Nesse caso, não conseguimos usar a função RELATED ou USERELATIONSHIP. Isso ocorre, pois não criamos um relacionamento entre as tabelas de Devolucoes e Vendas no modelo.
O resultado fica:
Figura 53: Resultado de devoluções por pré vendedor
Como calcular a média da diferença entre datas em tabelas diferentes?
Podemos criar um relacionamento virtual. Conseguimos o resultado com isso, porém estamos resolvendo além do cálculo um problema de modelagem.
Figura 54: Medida Tempo Médio v1
Medida:
Tempo Médio v1 =
AVERAGEX(
Devolucoes;
VAR vDataVenda = LOOKUPVALUE ( Vendas [ DataVenda]; Vendas [numPedido]; Devolucoes [ numPedidoOriginal])
RETURN
DATEDIFF( vDataVenda; Devolucoes[DataDevolucao]; DAY)
)
E a outra forma, seria semelhante a coluna que criamos para pré vendedor, mas agora será para a DataVenda utilizando LOOKUPVALUE:
Figura 55: Adicionando a coluna de DATAVENDA na tabela Devolucao
Com o relacionamento feito, devemos alterar nossa medida para:
Figura 56: Medida Tempo Médio v1 Alterada
Medida: Tempo Médio v1 = AVERAGEX( Devolucoes; DATEDIFF( vDataVenda; Devolucoes[DataDevolucao]; DAY) )
O relacionamento que criamos via LOOKUPVALUE é mais performático que a criação do relacionamento virtual, pois é somente uma busca de informação que naturalmente deveria estar ali. Assim, só consome memória quando atualizamos o modelo. No relacionamento virtual, forçamos o processamento dessa relação toda vez que for utilizado.
Figura 57: Modelo com os Relacionamentos das Tabelas do Case após Criação das Colunas
Figura 58: Resultados do Case
Pessoal, acredito que com o guia de hoje mais o case vocês assimilaram muito conteúdo! Estão feras em contexto de filtro, contexto de linha, função CALCULATE e de quebra ainda aprenderam a fazer relacionamentos entre tabelas para solucionar problemas no dia a dia!!
Espero que tenham gostado. Não deixe de entrar em contato, caso tenha alguma dúvida ou sugestão de tema.
Grande abraço,
Leonardo
