Fala galera! Tudo bem?! O post de hoje será sobre o Dashboard de Compras e Logística que construímos na #MasterLive de Aniversário. Mostrarei tudo que você precisa para aprender a montar esse super dashboard! Bora?!
Segue um spoiler do que faremos hoje:
Clique Aqui para se tornar aluno do curso mais completo de Power BI do mundo, e tenha acesso a esse e vários outros dashboards.
Cenário
O cenário de hoje será o mesmo que os alunos do Curso Completo tiveram umas semanas atrás quando acessaram a primeira missão do nosso #BootCampPowerBI.
A Santa Klaus Factory (SKF) é uma organização global com sedes em dezenas de países, incluindo o Brasil, sendo responsável por uma ampla gama de produção e distribuição de presentes para crianças.
As fábricas distribuídas nos países funcionam de maneira independente, sendo responsável pela aquisição de matéria-prima, produção dos presentes e distribuição.
Em resumo, suas macro-operações podem ser divididas em:
- aquisição de matéria-prima (a SKF precisa buscar nas cavernas)
- produção dos presentes
- distribuição
Agora vamos dar uma olhada na nossa base de dados!
Tabela fCompras
A tabela fCompras é composta por essas colunas:
Note que o intervalo entre a DataRequisição e DataSaida representa o tempo de negociação (Lead Negociaçao). E entre a DataSaida e DataEntrega temos o tempo de entrega (Lead Entrega). Note que essa DataEntrega é a data que a Matéria Prima (MP) chegou na fábrica, ou seja, aqui estamos falando da operação de Compras, beleza?!
Já a tabela fCustosPadrão contém as seguintes colunas:
Importando base de dados
Agora, bora importar esses dados no Power BI?
Para isso, basta seguir essas etapas:
- Página Inicial ⇒ Excel ⇒ Navegue até o arquivo xlsx
- Selecione as duas tabelas
- Clique em Transformar dados
Ao clicar em “Transformar Dados” aparecerá a janela do Power Query. É lá que faremos todas as transformações, tratamentos e limpeza dos dados.
Vamos iniciar as transformações!
Transformações no Power Query
Na tabela fCompras deveremos configurar para que o cabeçalho seja a primeira linha da tabela. Para isso devemos clicar no canto esquerdo da tabela assim:
Agora, devemos checar se todas as colunas estão com o Tipo correto. Você pode checar isso verificando o símbolo que aparece ao lado esquerdo do nome das colunas. Caso não esteja correto, basta clicar lá e selecionar o Tipo desejado. Veja que no exemplo abaixo a coluna Quantidade deve ser número inteiro, então ao selecionar “Número Inteiro” o Power Query vai adicionar uma etapa do lado direito em Etapas Aplicadas:
Agora você deve fazer o mesmo para fCustoPadrao. Porém, quando você alterar a coluna CustoPadrao para número decimal, aparecerá uma mensagem de erro.
Isso ocorreu porque há texto nessa coluna. Siga esses passos:
- Clique na coluna a ser alterada e em Tranformar
- Clique em Substituir Valores
- Digite ” Q#” no primeiro campo e deixe Substituir por vazio
Pronto, agora a tabela está Ok!
Clique Aqui para aprender mais sobre Power Query.
Mesclando consultas
Próximo passo será levar essa coluna CustoUnitarioPadrao para a tabela fCompras.
- Vá em Página Inicial
- Clique em Combinar ⇒ Mesclar Consultas
- Clique nesta ordem em: idMateriaPrima e idFornecedor
- Selecione a tabela fCustoPadrao
- Clique nesta ordem em: idMateriaPrima e idFornecedor
Obs: Deixe o Tipo de Junção da forma padrão (Externa esquerda…) e clique em “OK” no final
O Power BI permite que você faça mesclagem (joins) de tabelas utilizando mais de uma coluna, ou seja, você não precisa gerar colunas “concatenadas” para ter uma Chave comum entre ambas. Legal né?! Facilita a vida!
Agora, vamos expandir apenas a coluna que nos interessa (CustoUnitarioPadrao):
Adicionando coluna no Power Query
Precisamos adicionar a coluna DataPrevistaEntrega na fCompras também. Nós temos a coluna PrazoEntregaFornecedor que é um prazo padrão para que a matéria prima chegue até a fábrica SFK. Então precisamos somar o valor dessa coluna na DataSaida, certo?!
Para fazer isso basta:
- Ir em Adicionar Coluna
- Clicar em Coluna Personalizada
- Escrever a expressão abaixo
Como já temos a DataPrevistaEntrega e não iremos utilizar o Prazo EntregaFornecedor para nada, você deve excluir essa coluna. Basta clicar com o botão direto nela e depois em Excluir. Faça o mesmo com todas as colunas que não utilizará.
Importante
Tenha apenas as colunas estritamente necessárias no seu modelo. Isso ajudará na performance. Para saber mais veja nosso artigo sobre performance: 5 Dicas Infalíveis para Performance e Otimização de Modelos – Xperiun
Agora que temos tudo que precisamos, precisamos separar os dados em tabelas fato e dimensão.
Tabelas fato e tabelas dimensão
Antes de prosseguir, preciso te fazer uma pergunta:
Você sabe o que é uma tabela fato e uma tabela dimensão?
Se já sabe, pode pular para o próximo tópico, ok?! Caso contrário, continue aqui mesmo.
Resumidamente posso dizer que:
– Tabela dimensão: é uma tabela que possui informações relacionadas a alguma entidade de negócio (cadastro de algo)
– É obrigatório possuir um ID (código / chave primária) único que represente a dimensão
– Tabela fato: é uma tabela que possui movimentações, eventos/registros históricos (geralmente tem uma data atrelada)
– O que eu preciso ter na Tabela Fato que é relativo à dimensão? Apenas o ID (código) da dimensão.
Sempre que você tiver desenvolvendo um dashboard do zero, você precisa tentar ao máximo dominar esses conceitos para aplicar corretamente a modelagem e relacionamento entre tabelas.
Existem alguns esquemas de relacionamentos que têm nomes próprios. O principal deles e que deve ser usado de preferência é o Esquema Estrela (Star Schema) onde as tabelas fato estão relacionadas somente a tabelas dimensão e as tabelas dimensão somente as fato. Outro modelo bem conhecido é o Snow Flake onde têm casos de as tabelas dimensão que possuem relacionamentos com outras tabelas dimensão. É um formato conhecido e que também é utilizado, porém tem pior performance e maior dificuldade para se criar hierarquias.
Pronto, agora você está pronto para prosseguir. Seguindo a recomendação que falei agora, vamos definir nossas tabelas dimensão.
Obtendo as tabelas dimensão
Clique com o botão direito no nome da consulta fCompras do lado esquerdo e escolha a opção Duplicar. Essa nova tabela você chamará de BaseDados. Será a partir dela que criaremos nossas tabelas dimensão.
Volte na consulta fCompras e remova as colunas: “PrazoEntregaFornecedor”, “Comprador”, “MateriaPrima”, “Fornecedor”, “TemContrato”. Devemos fazer isso porque essas colunas pertencerão às tabelas dimensões que criaremos a seguir.
Você pode ter reparado que há duas opções: Duplicar e Referência. Mas e aí, qual a diferença entre elas?
Duplicar
Você copia tudo que foi feito numa consulta (todas as etapas) e cola numa nova consulta. A partir disso, tudo que você fizer na consulta original ficará apenas lá e a consulta nova fica livre para seguir seu próprio rumo.
Referência
Você usa uma consulta de referência e realiza outras transformações em seguida. Neste caso, tudo que for realizado na consulta original aparecerá aqui.
Se tiver dúvida, procure ver o que acontece nas etapas e tente “interpretar” o código que aparece.
Voltando ao que estávamos fazendo:
Quando duplicamos a fCompras, apareceu uma nova consulta com todas as etapas que tínhamos antes. Mude o nome para dFornecedor.
Agora, devemos deixar apenas informações relacionadas ao fornecedor, beleza?!
Para isso, clique nas colunas que deseja deixar (mantendo a tecla CTRL pressionada) e depois com o botão direito em uma delas, selecione “Remover outras colunas”.
Como se trata de uma tabela dimensão, não devemos ter o mesmo ID em várias linhas, certo?! Afinal ele é uma chave única. Portanto, devemos clicar na coluna idFornecedor e depois clicar em Remover Duplicatas.
Ainda na tabela dFornecedor precisamos adicionar uma coluna coma regra de negócio da SKF! A regra define basicamente que cada fornecedor tem um prazo de entrega padrão. Siga as etapas:
- Vá em Adicionar Coluna
- Clique em Coluna Condicional
- Digite PrazoEntregaPadrao em Nome da nova coluna e depois preencha o restante conforme a figura mostra a seguir:
Bora criar agora a tabela dComprador. Mesmo esquema: vá na tabela fato e clique com o botão direito nela para duplicá-la.
Essa é a dimensão que mostrará informações sobre compradores, certo?! Então você deve remover as duplicatas selecionando a coluna idComprador e depois mantenha apenas as colunas que interessam. Para isso, clique nas colunas que deseja deixar (mantendo a tecla CTRL pressionada) e depois com o botão direito em uma delas, selecione “Remover outras colunas”.
E aí, entendeu tudinho?! Espero que sim porque agora é com você! Para a tabela de dMateriaPrima você deve fazer o mesmo, beleza?! O resultado final fica assim:
Bom, agora podemos clicar em Fechar e Aplicar e seguir com a Modelagem.
Modelagem
Para visualizar os relacionamentos entre as tabelas, vá em Modelo:
A figura acima já te deu um spoiler de como ficará nossa modelagem, né?!
Mas vou passar certinho aqui embaixo o passo a passo:
- Arraste DataRequisicao(tabela fCompras) na direção de Date (tabela dCalendario) . Cardinalidade: Muitos para 1. Direção do filtro cruzado: Único
- Arraste idComprador (tabela fCompras) na direção de idComprador (tabela dComprador) . Cardinalidade: Muitos para 1. Direção do filtro cruzado: Único
- Arraste idMateriaPrima (tabela fCompras) na direção de idMateriaPrima (tabela dMateriaPrima). Cardinalidade: Muitos para 1. Direção do filtro cruzado: Único
- Arraste idFornecedor (tabela fCompras) na direção de idFornecedor (tabela dFornecedor). Cardinalidade: Muitos para 1. Direção do filtro cruzado: Único
- Arraste DataEntrega (tabela fCompras) na direação de Date (tabela dCalendario). Cardinalidade: Muitos para 1. Direção do filtro cruzado: Único. Relacionamento: Inativo
- Arraste DataSaida (tabela fCompras) na direção de Date (tabela dCalendario). Cardinalidade: Muitos para 1. Direção do filtro cruzado: Único. Relacionamento: Inativo
Repare que os itens 5 e 6 possuem relacionamento Inativo porque só é possível relacionar uma coluna de Data por vez através de relacionamento físico (esse modo de “arrrastar” é físico). Nós desativamos o relacionamento aqui (5):
Lembrando que para conferir se o “arrastar de campos” fez o relacionamento corretamente, dê um clique duplo sobre a linha que liga os dois campos (colunas) e confira se está igual ao que falei ali em cima, ok?!
Importante
Se você nunca ouviu falar sobre relacionamento e modelagem antes, sugiro que leia o meu Guia Completo sobre isso: Guia Definitivo sobre RELACIONAMENTOS entre tabelas e Modelagem de Dados – Xperiun. É fundamental que você entenda esses dois conceitos para fazer qualquer projeto de BI.
Prosseguindo… E aí, você acha que está faltando alguma tabela dimensão?!
É a tabela de datas que está faltando. Você poderia construí-la no Power Query mas também poderia fazer por DAX. Afinal, você sabe o que é isso?!
Segundo a própria Microsoft, DAX (Data Analysis Expressions) é uma coleção de funções, operadores e constantes que podem ser usados em uma fórmula ou expressão, para calcular e retornar um ou mais valores.
Agora vamos para as temidas medidas DAX!
Cálculos com DAX
Primeiro vou passar o racional de todas as medidas (junto das fórmulas) e no próximo tópico colocaremos essas medidas nos visuais, beleza?!
dCalendario via DAX
Para criar a tabela de Data precisamos utilizar a função CALENDARAUTO. Essa não precisa de argumento pois ela vai varrer todo o modelo e buscar a menor e maior data existente. Veja:
dCalendario =CALENDARAUTO ()Após isso, precisamos das colunas auxiliares da tabela de datas, como coluna de ano, mês, etc.
Para isso, clique com o botão direito na tabela dCalendario que acabamos de criar e selecione Nova coluna.
Agora basta inserir cada uma das fórmulas abaixo:
Ano = YEAR ( dCalendario[Date] )Mês = MONTH ( dCalendario[Date] )
Mês Abrev =
FORMAT (
dCalendario[Date],
“mmm”
)
MesAno =
YEAR ( dCalendario[Date] ) * 100
+ MONTH ( dCalendario[Date] )
Nome do Mês =
FORMAT (
dCalendario[Date],
“mmmm”
)
Medidas do negócio
Agora, precisamos calcular o total de custo com as matérias primas. Teremos então:
Custo Real =SUM ( fCompras[ValorTotal] )
Agora, precisaremos calcular qual o tempo médio entre a data de requisição e a data de entrega. Pense sempre de dentro para fora quando utilizar funções compostas como essa, beleza?! Veja que DATEDIFF vai trazer o intervalo entre as duas datas em número de dias (por isso usamos DAY no último argumento. E por fim, faremos a média de cada linha, com esse valor encontrado. Essas funções que terminam com o “X” no final são chamadas de funções iteradoras. Se quiser entender melhor veja esse link.
Lead Compra =AVERAGEX (
fCompras,
DATEDIFF (
fCompras[DataRequisicao],
fCompras[DataEntrega],
DAY
)
)
As duas próximas medidas serão bem parecidas, veja:
Lead Entrega =AVERAGEX (
fCompras,
DATEDIFF (
fCompras[DataSaida],
fCompras[DataEntrega],
DAY
)
)
O tempo de negociação ficará assim:
Lead Negociação =AVERAGEX (
fCompras,
DATEDIFF (
fCompras[DataRequisicao],
fCompras[DataSaida],
DAY
)
)
O Custo Padrão utilizará a função iteradora SUMX porque precisaremos realizar o produto entre duas colunas em cada linha da tabela fCompras e no final somar. Veja:
Savings =VAR vSavings = [Custo Padrão] – [Custo Real]
RETURN
IF (
vSavings > 0,
vSavings
)
Para obter o quanto economizamos depois da negociação com o fornecedor, isto é, saber o Saving, precisamos calcular a diferença entre o Custo Padrão e o Custo Real. Note na fórmula a seguir que usamos variáveis (veja o uso do “VAR”) e adivinha?! Também tenho um artigo completo sobre o uso de variáveis aqui.
A medida ficou assim, então:
Savings =VAR vSavings = [Custo Padrão] – [Custo Real]
RETURN
IF (
vSavings > 0,
vSavings
)
Já o Saving % é a divisão do Saving pelo Custo padrão. Bem intuitiva essa função aqui embaixo, né?!
Savings % =DIVIDE (
[Savings],
[Custo Padrão]
)
Para calcular o Atraso Entrega, precisamos fazer a diferença entre DataPrevistaEntrega e DataEntrega, certo?! Isso porque tudo que ultrapassou a data de previsão será considerada entrega atrasada. E para fazer a diferença entre duas datas já sabemos qual função usar, lembra?!
Atraso Entrega =AVERAGEX (
fCompras,
DATEDIFF (
fCompras[DataPrevistaEntrega],
fCompras[DataEntrega],
DAY
)
)
Além das medidas, precisamos adicionar uma coluna na tabela fCompras mostrando se houve ou não atraso na entrega em relação a data de entrega previsata. Para isso, basta clicar com o botão direito na tabela fCompras e adicionar uma nova coluna com a seguinte expressão:
HouveAtraso =VAR vDiff =
DATEDIFF (
fCompras[DataPrevistaEntrega],
fCompras[DataEntrega],
DAY
)
RETURN
IF (
vDiff,
“Sim”,
“Não”
)
Note que quando o DataPrevistaEntrega é igual a DataEntrega, o resultado da fómula é “Não” pois o valor da variável é zero.
Beleza, agora estamos prontos para começar nossos visuais!
Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.
Visual
Para facilitar, vamos adicionar logo o background (Segundo plano da página).
Backgrounds das páginas
Toda a parte de design eu fiz no Power Point! Isso mesmo, aquele seu velho amigo! Basta colocar a criatividade para funcionar, alinhar os elementos, combinar cores, etc.
Se quiser dicas sobre storytelling no Power BI, adivinha?! Temos um artigo também falando disso aqui: O poder do Storytelling no Power BI e como criar Dashboards incríveis – Xperiun.
Depois de criar seu background, basta salvar a apresentação como .SVG que é formato que preserva a boa resolução da imagem. Caso seu Power Point não possua esse recurso, salve como imagem mesmo (png, jpeg, etc), paciência.
No Power BI, basta importar essas indo no Rolinho de tinta e em Segundo plano da página:
Lembre-se de colocar Transparência = 0% e Ajuste da Imagem = Ajuste, beleza?!
Visualizações
Agora vamos adicionar os elementos!
Para adicionar um cartão, selecione o visual em Visualizações e arraste a medida desejada em Campos.
Ajuste o tamanho, cor, fundo do cartão conforme desejar. Como teremos outros cartões, sempre sugiro que você dê aquele CTRL+C & CTR+ V esperto para evitar o retrabalho de ter que formatar tudo de novo para um visual que é igual e só muda a medida, show?!
Então ficou assim:
O próximo gráfico será este:
Todos as propriedades como título, cor da legenda, eixos, tamanho dos rótulos (labels), etc podem ser modificadas clicando naquele Rolinho de tinta do painel Visualizações, beleza?!
Prosseguindo…
Podemos adicionar também o Gráfico de Rosca para mostrar qual o percentual dos custos vem de fornecedores com e sem contrato:
Os próximos dois visuais serão gráficos de barra com as seguintes medidas/colunas:
Para o título, se você quiser usar um fonte diferente daquelas que existem no Power BI Desktop, você pode importar uma imagem sem fundo e inserir assim:
Para inserir os filtros do topo, basta selecionar o visual Segmentação de Dados e arrastar o campo que deseja:
Agora vamos inserir um outro tipo de filtro: o Cartão de múltiplas linhas.
Os próximos visuais são:
- Gráfico de área
Eixo: MesAno
Valores: Savings - Gráfico Cartão de linha múltipla
Campos: Savings - Gráfico Cartão
Campos: Savings %
Da mesma forma que inserimos o título da página, vamos inserir uma imagem que será nosso botão para mudar de visual conforme o clique, veja:
Agora precisamos inserir os últimos 3 gráficos.
O primeiro será o de Saving por Fornecedor:
O gráfico debaixo será o Saving por matéria prima, e você só precisará alterar o Eixo (para MateriaPrima) e o título.
Faremos algo legal agora: vamos inserir um botão para que ao clicar apareça o gráfico Saving por Comprador.
Botão com Bookmarks
Para isso, da mesma forma que fez com o título, insira as duas imagens do botão. Uma imagem será com ao verde aceso no lado esquerdo e a outra imagem com o destaque do lado direito. Você pode criar a imagem no Power Point ou achar alguma imagem similar no Google.
Uma dica para por as duas imagens na mesma posição é digitar em Geral as posições exatas (X e Y).
Preciso também que você copie e cole o gráfico Saving por Comprador e altere seu título e o campo utilizado (Eixo = Comprador). Depois de criado, posicione-o no mesmo lugar que o outro.
Vamos brincar de mostrar/ocultar os elementos. Para isso você precisa ativar o painel de seleção aqui:
No painel de Indicadores (bookmarks) você poderá tirar “retratos” do status atual da página e posteriormente vincular esses retratos às ações de botões.
No painel de Seleção você pode mostrar e ocultar cada elemento, basta clicar naquele “olhinho” ali.
Renomeei as imagens para saber qual delas estará relacionada ao gráfico por fornecedor e comprador (basta dar um duplo clique no nome que aparece no Painel de Seleção).
Siga os passos abaixo:
- Deixe o Botão1 e Saving por Fornecedor visíveis no Painel de Seleção
- Oculte e Botão2 e Saving por Comprador
- Vá no Painel de Indicadores e clique em Adicionar (dê o nome Fornecedor ON). Desabilite “dados” ao selecionar “Mais opções” ao lado do nome do indicador criado. Por fim, atualize o indicador (em Mais Opções também).
- Faça o inverso: oculte Botão1 e Saving por Fornecedor e mostre Botão2 e Saving por Comprador
- Vá no Painel de Indicadores e clique em Adicionar (dê o nome Comprador ON). Desabilite “dados” ao selecionar “Mais opções” ao lado do nome do indicador criado. Por fim, atualize o indicador (em Mais Opções também).
- Selecione apenas o Botão1 e no Painel Formatar Imagem busque por Ação e insira o Indicador Comprador ON
- Selecione apenas o Botão1 e no Painel Formatar Imagem busque por Ação e insira o Indicador Fornecedor ON
Se tiver alguma dúvida nessas etapas, dê uma olhada nas próximas figuras:
Bora agora inserir o último recurso do nosso visual: Tooltips!
Tooltips
Antes de começar o que é Tooltip ?
Dica
Tooltip (Dica de Ferramenta): possibilita exibir informações extras que sejam relevantes à análise, geralmente cruzando informações de outras dimensões
Clique no botão amarelo “+” para criar uma nova página.
Nessa nova página vá no “Rolinho de tintas” e depois em Informações da página. Ative o “Dica de Ferramenta” e note que o nome está como “Tooltip”, beleza?! Usaremos isso depois. Veja:
Vá em Tamanho da página e selecione o Tipo como Dica de Ferramenta:
Agora, vamos inserir alguns elementos:
- Negociação por Comprador
– Gráfico de barras clusterizado
– Eixo: Comprador
– Valores: Lead Negociação - Entrega por fornecedor
– Gráfico de barras clusterizado
– Eixo: Fornecedor
– Valores: Lead Entrega - Título tooltip:
– Gráfico Cartão
– Valores: Nome do Mês - Cartão da esquerda (inferior)
– Gráfico Cartão
– Valores: Lead Negociação - Cartão da direita (inferior)
– Gráfico Cartão
– Valores: Lead Entrega
Para finalizar o Tooltip precisamos indicar onde ele irá aparecer.
Volte na página anterior e adicione o tooltip no gráfico desejado:
Habilite o Dica de Ferramenta e selecione a página “Tooltip” (poderia ser outro nome caso você tenha alterado o nome da página anteriormente).
O resultado do Tooltip ficou assim:
Agora sim, ufa… Acabou!
Espero que esse artigo seja útil para você e se tiver sugestão de algum tema para a próxima live, comenta aqui embaixo.
Abraços,
Leonardo.
