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.

Dashboard de Estoque: Análise de Saldo e Custo

Fala galera! Tudo bem!??

Hoje o post é referente ao conteúdo da Live#2 do Workshop #1. Vamos montar um Dashboard de Estoque com Análise de Saldo e Custo. O principal desafio nele é o que iremos trabalhar no Power Query.

O relatório vai conter os seguintes visuais:

  • Análise de custo e quantidade ao longo do tempo
  • Análise de custo por tipo de produto
  • Análise de custo e quantidade por armazém
  • Análise de custo por família e grupo
  • Filtros: temporais, por tipo de produto e por armazém

Base de dados

Nossa base é composta por 4 arquivos:

  • Armazem: tabela dimensão com os cadastros dos armazéns da empresa
  • Estoque2018: tabela fato com as ocorrências do estoque de 2018
  • Estoque2019: tabela fato com as ocorrências do estoque de 2019
  • Produtos: tabela dimensão com os cadastros de produtos da empresa

Figura 1: Arquivos base de dados

Figura 2: Arquivo Armazem

Figura 3: Arquivo Estoque2018

Figura 4: Arquivo Estoque2019

Figura 5: Arquivo Produtos

ETL

Para importar os arquivos no Power Query iremos utilizar um artificio de conexão direto via pasta:

Figura 6: Obter dados de Pasta

Figura 7: Seleção da Pasta

Figura 8: Seleção de transformação de dados

Etapas: 
1. Em "Página inicial" clicar em "Nova fonte" → Clicar em "Pasta"
2. Colar o caminho da pasta dos arquivos no campo
3. Clicar em "Transformar Dados"

O resultado dessa importação vai servir como a base de dados para nosso projeto:

Figura 9: Alteração do Nome da Consulta

Nossas tabelas fato e dimensão serão referenciadas a partir dessa consulta como se fossem ramificações da mesma:

Figura 10: Criação das tabelas fato e dimensão

Etapas: 
1. Clicar com o botão direito na consulta "BaseDados" → Clicar em "Referência" (3x)
2. Renomear as novas consultas, para: dArmazem, dProduto e fEstoque

Após referenciar as novas consultas, podemos ver as conexões em “Dependência de Consulta” e os parâmetros para configurar gateway em “Configurações da fonte de dados”:

Figura 11: Exibição das dependências de consultas

Figura 12: Configurações da fonte de dados

dArmazem

A partir da referência para a dArmazem, vamos exibir seu conteúdo clicando em Binary:

Figura 13: Expansão do conteúdo de Armazem.csv

Etapas: 
Selecionar a consulta "dArmazem" e clicar em "Binary" da linha de "Armazem.csv"

Figura 14: Resultado da Expansão

Veja que precisamos fazer um tratamento para retirar a linha em branco, retirar a linha de “Cadastro de Armazém” e nomear nossas colunas. Para isso, vamos fazer os seguintes passos:

Figura 15: Removendo linhas em vazio

Figura 16: Removendo linha “Cadastro de Armazéns”

Figura 17: Promovendo cabeçalho

Etapas: 
1. Clicar na seta para baixo ao lado de "Column 1" → Clicar em "Remover Vazio"
2. Clicar na seta para baixo ao lado de "Column 1" → Desmarcar "Cadastro de Armazéns"
3. Clicar no painel a esquerda da "Column 1" → Clicar em "Usar a Primeira Linha como Cabeçalho"

Figura 18: Resultado final da dArmazem

dProduto

Você lembra da estrutura do arquivo de Produtos!?? Nele tínhamos diversas planilhas com os cadastros por tipo de produto fornecido (serviço, produto intermediário, produto acabado, embalagem, etc…). Então, além de tratar valores duplicados para cabeçalhos, pois cada planilha temos essa informação repetida, temos que criar uma coluna para identificar o tipo de produto fornecido.

O primeiro passo é expandirmos o arquivo:

Figura 19: Expansão do conteúdo de Produtos.xlsx

Figura 20: Selecionando colunas

Figura 21: Expandido as Tabelas

Etapas: 
1. Selecionar a consulta "dProduto" → Clicar em "Binary"
2. Selecionar a coluna  "Data" → Em "Página Inicial" clicar em "Remover Colunas" → Clicar em "Remover Outras Colunas"
3. Clicar no ícone de expansão ao lado no canto superior direito da coluna "Data" → Desmarcar a opção de usar o nome da coluna original como prefixo" → "Ok"

Figura 22: Resultado final da expansão

Com isso, vamos adicionar uma nova coluna para o atributo de tipo de produto:

Figura 23: Adicionando coluna condicional

Figura 24: Preenchimento das linhas

Etapas: 
1. Em "Adicionar Coluna" clicar em "Coluna Condicional"
2. Utilizar a lógica descrita
3. Clicar com o botão direito na nova coluna → Clicar em "Preenchimento" → Clicar em "Para Baixo"

Com a nova coluna criada, podemos remover a informação do tipo de produto da primeira coluna utilizando um filtro:

Figura 25: Filtro de texto

Figura 26: Especificação do filtro

Etapas: 
1. Clicar na seta no canto superior direito de Column1 → Clicar em "Filtro de Texto" → Clicar em "Não começa com..."
2. Preencher o campo com "TIPO DE PRODUTO"

Então, criamos nosso cabeçalho com a primeira linha:

Figura 27: Criação do cabeçalho

Figura 28: Alteração do cabeçalho para a coluna adicionada

Etapas: 
1. Clicar no ícone de tabela no canto superior esquerdo da "Column 1" → Clicar em "Usar a Primeira Linha como cabeçalho"
2. Renomear a coluna "Personalizar" para "Tipo Produto"

Veja que temos um texto desnecessário “TIPO DE PRODUTO: ” em todas as linhas dessa coluna. Vamos remover utilizando “Extrair” para deixar a informação mais limpa:

Figura 29: Extração de texto após delimitador

Figura 30: Filtro dos cabeçalhos duplicados

Etapas: 
1. Com a coluna "TipoProduto" selecionada clicar em Transformar" → Clicar em "Extrair" → Clicar em "Texto Após o Delimitador" → Preencher o campo
2. Clicar na seta no canto superior direito da coluna "CodigoProduto" → Desmarcar "CodigoProduto"

O último passo é verificar se todas as colunas estão com os tipos de dados definidos. Se você reparar bem, irá ver que nossa coluna de custo unitário não está com o tipo definido (ver o ícone abaixo):

Figura 31: Definindo tipo de dados

Esse símbolo com ABC/123 nos informa que os dados dessa coluna não estão definidos. O formato correto é de número decimal :

Etapas: 
Clicar no ícone ABC/123 da coluna "CustoUnit" → definir como "Número Decimal"
Importante:
Sempre que finalizar o tratamento das suas tabelas no Power Query, verifique se todas as colunas estão com o tipo de dados definido corretamente. Isso com certeza te ajudará a não ter problemas nos relacionamentos dos modelos e cálculos.

Figura 32: Resultado final da dProduto

fEstoque

Para nossas tabelas fato fEstoque temos um passo extra para realizar. Perceba que são duas tabelas (2018 e 2019)! Vamos combinar as duas em uma única com auxilio da função Excel.Workbook:

Figura 33: Arquivos que serão combinados

Figura 34: Filtro de arquivos

Figura 35: Adicionando coluna com Excel.Workbook

Etapas: 
1. Clicar na seta no canto superior direito da coluna "Name" → Clicar em "Filtros de Texto" → Clicar em "Começa com..." → Preencher com Estoque
2. Em "Adicionar Coluna" clicar em "Coluna Personalizada" → Preencher com a Fórmula

Figura 36: Resultado da coluna adicionada

Para deixar a tabela mais limpa, vamos remover todas as colunas exceto a “Name” e “Conteudo”:

Figura 37: Removendo colunas

Etapas: 
Com as colunas "Name" e "Conteudo" selecionadas → Em "Página Inicial" selecionar "Remover Colunas" → Clicar em "Remover Outras Colunas"

Com isso, vamos expandir as informações de “Conteudo”:

Figura 38: Expandindo “Conteudo

Etapas: 
Clicar na seta no canto superior direito da coluna "Conteudo" → "OK"

Novamente, iremos aplicar um filtro e remover outras colunas para deixar a tabela limpa:

Figura 39: Filtro somente de tabelas

Figura 40: Removendo outras colunas

Figura 41: Expandindo dados

Etapas: 
1. Clicar na seta no canto superior direito da coluna "Kind" → Filtrar "Table"
2. Com as colunas "Name" e "Data" selecionadas → Em "Página Inicial" selecionar "Remover Colunas" → Clicar em "Remover Outras Colunas"
3. Clicar na seta no canto superior direito da coluna "Data" → "OK"

Figura 42: Resultado da expansão da tabela

Aqui é interessante analisar que os meses estão distribuídos em colunas. Essa não é uma boa forma para fazer os cálculos e relacionamentos no Power BI. Além disso, temos que juntar essa informação com o ano que iremos extrair da coluna “Name”. Para isso, vamos fazer o seguinte:

Figura 43: Transformar as colunas de meses em linhas

Figura 44: Extraindo a informação de Ano da coluna Name

Figura 45: Extraindo a informação do Mês da coluna Atributo

Figura 46: União entre as colunas

Figura 47: Detecção do tipo de dado

Figura 48: Remoção de Vazio

Figura 49: Alteração do Nome

Etapas: 
1. Selecionar as colunas Name, CodigoProduto e CodigoArmazem → Clicar com o botão direito do mouse em uma das colunas → Clicar em "Transformar outras colunas em linhas"
2. Selecionar a coluna Name → Clicar em "Transformar" → Clicar em "Extrair" → Clicar em "Texto Entre Delimitadores" → Preencher os campos
3. Selecionar a coluna Atributo → Clicar em "Transformar" → Clicar em "Extrair" → Clicar em "Texto Entre Delimitadores" → Preencher os campos
4. Selecionar a coluna Atributo e depois a coluna Name → Clicar com o botão direito no mouse em uma das colunas → Clicar em "Mesclar colunas" → Preencher campos
5. Selecionar todas as colunas → Clicar em Transformar e selecionar "Detectar Tipo de Dados"
6. Selecionar coluna Valor → Remover Vazio
7. Renomear coluna Valor

dCalendario

Nossa tabela dCalendario será copiada da que criamos para a Live#1 do Workshop.

Não se lembra como foi feita? Calma que é só olhar o post da Live#1 e ver o passo a passo: clique aqui

Figura 50: Copiar a consulta da dCalendario da Live#1

Figura 51: Colar a consulta no Power Query

Etapas: 
1. Alterar para List.Dates na Fonte da consulta dCalendario do arquivo da Live 1
2. Copiar a consulta
3. Colar a consulta

Como o inicio da nossa avaliação é em 2018 e não 2017 e o período é de 2 anos (730 dias), vamos alterar esses parâmetros na fonte da dCalendario:

Figura 52: Alteração de inicio e dias do período

Etapas: 
Na consulta dCalendario, em "Fonte" alterar a data de inicio para 2018 e a contagem de dias para 730

Por fim, iremos desabilitar a carga da consulta de BancoDados, pois não será utilizado nas análises do Power BI:

Figura 53: Desabilitando carga da consulta

Podemos “Fechar e aplicar” as consultas do Power Query e salvar o arquivo.

Relacionamentos

O primeiro passo após salvar o arquivo é verificar os relacionamentos entre as tabelas fato e dimensão:

Figura 54: Verificação de relacionamentos

Veja a importância desse passo na construção do projeto. O Power BI acabou não fazendo o relacionamento entre nossa tabela dCalendario com a tabela fEstoque. Se não tivéssemos verificado o relacionamento, qualquer análise que fizessemos baseada entre as duas tabelas ficaria comprometido. Bora arrumar!???

Figura 55: Criação do relacionamento

Etapas: 
Arrastar a coluna "Data" da "dCalendario" em cima da coluna "DataEstoque" da "fEstoque"

Cálculos e Visuais

Antes de fazer as medidas, é muito importante pensarmos em como o valor de estoque se comporta. É possível e podemos somar o estoque de bananas com laranja, por exemplo. Então, o estoque é uma medida que podemos fazer adição. Porém, não podemos somar o estoque de ontem com o de hoje. Isso quer dizer que temos que definir a data (ou período) de avaliação.

Para melhor entendimento vamos criar um gráfico do estoque ao longo do tempo e um cartão com a medida de quantidade de estoque:

Fórmula DAX: 
Qtd Estoque =
SUM ( fEstoque[Saldo] )

Figura 56: Classificando a coluna “Nome do Mês”

Figura 57: Gráfico do estoque ao longo do tempo

Figura 58: Cartão da quantidade de estoque

Etapas: 
1. Criar a medida
2. Classificar a coluna de Nome do Mês
3. Criar gráfico de colunas empilhadas → Atribuir os campos conforme imagem
4. Criar cartão → Atribuir os campos conforme imagem

Veja que o valor no cartão é muito alto, ele é a soma de todo o estoque no período inteiro. E sabemos que isso não corresponde a quantidade atual. Para avaliarmos o contexto correto de estoque usaremos o auxilio da CALCULATE e da LASTDATE:

Qtd Estoque =
CALCULATE ( SUM ( fEstoque[Saldo] )LASTDATE ( fEstoque[DataEstoque] ) )

Com isso, o contexto do período de avaliação fica definido e não aberto para qualquer análise que fizermos.

Antes de montar os visuais, vamos importar o background da página e o tema do Dashboard:

Figura 59: Importando segundo plano de página

Figura 60: Importando tema

Etapas: 
1. Em "Segundo plano de página" clicar em "Adicionar imagem" → Selecionar o arquivo → Alterar transparência e Ajuste
2. Em "Exibição" clicar em "Procurar temas" → Selecionar Arquivo

O arquivo que importamos (Tema Urban.json) pode ser aberto no Notepad para você visualizar as definições da paleta de cores para cada atributo:

Figura 61: Tema Urban.json

A primeira formatação que iremos fazer será no gráfico de colunas:

Figura 62: Gráfico de colunas empilhadas formatado

Formatação: 
Eixo X → Tamanho do texto: 10 pt; Cor: E6E6E6
Eixo Y → Desativado
Rotulo de dados → Tamanho do texto: 10 pt; Cor:E6E6E6
Título → Tamanho do texto: 12 pt; Cor:FFFFFF; Família de fontes: Segoe (Bold)
Tela de Fundo → Transparência: 0 %; Cor:333333

Para a segmentação de dados por Ano, vamos importar um visual personalizado do marketplace (fora dos padrões):

Figura 63: Obter novos visuais

Figura 64: Escolhendo o visual

Etapas: 
Clicar nos 3 pontos no canto inferior direito de Visualizações → Clicar em Obter mais visuais → Pesquisar por "Chiclets" → Selecionar o visual de Chiclet Slicer

Figura 65: Filtro de Ano

Formatação: 
Cabeçalho → Desativado
Chiclets → Tamanho de texto: 11 pt; Altura: 35 e Largura: 165
Tela de fundo → Transparência: 0 %; Cor:333333

Os outros dois filtros na nossa página são de tipo de produto e de armazém:

Figura 66: Filtro de Tipo de Produto

Formatação: 
Itens → Cor da fonte: FFFFFF
Cabeçalho de segmentação → Cor da fonte: FFFFFF; Tamanho do Texto: 11 pt
Tela de fundo → Transparência: 0 %; Cor:333333

Para o visual de filtro de armazéns só precisamos copiar o visual que acabamos de criar e alterar o campo para Armazem:

Figura 67: Filtro de Armazem

Formatação: 
Copiar o visual de filtro de TipoProduto e alterar o campo para Armazem

Também iremos aproveitar a formatação do primeiro gráfico que montamos para os outros visuais. Então, vamos copiar e colar 3 gráficos na nossa página:

Figura 68: Visuais copiados

A próxima medida que iremos utilizar é a de custo. Para isso, temos que calcular a quantidade do estoque multiplicado pelo seu custo unitário. Para fazer essa relação entre as tabelas usamos o auxilio da função RELATED. Além disso, temos que usar a LASTDATE para não termos o problema de contexto do período de avaliação mencionado anteriormente:

Custo Estoque =
CALCULATE (
    SUMX ( fEstoque; fEstoque[Saldo] * RELATED ( dProduto[CustoUnit] ) );
    LASTDATE ( fEstoque[DataEstoque] )
)

Formato: Moeda, 2 casas decimais

A importância da SUMX com a RELATED é que corremos linha a linha essa relação do custo do produto com seu estoque! Vamos criar um cartão de linha múltipla (com a formatação copiada do gráfico) e colocar as medidas de custo e quantidade de estoque.

Figura 69: Cartão de linhas múltipas

Formatação: 
Rótulo de dados → Cor da fonte: FFFFFF, Tamanho de texto: 12 pt; Família de fontes: Segoe (Bold)
Rótulo de categoria → Família de fontes: Segoe (Bold)

O próximo visual que vamos criar é para analisar o custo de estoque por tipo de produto. Então, vamos alterar um dos gráfico copiados para Gráfico de barras empilhado:

Figura 70: Gráfico de barra empilhado

Etapas: 
Alterar o tipo de gráfico para "Gráfico de barras empilhados" → Atribuir os campos conforme imagem

O próximo gráfico copiado que iremos alterar será para o formato Treemap, para analisarmos a quantidade de estoque em cada armazém:

Figura 71: Treemap

Etapas: 
Alterar o tipo de gráfico para "Treemap" → Atribuir os campos conforme imagem

O último gráfico que vamos alterar será para a análise de custo de estoque por produto (família e tipo):

Figura 72: Gráfico de barras empilhadas

Etapas: 
Alterar o tipo de gráfico para "Gráfico de barras empilhadas" → Atribuir os campos conforme imagem

Nesse momento, o visual da nossa página está assim:

Figura 73: Visual geral da página

Pense como se você fosse o usuário final. O que acha do gráfico de custo de estoque por família e grupo!?? Está fácil de interpretar? Normalmente, o usuário quer enxergar os produtos acabados nesse tipo de gráfico. Para isso, podemos alterar o filtro lateral e deixar o produto acabado selecionado. Vamos ver o que acontece:

Figura 74: Filtro aplicado

O visual que queríamos melhorar, ficou bom! Porém, o visual para analisar o custo de estoque por tipo de produto ficou inútil. Podemos editar a interação para que esse visual em específico não seja filtrado:

Figura 75: Editando interações entre visuais

Etapas: 
Selecionar o visual de segmentação → Clicar em "Formato" → Clicar em "Editar Interações" → Desabilitar no visual desejado

Vamos inserir dois ícones um para o custo e o outro para a quantidade no cartão:

Figura 76: Inserindo ícones

E também dar um título para nosso Dashboard:

Figura 77: Inserindo caixa de texto

Para os 3 gráficos vamos adicionar uma dica de ferramenta a fim de ajudar o usuário final em suas análises:

Figura 78: Inserindo dicas de ferramenta

Pronto!!! Com isso temos todos os elementos do nosso Dashboard, que ficou assim:

Figura 79: Dashboard Final

Para assegurar que o relatório vai atualizar corretamente caso a gente faça alguma alteração no nosso banco de dados vamos simular que deletamos uma das planilhas de cadastro de produtos e atualizar nosso projeto:

Figura 80: Cópia do arquivo original de produtos

Figura 81: Excluindo produtos de embalagem

Figura 82: Atualizando projeto

Figura 83: Resultado antes da atualização

Figura 84: Resultado após da atualização (sem Material de Embalagem)

Bom galera, esse foi nosso conteúdo da Live#2 do Workshop #1!!!! Conteúdo super especial desse Workshop que fiz com muito carinho e dedicação para vocês. Espero que tenham aprendido vários conceitos de ETL, DAX e criação de visuais para análise de estoques.

Se tiver alguma dúvida ou sugestão para conteúdo das próximas Lives, deixa aqui um comentário.

Grande abraço,

Leonardo!

COMPARTILHE ESSE POST

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