Hey! É o Edwin aqui.
Hoje vim compartilhar com vocês um problema que tive recentemente ao realizar operações utilizando o visual de matriz no PowerBI, o qual, em um contexto, a conta fazia sentido, mas em uma hierarquia diferente já não fazia. Iremos aprender a manipular a função ISINSCOPE() do DAX.
Quer ficar fera nessa função? Vem comigo! 🚀
O problema
Um cliente meu gostaria de visualizar no PowerBI o cálculo de comissionamento de vendas feitas por seus parceiros comerciais. Uma porcentagem da venda é repassada ao vendedor de acordo com algumas regras, tais como a taxa negativa negociada com o cliente.
Vou dar o exemplo de uma venda:
Valor da Venda: R$10.000
Desconto dado na venda: 0,3%
Taxa ao vendedor por venda: 1%
Taxa efetiva (Taxa do vendedor – Desconto dado na venda): 0,7% ou R$70
No exemplo acima vemos que o cálculo é relativamente simples e poderia ser facilmente transposto em uma tabela:
Todos os valores acima foram preenchidos manualmente e os cálculos deveriam obedecer a fórmula acima. Então vamos colocar no PowerBI no visual Matriz obedecendo a seguinte hierarquia:
Data (mês/ano) -> Parceiro -> Produto -> Cliente
As medidas são as seguintes:
Soma valor venda = SUM(Tabela1[Valor venda])
Soma desconto dado = SUM(Tabela1[Desconto dado na venda])
Soma taxa ao vendedor = SUM(Tabela1[Taxa ao vendedor por venda])
Result Taxa efetiva = [Soma taxa ao vendedor] – [Soma desconto dado]
Valor de repasse = [Soma valor venda] * [Result Taxa efetiva]
Vamos analisar a venda à KM Transportes feita pelo Alan em março de 2021.
Podemos ver que os cálculos a nível de Cliente estão corretos. Mas repare o valor total do produto Light:
Como assim 280 + 100 é igual a 810 ? 😨
Bem, o entendimento de cálculo em matrizes no PowerBI é um pouco diferente. Apesar de nosso senso comum entender que os valores percentuais não devem ser somados ao subir a hierarquia, a soma é feita assim mesmo, o que pode confundir a pessoa que irá utilizar o relatório. Então como tratar isso? Agora iremos ver nossa função salvadora!
Chamando ISINSCOPE(), Chamando ISINSCOPE()… 📣
Conforme já bem explicado pelo excelente vídeo do Laennder Alves sobre esta função, ISINSCOPE() muda o contexto de cálculo conforme os parâmetros que estão nele.
Vamos utilizar um exemplo abaixo:
Veja que passando o parâmetro da Tabela1[Cliente] para a ISINSCOPE() temos o retorno True quando a linha pertence ao Cliente e False quando não pertence. Dessa forma podemos utilizar concomitantemente com SWITCH() para fazermos os cálculos necessários:
Explicando a medida:
Valor do repasse correto =
SWITCH(TRUE();
ISINSCOPE(Tabela1[Cliente]);[Valor de repasse];
SUMX(VALUES(Tabela1[Cliente]);[Valor de repasse])
)
SWITCH passa por cada linha e analisa o contexto que ela se encontra. Caso encontre um contexto [Cliente], realiza o cálculo normal de [Valor de repasse] (no caso [Soma valor venda] * [Result Taxa efetiva]). Caso não seja esse contexto, é feito um SUMX onde o parâmetro VALUES indica por onde a soma pode ser realizada (caso não tenha entendido muito bem a utilização destas funções recomendo o vídeo do Leonardo Karpinski que explica isso com maestria)😉
Podemos melhorar nossa matriz mais ainda se tirarmos os valores percentuais dos contextos que não envolvem o Cliente.
Caso queira o arquivo de exemplo, estou disponibilizando o .pbix neste link
Bom, é isso pessoal. Em caso de dúvidas, podem utilizar o campo dos comentários.
Até logo! 😎
