Leonardo Karpinski

Leonardo Karpinski

Mestre do Power BI, criador do Curso Express de Power BI e Curso Completo de Power BI. Formou mais de 16 mil alunos nos últimos anos e participou de projetos em grandes empresas nacionais e multinacionais.

DAX Avançado: Segmentação Dinâmica, Comparações Temporais, Cross Sell e Médias

Fala galera, tudo certo?!

Hoje nosso post é referente a Live #27 do Mestre!!!

Antes de começar, queria fazer algumas perguntas. Você já teve problemas com aquelas análise para comparar resultados atuais com o do ano passado ou outras semanas? E problemas com análise onde tem que comparar médias especificas com o total? Ou já ficou na dúvida do que é uma análise de cross sell (cesta de carrinho)?

Recebo frequentemente nas minha redes sociais perguntas referentes a esses temas, e a partir disso montei nossa Live #27!

Então, vem comigo e acompanha até o final que você ficar fera nesses temas de DAX avançado:

  • Análise de YoY de Faturamento e Margem (comparação entre anos subsequentes)
  • Cross sell
  • Análise entre médias

Análise: YoY Faturamento

Aqui vou fazer uma análise avaliando a medida de faturamento de vendas de uma determinada empresa comparando o ano atual com o ano anterior.

Dica:
Lembre que o “atual” no Power BI depende de contexto de avaliação. Imagine que temos dados de 2017 até 2019 na nossa base. E na página onde iremos fazer a análise filtramos o ano de 2018. Então, nosso contexto “atual” será o 2018.

Primeiro, vamos criar a medida de Faturamento:

Faturamento =
SUMX ( fVendas, fVendas[QtdItens] * fVendas[ValorUnitario] )

Com ela vamos fazer nosso primeiro visual com faturamento por categoria e também uma segmentação de dados por ano:

Figura 1: Tabela de faturamento por categoria

Figura 2: Segmentação de dados por ano

Etapas:
1. Em "Visualizações" selecionar "Tabela" → Formatar o campo conforme imagem
2. Em "Visualizações" selecionar "Segmentações" → Formatar o campo conforme imagem

Precisamos de outras 3 medidas para continuar com nossa análise:

Faturamento LY =
CALCULATE ( [Faturamento], SAMEPERIODLASTYEAR ( dCalendario[Data] ) )

Faturamento YoY =
[Faturamento] – [Faturamento LY]

Faturamento YoY % =
DIVIDE ( [Faturamento YoY], [Faturamento LY] )

As siglas de LY e YoY, vem de Last Year (ano anterior) e Year over Year (ano sobre ano).

Uma pergunta….vocês repararam algo diferente nas 4 medidas?? Dê uma atenção nos separadores utilizados dentro das funções!

E aí, percebeu? Eu estou adotando a utilização de “,” ao invés de “;“. Essa foi uma opção que veio em uma das últimas atualizações do Power BI. E para quem me acompanha sabe que tenho intenção de divulgar em outros países meu trabalho! Então, já comecei a me adaptar. Pensar no futuro em galera…

Bom, com essas medidas já conseguimos avançar um pouco com a análise em nossa tabela para o Ano de 2018:

Figura 3: Tabela de Análise

Etapas:
Adicionar as novas medidas no campo de "Valores"

Gráficos de Dispersão

Esse é um tipo de gráfico que não uso tanto, mas comecei a me policiar para utilizar com mais frequência, pois ajuda muito o usuário entender o todo e enxergar cenários.

O primeiro passo é montar o gráfico com as informações que já temos:

Figura 4: Gráfico de dispersão

Etapas:
Em "Visualizações" selecionar "Gráfico de dispersão" → Formatar o campo conforme imagem

Ajuda muito se colocarmos um parâmetro para a legenda do gráfico que diferencie se o crescimento foi alto, baixo ou negativo, por exemplo.

Isso é possível, mas antes de adicionarmos esse elemento ao visual precisamos de uma tabela auxiliar com os limites desse parâmetro.

Figura 5: Criação de tabela para segmentação

Etapas:
Em "Página Inicial" selecionar "Inserir dados" → Preencher a tabela
Importante:
Quando utilizamos limites em medidas tem que ficar muito atento onde vai utilizar menor (<), maior (>) e igual (=). Esse é um ponto que bastante gente erra e se complica no cálculo da medida.

A próxima medida que iremos criar é a que faz o cálculo do faturamento segmentado pelo nosso parâmetro de crescimento:

Faturamento por Categoria =
CALCULATE (
    [Faturamento],
    FILTER (
        VALUES ( dCliente[Categoria] ),
        [Faturamento YoY %] >= MIN ( SegmentoCrescimento[LimInf] )
            && [Faturamento YoY %] < MAX ( SegmentoCrescimento[LimSup] )
    )
)

Com ela temos a segmentação dinâmica do faturamento por crescimento! Vamos testar para ver se funciona!?? Para isso vou criar um visual com a tabela de segmentação e com os campos das medidas de faturamento com e sem o filtro:

Figura 6: Tabela com análise de crescimento

Etapas:
1. Criar a medida de Faturamento por Categoria
2. Em "Visualizações" selecionar "Tabela" → Montar os campos conforme imagem

Veja que se não aplicarmos o filtro para criar esse relacionamento virtual na medida o cálculo de faturamento não entende o contexto de crescimento!

Com isso, podemos melhorar nosso gráfico de dispersão alterando a medida do eixo X e adicionando a legenda:

Figura 7: Alteração do Gráfico de dispersão

Outro visual bem bacana e que ajuda a entender o faturamento é o gráfico de barras empilhadas! Vamos montá-lo da seguinte forma:

Figura 8: Gráfico de barras empilhado

Para visualizar a representatividade do nosso faturamento por categoria e por tipo de crescimento, vamos criar um gráfico de colunas clusterizado:

Figura 9: Gráfico de colunas clusterizado

Etapas:
1. Em "Visualizações" selecionar "Gráfico de dispersão" → Montar os campos conforme imagem
2. Em "Visualizações" selecionar "Gráfico de barras empilhado" → Montar os campos conforme imagem
2. Em "Visualizações" selecionar "Gráfico de colunas clusterizado" → Montar os campos conforme imagem

Além disso, imagine que usuário quer ver rapidamente somente as categorias negativas. O que poderíamos fazer para montar isso no nosso visual!?? Se você respondeu segmentação de dados/filtro, acertou! Vamos montar:

Figura 10: Segmentação de dados por crescimento

Etapas:
1. Em "Visualizações" selecionar "Segmentação de dados" → Montar os campos conforme imagem

Eu falei que essa classificação é dinâmica! Bom, e aí você pode perguntar “Léo, porque essa classificação é dinâmica!!???”. Te respondo! Porque se você colocar filtros e alterar contextos na página os visuais vão interagir com essa nova definição! Assim, não é estático….

Para exemplificar, vamos aplicar um filtro para o grupo “Açúcar” na página:

Figura 11: Segmentação do grupo “Açúcar”

Figura 12: Resultado do filtro aplicado

Etapas:
1. Em "Visualizações" selecionar "Segmentação de dados" → Montar os campos conforme imagem → Selecionar "Açúcar"
2. Visualizar variação dos resultados

Veja como os valores do gráfico de barras empilhadas mudou e muito!

Para finalizar nossa página, vamos colocar um cartão indicando nossa variação resumida do faturamento:

Figura 13: Cartão de linha múltipla

Etapas:
Em "Visualizações" selecionar "Cartão de linha múltipla" → Montar os campos conforme imagem

E o resultado da nossa primeira página de análise de faturamento YoY fica assim:

Figura 14: Página final de Faturamento YoY

Análise: YoY Margem

Olhar somente o faturamento pode enganar sobre a saúde financeira da empresa. A análise de margem, vem para agregar informações sobre o negócio. Então, em uma nova página vamos fazer essa análise!

Vamos precisar do nosso custo para calcular a margem. O custo vem da tabela fVendas e depende do custo unitário do produto e de quantas unidades foram vendidas, ficando assim nossa medida:

Custo =
SUMX ( fVendas, fVendas[Custo Unitário] * fVendas[QtdItens] )

E nossa medida de margem fica atrelada ao faturamento e nosso custo:

Margem =
[Faturamento] – [Custo]

E para calcular a margem do ano anterior, basta usar a SAMEPERIODLASTYEAR:

Margem LY =
CALCULATE ( [Margem], SAMEPERIODLASTYEAR ( dCalendario[Data] ) )

Com as duas, podemos calcular a variação:

Margem YoY =
[Margem] – [Margem LY]

O primeiro visual que vamos criar para começar a analisar nossa margem é o de tabela. Com ela, vamos entender por categoria quanto melhoramos ou pioramos em relação ao ano anterior (lembre que o filtro do ano é para 2018):

Figura 15: Tabela para análise de margem

Etapas:
Em "Visualizações" selecionar "Tabela" → Montar os campos conforme imagem

De cara já vemos que pioramos a margem, mesmo tendo um faturamento maior (Faturamento 2018 = R$ 10.214.809, 41/Faturamento 2017 = R$6,648,222,05/Aumento de = 53,65 %).

Bom, imagine que você mostrou esse resultado ao seu gestor, e ele começou a ficar preocupado. E agora!? Como ajudar a entender essa informação!? Lembra do gráfico de dispersão??? Ele é uma ótima ferramenta para auxiliar nesse tipo de questão.

O primeiro passo é montar um gráfico de dispersão com as informações de variação de margem e variação de faturamento por categoria:

Figura 16: Gráfico de dispersão

Etapas:
Em "Visualizações" selecionar "Gráfico de dispersão" → Montar os campos conforme imagem

Bom, com esse gráfico já consegue entender melhor a análise!?? Difícil, né! Ainda temos como melhorar e muito….o segundo passo é dividir esse gráfico em 4 quadrantes:

  • 1 – Margem Positiva e Faturamento Positivo;
  • 2 – Margem Negativa e Faturamento Positivo;
  • 3 – Margem Negativa e Faturamento Negativo;
  • 4 – Margem Positiva e Faturamento Negativo.

Para fazer essa divisão no gráfico, usamos a ajuda de “Análise”….é aquele botão de lupa ao lado do rolo de formatação:

Figura 17: Adicionando linhas para criar quadrantes

Etapas:
1. Em "Análise" selecionar "Linha Constante do Eixo X" → Valor = 0
2. Em "Análise" selecionar "Linha Constante do Eixo Y" → Valor = 0

Criando essa linhas, nosso gráfico fica assim:

Figura 18: Gráfico de dispersão com quadrantes

Dessa forma, já temos uma visão bem melhor de onde devemos analisar com mais detalhes. Imagine que a gente consegue criar um filtro que faça a segmentação desses quadrantes. Ajudaria, né!? Sim, e digo mais…vamos fazer. Vamos criar uma tabela de segmentação com os limites:

Figura 19: Tabela de segmentação

Etapas:
Em "Página Inicial" selecionar "Inserir dados" → Preencher tabela conforme imagem
Importante:
Sempre que criar tabelas de segmentação, entre em gerenciar relações para analisar se o Power BI não criou automaticamente relações para essa nova tabela.

E vamos deletar uma relação que o Power BI fez automaticamente entre essa e a outra tabela de segmentação:

Figura 20: Excluindo relação

Etapas:
Em "Modelagem" selecionar "Gerenciar relações" → Excluir relação entre as tabelas

Lembra como conseguimos utilizar esse tipo de segmentação para faturamento!?? Tivemos que colocar esses limites dentro de uma medida para o Power BI aplicar o contexto para cada linha. Então, aqui faremos algo bem semelhante:

Margem YoY por Categoria =
CALCULATE (
    [Margem YoY],
    FILTER (
        VALUES ( dCliente[Categoria] ),
        [Faturamento YoY] >= MIN ( SegmentoMC[LimInfFat] )
            && [Faturamento YoY] < MAX ( SegmentoMC[LimISupFat] )
            && [Margem YoY] >= MIN ( SegmentoMC[LimInfMC] )
            && [Margem YoY] < MAX ( SegmentoMC[LimSupMC] )
    )
)

Importante:
Veja que os limites são aplicados e associados somente para “Categoria” do cliente! Se quiser criar essa análise para informação da tabela cliente, deve-se alterar a parte de VALUES da medida.

Com essa medida, alteramos os campos do nosso gráfico de dispersão adicionando a legenda com quadrante e alterando a medida do eixo x:

Figura 21: Gráfico de dispersão atualizado

Etapas:
Adicionar a "Legenda" e atualizar o "Eixo X"

E também vamos criar 3 visuais para munir o usuário com informações para analisar categorias de cliente e produtos com margem baixa e ter uma segmentação de dados por quadrante:

Figura 22: Gráfico de barras empilhado

Figura 23: Gráfico de cascata

Figura 24: Filtro de Quadrante

Etapas:
1. Em "Visualizações" selecionar "Gráfico de barras empilhado" → Montar os campos conforme imagem
2. Em "Visualizações" selecionar "Gráfico de cascata" → Montar os campos conforme imagem
3. Em "Visualizações" selecionar "Segmentação de dados" → Montar os campos conforme imagem

Com os visuais prontos, temos nossa página de análise de margem YoY pronta, que ficou assim:

Figura 25: Página YoY Margem

Cross sell

A análise de cross-sell (cesta de carrinho/venda cruzada) é bem famosa para o pessoal de varejo e distribuição. Não sei se vocês conhecem aquela história de um mercado americano que analisou suas vendas e percebeu que nas sextas-feiras a quantidade de compras com cerveja e fralda na mesma compra era alta. Isso acontecia, pois normalmente os pais que iam comprar as fraldas já aproveitavam para levar a cerveja do fim de semana para casa. Esse é um exemplo bem clássico de análise de cesta de carrinho, e ajuda bastante os usuários a tentar entender suas vendas.

A ideia aqui é quantificar quantos clientes compraram um determinado produto A (por exemplo, cerveja) e também compraram o produto B (por exemplo, fralda).

O primeiro passo é criar a medida para contagem de clientes:

Clientes =
DISTINCTCOUNT ( fVendas[cdCliente] )

E montar essa contagem por grupo em um visual de tabela:

Figura 26: Tabela de produtos e quantidade

Beleza, aqui temos as informações que precisamos (produto e quantidade). Porém, precisamos da informação de produto duplicada! Pensa comigo, você vai estar analisando um produto de um lado em uma tabela e isso tem que estar relacionado (por isso duas tabelas) com outro produto em outra tabela.

Tem como criar essa tabela virtualmente, mas aqui vamos duplicar a tabela dProduto no Power Query:

Figura 27: Duplicando a tabela de produto

Etapas:
1. Em "Página inicial" selecionar "Transformar dados" 
2. Clicar com o botão direito do mouse na tabela dProduto → Clicar em "Duplicar" e renomear

Com a tabela duplicada, podemos fechar e aplicar para voltar ao ambiente do Power BI.

Lembra o que eu comentei lá atrás do que pode acontecer quando criamos nova tabelas no projeto? O Power BI pode criar relacionamentos automaticamente! Nesse caso, ele criou o relacionamento entre as tabelas dProduto original e a cópia. Vamos precisar desse relacionamento, entretanto vamos deixá-lo inativo:

Figura 27: Desativando relacionamento entre as tabelas

Etapas:
Em "Modelagem" selecionar "Gerenciar relações" → Desativar o relacionamento

Para calcular os clientes que compraram tanto do grupo selecionado na primeira segmentação de dados (Grupo – dProduto) quanto na segunda segmentação de dados (Grupo 1 – dProdAux1) utilizamos a intersecção entre elas. Para isso vamos criar uma medida usando o INTERSECT:

Clientes Cross =
VAR vClientes0 =
    VALUES ( fVendas[cdCliente] )
VAR vClientes1 =
    CALCULATETABLE (
        VALUES ( fVendas[cdCliente] ),
        ALL ( dProduto ),
        USERELATIONSHIP ( dProdutoAux1[cdProduto], fVendas[cdProduto] )
    )
VAR vInt =
    INTERSECT ( vClientes0vClientes1 )
RETURN
    COUNTROWS ( vInt )

Vamos aplicar duas segmentações de dados na página (Açucares para Grupo e Farinha de Trigo para Grupo 1) e ver em uma tabela o resultado de cross sell para essa análise:

Figura 28: Dois visuais de segmentação de grupo

Figura 29: Tabela resultado com os filtros aplicados

Etapas:
1. Em "Visualizações" selecionar "Segmentação de dados" → Criar 2 filtros para a página com Grupo - dProduto e Grupo 1 - dProdutoAux1
2. Em "Visualizações" selecionar "Segmentação de dados" → Montar conforme imagem

Tendo as medidas de quantidade de clientes que compraram o produto e também o de cross sell. Existe uma forma de avaliar a relação percentual entre elas com uma medida. Essa medida é chamada de “confiança” (nomenclatura usada em estatística).

Confiança =
DIVIDE ( [Clientes Cross], [Clientes] )

E com ela montamos nosso visual de matriz:

Figura 30: Matriz de cross sell (confiança)

Etapas:
Em "Visualizações" selecionar "Matriz" → Montar os campos conforme a imagem - atenção para a utilização correta dos campos de grupo de cada tabela

Para deixar um visual mais inteligível ao nosso usuário, podemos montar um heat map na matriz:

Figura 31: Formatação condicional do campo de confiança

Etapas:
Em "Valores" clicar na seta no canto direito de "Confiança" → Selecionar "Formatação condicional" → Selecionar "Cor da tela de fundo" → Preencher os campos conforme imagem

Figura 32: Resultado da matriz com Heat Map

E aí, o que está achando do visual!?? Percebeu que quando a linha é igual a coluna (Açúcares x Açúcares, Azeites x Azeites, etc…) os valores são 100% e isso deixa o visual um pouco “poluído”? Podemos fazer uma alteração na medida de confiança para que o retorno seja vazio nesses casos, melhorando bastante o visual:

Confiança =
IF (
    SELECTEDVALUE ( dProduto[Grupo] ) <> SELECTEDVALUE ( dProdutoAux1[Grupo] ),
    DIVIDE ( [Clientes Cross], [Clientes] )
)

Figura 33: Visual após alterar a medida de “Confiança”

Tá ficando bonito, né!??

Bom, agora imagina que ao invés de analisar 2 produtos você tenha que fazer com 3. Essa foi uma questão de um dos alunos nosso no grupo do Telegram quando eu estava montando a Live #27, e que dá uma complicada para calcular.

Para fazer isso, vamos criar mais uma tabela de cópia da dProdutos no Power Query:

Figura 34: Nova cópia da dProduto (dProdAux2)

Desativar o relacionamento entre a dProduto e essa cópia:

Figura 35: Desativando relacionamento

E agora, precisamos adicionar na nossa medida uma nova intersecção que é o resultado da primeira intersecção com essa segunda coluna calculada:

Clientes Cross =
VAR vClientes0 =
    VALUES ( fVendas[cdCliente] )
VAR vClientes1 =
    CALCULATETABLE (
        VALUES ( fVendas[cdCliente] ),
        ALL ( dProduto ),
        USERELATIONSHIP ( dProdutoAux1[cdProduto], fVendas[cdProduto] )
    )
VAR vClientes2 =
    CALCULATETABLE (
        VALUES ( fVendas[cdCliente] ),
        ALL ( dProduto ),
        USERELATIONSHIP ( dProdutoAux2[cdProduto], fVendas[cdProduto] )
    )
VAR vInt01 =
    INTERSECT ( vClientes0vClientes1 )
VAR vInt12 =
    INTERSECT ( vInt01vClientes2 )
RETURN
    COUNTROWS ( vInt12 )

Pronto! Com isso podemos aplicar 3 filtros na página (Grupo – dProduto, Grupo 1 – dProdAux1 e Grupo 2 – dProdAux2) que nosso visuais irão funcionar para esse contexto:

Figura 36: Segmentação de dados da página

Figura 37: Resultado do filtro aplicado

Etapas:
1. Criar mais uma segmentação na página → Grupo2 - dProdAux2

Média por categoria

Essa é uma análise interessante para avaliar como uma ou algumas categorias se comporta comparada ao total.

Como é uma comparação, temos que criar duas medidas. A primeira vai ser o faturamento médio por cliente, considerando filtros aplicados caso o usuário queira avaliar algum grupo específico. E a segunda, o faturamento médio de todos os clientes (desconsiderando qualquer filtro aplicado aos visuais) que utilizamos o ALL para desconsiderar contextos aplicados.

Fat Médio por Cliente =
AVERAGEX ( VALUES ( dCliente[cdCliente] ), [Faturamento] )

Fat Médio por Todos Cliente =
CALCULATE ( [Fat Médio por Cliente], ALL ( dCliente ) )

E a partir delas, vamos criar 3 visuais:

  • Segmentação de dados: para o usuário escolher qual categoria irá analisar
  • Tabela: com o grupo e as duas medidas para mostrar os valores
  • Gráfico de colunas e linhas: para analisar como foi a variação do resultado da categoria selecionada com a média total

Figura 38: Segmentação de dados

Figura 39: Tabela de comparação

Figura 40: Gráfico de colunas e linha

E o resultado com o filtro aplicado (padaria, no exemplo) fica assim:

Figura 41: Resultado da página com filtro aplicado

Etapas:
1. Em "Visualizações" selecionar "Segmentação de dados" → Montar os campos conforme a imagem
2. Em "Visualizações" selecionar "Tabela" → Montar os campos conforme a imagem
3. Em "Visualizações" selecionar "Gráfico de colunas e linha" → Montar os campos conforme a imagem

Análise por Semana

Frequentemente o pessoal me pede e coloca dúvidas nos grupos sobre análises temporais de semana. Esse não é o tipo de análise tão complicado se você utilizar um artificio de contador para as semanas na sua dCalendario. Trabalhando com esse contador fica muito mais fácil para fazer aquelas análises quando vira ano (por exemplo, da semana 52 para a semana 1).

Vamos construir esse contador em 2 passos. Primeiro iremos criar o número da semana na nossa dCalendario e depois por lógica matemática conseguimos criar o contador:

Figura 42: Criação de nova coluna

Semana do Ano =
WEEKNUM ( dCalendario[Data] )

Contador Semana =
 ( dCalendario[Ano] – 2017 ) * 53 + dCalendario[Semana do Ano]


Figura 43: Resultado da dCalendario com as duas colunas

Etapas:
1. Em "Dados" selecionar a tabela "dCalendario" → Em "Ferramentas de coluna" selecionar "Nova coluna"
2. Utilizar a fórmula DAX para Semana do Ano
3. Repetir o passo 1
4. Utilizar a fórmula DAX para Contador Semana

Com essa coluna para auxiliar, conseguimos criar nossas medidas comparativas para semana para o faturamento da semana passada (Faturamento LW) e o faturamento das últimas 4 semanas (Fat Médio 4W):

Faturamento LW =
CALCULATE (
    [Faturamento],
    FILTER (
        ALL ( dCalendario ),
        dCalendario[Contador Semana]
            MAX ( dCalendario[Contador Semana] ) – 1
    )
)

Fat Médio 4W =
VAR vSemanaContexto =
    MAX ( dCalendario[Contador Semana] )
RETURN
    CALCULATE (
        [Faturamento]
            CALCULATE ( DISTINCTCOUNT ( dCalendario[Contador Semana] ), fVendas ),
        FILTER (
            ALL ( dCalendario ),
            dCalendario[Contador Semana] <= vSemanaContexto
                && dCalendario[Contador Semana] > vSemanaContexto – 4
        )
    )

Figura 44: Análise de Faturamento, Faturamento LW e Fat Médio 4W

Etapas:
1. Criar as medidas de Faturamento LW e Fat Médio 4W
2. Em "Visualizações" selecionar "Matriz" → Montar os campos conforme imagem

Bom, galera, esse foi nosso conteúdo da Live #27!!! Muito do que eu fiz aqui foram de dúvidas e sugestões do pessoal nas redes sociais. Então, espero que tenha ajudado a entender essas análises que são em um nível mais avançado de DAX. Se tiver dúvidas ou sugestões para os próximos temas da Lives, já sabe, pode deixar nos comentários.

Abraço,

Leonardo

COMPARTILHE ESSE POST

Compartilhar no facebook
Compartilhar no linkedin
Compartilhar no twitter
Compartilhar no pinterest