Fala pessoal, tudo tranquilo?

Na live da última terça-feira construímos do zero um Dashboard Comercial bem bacana! Você já precisou verificar se uma determinada meta de vendas foi alcançada? Já quis simular cenários e analisar como a variação do valor unitário dos produtos impacta no atingimento dessa meta? Já precisou adicionar novos arquivos na base de dados rapidamente?

A nossa última live mostrou como fazer tudo isso e muito mais! Nesse artigo vou mostrar passo a passo tudo o que foi feito! Vamos percorrer todo o processo de BI: ETL ⇒ Relacionamentos ⇒ Medidas ⇒ Visual.

Clique Aqui para adquirir essa solução completa.

ETL e Relacionamentos

Vamos simular que os dados de Faturamento são extraídos periodicamente de um sistema.

Nossa fonte de dados consiste de 4 arquivos em Excel.

Temos os arquivos: Vendas 2017.xlsx e Vendas 2018.xlsx. Ambos possuem uma aba Vendas e as seguintes colunas: DataEmissao, DataVencimento, Nfe, cdCliente, cdVendedor, cdProduto, QtdItens, Valor Unitário e Peso Líquido.

No nosso caso, estes dois arquivos possuem a mesma estrutura (lembra, que são extraídos de um sistema?!) e o nosso objetivo é conseguirmos adicionar novos arquivos (2019, 2020, etc) rapidamente. Faremos isso daqui a pouco!

Também temos o arquivo: Metas.xlsx. Encontramos nele a aba Metas com as seguintes colunas: cdVendedor, jan/17, mar/17, jun/17, … , Total.

Note que essa tabela está pivotada e precisaremos editá-las no Editor de consultas (Power Query).

Já o nosso arquivo Dimensões.xlsx possui 5 abas:

• Na aba Cliente, encontramos as seguintes colunas: cdCliente, Razão Social, Status, Categoria, Cidade e UF.
• Na aba Produto, temos as colunas: cdProduto, Descrição e cdGrupo.
• Na aba Grupo Produto, as colunas são: cdGrupo, Grupo e Linha.
• A aba Vendedor possui as colunas: cdVendedor, Vendedor, cdSupervisor, cdGerente, Gerente e Equipe.
• Por fim, temos a aba Data com as colunas: Data, Ano, Mês, MesNum e Dia.

Importando os dados

Base Dimensão

Para importar o arquivo Dimensões.xlsx, devemos ir na guia Página Inicial ⇒ Obter Dados ⇒ Excel ⇒ Selecione o arquivo Dimensões.xlsx ⇒ Na caixa de diálogo Navegador, selecione as tabelas dCalendario, dCliente, dProduto, dProdutoGrupo e dVendedor à Transformar Dados.

Note que nessa janela, aparecerá a lista de abas e de tabelas do arquivo. Neste caso as tabelas já estão formatadas e prontas para uso, ou seja, não será necessário editá-las no Editor de Consultas.

Base Faturamento

Como vimos, a base de faturamento é padronizada e para facilitar a importação de novos arquivos (Vendas 2019.xlsx, por exemplo), ao invés de importamos arquivo por arquivo, realizaremos a importação de uma pasta.

Para isso, basta ir em Página Inicial ⇒ Obter Dados ⇒ Pasta. Em seguida copie o caminho da pasta que contém os arquivos da base faturamento (Vendas 2017.xlsx e Vendas 2018.xlsx) e cole na caixa de diálogo que aparecerá em seguida. Após, clique em Ok e Transformar Dados.

Renomearemos essa pasta importada no Editor de Consultas para fVendas. Como todos os arquivos a serem importados estão localizados nessa mesma pasta, deveremos manter apenas os arquivos referentes à base faturamento na lista de arquivos pois, os arquivos Dimensões.xlsx e Metas.xlsx serão importados individualmente. Para isso, no Editor de Consultas, selecione o botão de dropdown da coluna Name na consulta fVendas, e filtre as linhas que começam com “Vendas” e depois clique em Ok.

Após isso, clique no botão da coluna Content para combinar os arquivos. Na caixa de diálogo Combinar Arquivos, digite Vendas e depois clique em Ok. Agora temos toda os dados da base faturamento (arquivos Vendas 2017.xlsx e Vendas 2018.xlsx), um embaixo do outro. Agora só clicar no topo da janela em Fechar e Aplicar.

Clique Aqui para aprender mais sobre Power Query.

Relacionando tabelas

Após arrumarmos as caixinhas de forma que as dimensões (dProdutoGrupo, dProduto, dCliente, dCalendario, dVendedor) fiquem na parte de cima e a tabela fato fVendas fique na parte de baixo, adicionaremos o relacionamento entre e a fVendas e a dCalendario. Como faz isso?! Arraste DataEmissao (da tabela fVendas) até a Data (da tabela dCalendario). Pronto!  Ao clicar na setinha gerada você verá as colunas relacionadas assim (1:*):

Por que o Power BI não conseguiu identificar este relacionamento automaticamente assim como os demais? Bom, provavelmente porque os nomes das colunas relacionadas não são iguais nessas duas tabelas (DataEmissao e Data), mas nas outras tabelas são.

Analisando os relacionamentos, vemos que há relacionamento entre duas tabelas dimensão  (dProdutoGrupo e dProduto). Uma boa prática é criarmos relacionamentos apenas entre tabelas dimensão e tabelas fato. E mesclar as tabelas que sejam do tipo dimensão (ou seja, não criar relacionamento entre duas tabelas dimensão). Para não esquecer, esquematizamos essa dica aqui embaixo:

Mesclando consultas

Para mesclar as consultas dProdutoGrupo e dProduto basta clicar em Página Inicial ⇒ Transformar Dados. Na tabela dProduto, ainda na Página inicial, clique em Mesclar Consultas. Após isso, selecione a coluna cdGrupo (1) da tabela dProduto, escolha a tabela dProdutoGrupo (2) no botão de dropdown  e selecione a cdGrupo (3). No final, clique em Ok (4).

Agora precisaremos expandir as colunas que queremos trazer da tabela dProdutoGrupo para a dProduto (lembra daquele ProcV do Excel?). Clique para expandir e apenas deixe as caixinhas de Grupo e Linha selecionadas (lembre-se que cdProduto já existe na tabela dProduto).

Veja que não precisaremos mais da tabela dProdutoGrupo porém não podemos excluí-la, então vamos desabilitar a carga dela. Basta clicar com botão direito na tabela dProdutoGrupo e desmarcar a caixinha de Habilitar Carga.

Base Metas

Agora, vamos importar o arquivo Metas.xlsx. Vá em Transformar Dados ⇒ Nova Fonte ⇒ Excel ⇒ Selecione o arquivo Metas.xlsx ⇒ Na caixa de diálogo Navegador, selecione a tabela Metas ⇒ Ok.

Observe que há linhas Null, uma linha e uma coluna com Total e também colunas com datas na horizontal. Ou seja, precisaremos arrumar essa tabela, certo?!

O passo a passo será:

• Remover as linhas null

Clique no botão de dropdown (filtro) em Column2 ⇒ Remover vazios ⇒ OK.

• Promover cabeçalho

Clique no canto esquerdo superior e selecione a opção para promover o cabeçalho. Assim, a primeira linha se tornará o cabeçalho das colunas.

• Remover linha Total

 Filtre a coluna cdVendedor e desmarque a caixinha com a linha Total.

• Unpivot das colunas com datas

Muita gente confunde as funcionalidades: Transpor (Transpose) e Transformar Colunas em Linhas (Unpivot). Vamos às diferenças entre elas? Quando usamos o Transpose, estamos transformando tudo que é linha em coluna e tudo que é coluna em linha, na tabela inteira. Quando usamos o Unpivot, estamos transformando apenas algumas colunas em linhas. Nós aplicaremos o Unpivot nesse caso.

Nós temos duas formas de fazer isso. A primeira é selecionar todas as colunas com datas, clicar com o botão direito e escolher a opção Transformar Somente as Colunas Selecionadas em Linhas. A segunda forma é selecionar a única coluna que ficará fixa (cdVendedor) e clicar em Transformar Outras Colunas em Linhas. Faremos da segunda forma por ser mais prático e mais fácil de identificar, beleza?!

Após realizar essa etapa, teremos a coluna cdVendedor (que foi mantida), Atributo e a Valor. A coluna Atributo representa aquelas colunas que tinham datas, mas agora elas estão dispostas nas linhas e a coluna Valor corresponde aos valores das metas. Renomeando a coluna Atributo para Data, e mudando seu tipo para Data (clique com botão direito nessa coluna e selecione Data), teremos o seguinte resultado:

Por fim, clique em Fechar e Aplicar, para aplicar as mudanças que realizamos até agora.

Relacionando a tabela de Metas

Precisamos criar o relacionamento entre a dCalendario e Metas. Basta arrastar o campo Data de uma caixinha para a outra. Também devemos criar um relacionamento entre a tabela dVendedor a tabela Metas (arraste a coluna cdVendendor). O resultado deverá ser este:

Ah, também vamos renomear a tabela Metas para fMetas porque ela é uma tabela fato, certo?! Se quiser, dê uma olhadinha aqui pra refrescar caso não lembre desse assunto.

Relatórios

Agora, vamos inserir uma Tabela chamada _Medidas apenas para organizar as medidas que criaremos. Esse underline na frente é para que ela fique posicionada acima das demais tabelas na lista de Campos. Para fazer isso, vá ícone de Relatório (1) ⇒ Página Inicial (2) ⇒ Inserir Dados (3) ⇒ Digite _Medidas (4) no campo de nome da tabela ⇒ Carregar (5).

Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.

Criando medidas em DAX

Nossa primeira medida em Dax será o cálculo do Peso em toneladas. Como a coluna Peso Líquido da fVendas já está considerando a QtdItens,  só precisaremos dividir o Peso por mil. Clique com o botão direito na tabela _Medidas, selecione Nova Medida e digite:

Peso (ton) = SUM ( fVendas[Peso Líquido] ) / 1000

Agora, precisamos criar uma medida para calcular o faturamento! Lembre-se que nossa tabela fVendas possui apenas uma coluna de QtdItens e ValorUnitario, então precisaremos criar uma medida que nos dê o total do faturamento com base no produto dessas duas colunas. A função que nos ajudará a fazer isso é a SUMX. Tem esse “X” no final porque é uma função iteradora. Lembra o que significa isso? Basicamente essa função vai percorrer cada linha da nossa tabela fVendas e fazer um cálculo específico (no nosso caso será o produto QtdItens x ValorUnitario) e no final da avaliação de todas as linhas é feita a agregação solicitada (nesse caso é a Soma).

Nem adianta tentar fazer esse produto dentro da função SUM porque o Power Bi não vai deixar, ok?! Muito menos fazer a soma da QtdItens e depois soma do ValorUnitario e no final multiplicar essas somas porque não é o correto, beleza?! Nossa medida ficará assim:

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

Bom, nossa próxima missão será contar a quantidade de vendas distintas que ocorreu. Isso será tranquilo porque temos uma coluna com Nfe de cada venda! Veja que o número da Nfe repete em algumas linhas e isso ocorre porque podemos ter mais de um tipo de produto vendido pra cada número de nota fiscal. Então, a fórmula ficará assim:

Qtd Vendas = DISTINCTCOUNT ( fVendas[Nfe] )

E se quiseremos saber o ticket médio por nota fiscal? Simples! Basta dividirmos as duas medidas que acabamos de criar usando a função DIVIDE:

Ticket Médio = DIVIDE ( [Faturamento]; [Qtd Vendas] )

Pra formatar cada medida criada, basta clicar nela, e editar a formatação conforme esquema abaixo:

Nossa próxima medida será o cálculo da Cobertura de Clientes (ou Positivação de Clientes). Mas … O que é isso mesmo?! Basicamente isso é um indicador que identifica o número de clientes positivados, isto é, pra quantos clientes cada vendedor realizou a venda. Então, precisamos novamente usar a função DISTINCTCOUNT:

Cobertura = DISTINCTCOUNT ( fVendas[cdCliente] )

Observação importante: Não utilizamos a tabela cdCliente na fórmula acima porque o objetivo aqui é identificar quantos clientes compraram de fato e não listar quantos clientes existem, beleza?!

Ah, também precisaremos de uma medida com o valor da meta (para compararmos o realizado versus meta), então teremos também a seguinte medida:

Valor Meta = SUM ( fMetas[Valor] )

Agora, criaremos uma matriz para visualizar os valores das medidas criadas. Basta selecionar o visual chamado Matriz e depois arrastar as medidas em Linha (Gerente) e Valores (Peso (ton), Faturamento, Qtd Vendas, Ticket Médio, Cobertura e Valor Meta). A matriz ficará assim:

Figura 16: Matriz

Visual

O Power Bi nos permite importar um template em .json com um background pré-definido e uma paleta de cores. Caso possua um template para importá-lo, vá em Exibição (1) ⇒ Setinha para baixo (2) ⇒ Procurar Temas (3) ⇒ Selecione o arquivo .json.

Cartão

Primeiro criaremos 5 cartões. Vamos utilizar aquele visual chamado Cartões. Dica: formate o primeiro cartão e depois copie e cole 4 vezes para evitar retrabalho de formatá-los um por um um. As medidas utilizadas em cada cartão são: Faturamento, Qtd Vendas, Ticket Médio, Peso (ton) e Cobertura. Para inserir cada campo em um cartão, basta ir no ícone Campos e arrastar a medida desejada.

Vamos adicionar também um slicer para filtrar o ano. Para isso, selecione o visual chamado Segmentação de Dados e arraste a coluna Ano para a região Campos (semelhante ao realizado com Cartões). Lembre-se que para editar a formatação de qualquer visual basta ir em Formato (rolinho de tinta que fica ao lado do botão Campos),beleza?!

Gráfico de colunas e barras empilhadas

Criaremos também um gráfico pra mostrar o faturamento por ano e trimestre. Selecione o gráfico chamado Gráfico de colunas empilhadas e de arraste a Data para o Eixo e Faturamento para Valor nas configurações de Campo.

Para mostrar a cobertura utilizaremos o gráfico chamado Gráfico de barras empilhadas e colocaremos a coluna Grupo no Eixo e Cobertura no Valor. Para facilitar a visualização do usuário, vamos manter o padrão de cores dos cartões no restante dos gráficos (azul para faturamento, verde para cobertura, etc).

Mapa

O último gráfico dessa página será um mapa (se chama Mapa mesmo). Note que se apenas adicionarmos o campo Cidade na Localização, o Power Bi pode não plotar a localização certa no mapa. Então vamos precisar adicionar também o campo UF. Para que cada bolinha do mapa tenha tamanho proporcional ao valor da cobertura, vamos adicionar a medida Cobertura em Tamanho. No final, teremos isso:

Indicador como Tooltip

Agora, vamos criar uma medida para comparar o faturamento versus meta. Não esqueça de mudar a formatação da medida para Porcentagem! A medida ficará assim:

Fat x Meta = DIVIDE ( [Faturamento] ; [Valor Meta] )

Vamos criar também uma página nova para adicionar essa medida no visual Indicador. Essa página será o Tooltip do nosso primeiro gráfico (Faturamento por Ano e Trimestre). O objetivo é que ao passar o mouse sobre as barras desse gráfico apareça o Indicador de atingimento da meta.  Vamos configurá-lo inicialmente assim:

Mas é possível passar a meta, certo? Então agora vamos mudar esse padrão de máximo igual 100%. Para isso , procure por Eixo do Marcador em Formato e configure o Valor mínimo e Valor máximo assim:

Podemos também mudar a cor do gráfico toda vez que o faturamento ultrapassar 100%. Para fazer isso, ainda em Formato, procure por Cores dos Dados, e clique nesse botão em destaque:

Note que esse botão está disponível para versões do Power Bi Desktop depois de Abril/2020.

Depois, basta configurá-lo dessa forma e clicar em Ok:

Vamos voltar para a primira página, e clicar no gráfico de Faturamento por Ano e Trimestre ⇒ Formato ⇒ Dica de Ferramenta ⇒ Selecionar no dropdown Página a opção: Tooltip. Assim, ao passar o mouse sobre as barras do gráfico visualizaremos o Indicador que criamos:

Ah, sempre quando criarmos Tooltip em outra página, lembre-se de ocultar a página para que o usuário não consiga ir até ela de forma manual, afinal, a ideia é ele só aparecer dinamicamente nos gráficos como Tooltip.

Simulador via Parâmetro

Finalmente, chegamos no nosso simulador de cenários! Vamos construir esse simulador utilizando Parâmetros. Insira uma nova página no relatório (“Simulação”). Adicione um parâmetro: Guia Modelagem ⇒ Novo Parâmetro ⇒ Edite as configurações:

Precisamos de uma nova medida para simular o novo faturamento (simulado), que deverá refletir o percentual de aumento ou diminuição nas vendas, veja:

Faturamento Cenário =
SUMX (
    fVendas;
    fVendas[QtdItens] *
   ( fVendas[ValorUnitario]* ( 1 + [VarPrecoUnit Valor] )
)

Para comparar percentualmente o valor original e o valor simulado precisamos também dessa medida:

Faturamento Cenário x Meta = DIVIDE ( [Faturamento Cenário]; [Valor Meta] )

Matriz com formatação condicional

Podemos adicionar uma matriz semelhante à que fizemos antes para comparar mensalmente (ou trimestralmente) o faturamento original e o valor simulado. Vamos também adicionar ícones nessa Matriz usando a formatação condicional. Basta ir em Valor e clicar na setinha do campo Faturamento e seguir os passos a seguir:

A regra será igual àquela aplicada para o Tooltip:

Nossa matriz ficou assim:

Árvore de Decomposição

O próximo visual adicionado será a Árvore de Decomposição. Ele é bem bacana e super fácil de configurar, veja:

O legal desse gráfico é que conseguimos decompor o valor de várias formas clicando no (+). Veja as opções:

Observe que no Power Bi online o usuário pode mudar a visualização que você deixar pré-configurada à vontade!

Lembra daquele gráfico Indicador que criamos para fazer o Tooltip? Podemos adicioná-lo nessa página também ao lado do Parâmetro. Na live adicionamos 2 Indicadores: um com % de Faturamento versus Meta e outro com % Faturamento simulado versus Meta.Ah, lembre-se de configurar a formatação condicional do Indicador de acordo com o valor que você quer trabalhar, ok?!

Bom galera, esse foi o conteúdo da nossa Live #3 do Workshop #2 de Power BI para Negócios! Conseguimos fazer um Dashboard comercial completo em menos de 2h! Espero que tenham gostado e até breve!

Clique aqui para dominar o Power BI e se tornar um profissional valioso para o mercado de trabalho.

Grande abraço,
Leonardo