MasterLive Dashboard de People Analytics (RH) do ZERO - Power BI Experience
MasterLive Dashboard de People Analytics (RH) do ZERO
Hoje vamos montar um dashboard de Recursos de humanos. Esse em especial, foi desenvolvido pelo Flávio Leopoldo durante um desafio proposto por mim aos alunos do Power BI Experience.
O Desafio People Analytics proposto para aos alunos na nossa comunidade exclusiva, teve como objetivo realizar as seguintes análises:
Contratações, Demissões, Headcount (funcionários ativos), Turnover Demissional, Massa Salarial;
No cálculo de Turnover Demissional, divida a quantidade de Demissões no período atual pelo Headcount no período anterior;
Calcular a quantidade e o percentual de má contratações, considerando que uma má contratação é quando um funcionário é demitido com menos de 60 dias de contratação;
Segmentar o tempo de retenção de funcionários em 3 categorias: "Menor que 60 dias", "Entre 60 e 365 dias" e "Maior que 365 dias";
Avaliar o que influencia a retenção de funcionários por mais tempo;
Segmentar os afastamentos em Ativo (códigos 3, 4 e 14), Passivo (códigos 1, 2 e 13) e Espontâneo (demais códigos) e calcular o Turnover para essas divisões;
Realizar essas análises ao longo do tempo, por cargo, faixa etária, sexo, escolaridade.
Análise da Ficha Financeira
Calcular os valores referentes à Faltas (eventos 3 e 4), Atrasos (evento 2457) e Atestados (eventos 14 e 113). Esses valores somados dão origem ao KPI de Absenteísmo;
Calcular o percentual de Absenteísmo, dividindo a soma dos valores acima pelos valores gastos com Horas Normais (eventos 1 e 100);
Calcular o valor gasto com Horas Extras (eventos 257, 261, 264, 266 e 400) e o seu percentual em relação às Horas Normais;
Dashboard do Flávio
Com base nisso, o Flávio (vencedor do desafio), criou um dashboard segmentando as análises da seguinte forma:
Análise de Funcionários: onde temos as contratações, desligamentos, funcionários ativos, headcount e más contratações. O grande barato dessa página são os filtros dos gráficos, quando eu clico por exemplo em Contratações ele muda as análises dos gráficos para contratações, ou seja, um filtro de medida dinâmico para trocar a análise, que será feito utilizando o Tabular Editor.
Figura 02: Análise de Funcionários
Análise de Absenteísmo: ao longo do tempo, por faixa etária, por gênero.
Figura 03 – Análise Absenteísmo
Comparativo de Horas Extras
Figura 04 – Comparativo Horas Extras
A base de dados de disponibilizada continha os seguintes arquivos:
Tabela de funcionários: que foi extraída do sistema contendo código do funcionário (matrícula), nome, sexo, data de admissão, cargo atual, a data da última mudança do cargo, escala, centro de custo, estado civil, escolaridade, data de nascimento, nacionalidade, raça, valor do salário, data do último salário, situação atual (demitido, férias, trabalhando etc.), data da demissão, causa da demissão.
Figura 05 – Tabela Funcionários
Ficha financeira: contendo informações referente aos pagamentos, ou seja, folha salarial e tudo mais. Foram extraídos do sistema os movimentos de janeiro a junho do ano de 2020, com a estrutura das colunas idênticas mudando apenas o ano e o mês. Neste arquivo temos as seguintes colunas: matrícula (código do funcionário), cargo, mês de pagamento, evento, descrição, tipo e valor pago.
Figura 06 – Ficha Financeira
Você sabe o que são os eventos de uma folha salarial?
Evento é o que escreve se é salário normal, férias, ausência, imposto etc.
Importando e Tratando os Dados
Com o Power BI desktop aberto:
Vá em obter dados
Conectar, em seguida selecione a pasta onde está salva a base de dados
Figura 07 – Obtendo Dados
Clique em transformar dados.
Figura 08 – Transformar Dados
Crie uma referência para o arquivo funcionários, e outra para a ficha financeira:
Clique com o botão direito em BaseDados
Nomeie a nova consulta como Funcionários
Repita os passos 1 e 2, e nomeie a nova consulta como Ficha Financeira
Em seguida, clique com o botão direito em cima de BaseDados e desmarque a opção Habilitar carga.
Desabilite a carga também das outras duas consultas, Ficha Financeira e Funcionários.
Figura 09 – Referências da Base de Dados
Expandindo a tabela de Funcionários
Com a tabela Funcionários selecionada, clique em Binary, em seguida clique em Table na linha da planilha 1. Pronto, a tabela de funcionários já está expandida.
Figura 10 – Expandindo a Tabela Funcionários
Clique em usar a primeira linha como cabeçalho.
Figura 11 – Primeira Linha como Cabeçalho
Crie 02 referências a partir da tabela Funcionários, nomeie a primeira como dFuncionarios e a segunda como fContratos. Deve ficar assim:
Figura 12 – Referência da Tabela Funcionários
Tratando a tabela de dFuncionarios
Na tabela dFuncionarios:
Vá em escolher colunas
Selecione as colunas: Cód Funcionario, Data Nascimento, Escolaridade, Estado Civil, Funcionario, Nacionalidade, Raça e Sexo
Figura 13 – Escolher colunas
Clique com o botão direito em cima do código do funcionário e remova duplicatas.
Figura 14 – Remover Duplicatas
Na coluna de Data Nascimento, coloque o tipo de data. Viu que tem vários erros? Esse cenário é muito comum nas empresas não é mesmo, os dados nunca vêm perfeitos.
Figura 15 – Tipagem da data
Nesse caso, esses erros serão tratados como branco, mas o melhor cenário seria a correção do dado na fonte. Clique com o botão direito em cima da coluna Data Nascimento, e escolha a opção substituir erros.
Figura 16 – Substituir Erros
Analise as datas… Veja que há valores bem estranhos!
Figura 17 – Datas com erro
Nesse caso pode-se deduzir que o valor de 21/04/2982 por exemplo, é 21/04/1982. Porém deduzir não é melhor cenário, pois o dado pode ficar errado.
Então, crie uma condicional onde a data de nascimento não poderá ser menor que 1/1/1920 e nem maior que 1/1/2006.
= if [Data Nascimento] is null  or [Data Nascimento] < #date(1920,1,1)  or [Data Nascimento] > #date(2006,1,1)  then null else [Data Nascimento]
Dica: Estamos trabalhando com linguagem M neste cálculo, a linguagem M é case sensitive portanto, letras maiúsculas e minúsculas importam, e muito.
Figura 19 – Data de nascimento personalizada
Clicando com o direito em cima de Data Nascimento, escolha a opção remover
Renomeie a coluna personalizada que você acabou de criar como Data Nascimento e coloque o tipo como Data.
Note que os valores estranhos sumiram.
Figura 20 – Data sem valores estranhos
Trate a coluna de Sexo, trocando M por Masculino e F por Feminino.
Clique com o botão direito em cima do M e escolha a opção substituir valores, e escreva Masculino. Depois repita o processo para o sexo F.
Figura 21 – Ajuste da coluna sexo
Crie um range por faixa etária, para isso primeiro é necessário criar uma coluna com a idade de cada funcionário:
Clique na coluna Data Nascimento
Figura 22 – Calculando idade em dias
Isso irá trazer a idade em dias, e para transformar em anos, com a coluna Idade que acabou de criar selecionada:
Clique na guia Transformar
Figura 23 – Transformando idade em anos
Com a informação da idade em anos, já é possível criar o range utilizando uma coluna condicional. Na guia adicionar coluna, selecione coluna condicional, e preencha os campos conforme abaixo:
Idade for igual a null então N/A
Idade for menor que ou igual 24 então 15 a 24
Idade for menor que ou igual 34 então 25 a 34
Idade for menor que ou igual 44 então 35 a 44
Idade for menor que ou igual 54 então 45 a 54
Senão 55+
Figura 24 – Condicional do range
Veja que o valor 55 + não ficou como esperado, então altere na barra de fórmulas, e aproveitar para colocar o tipo como texto.
Figura 25 – Ajuste texto range 55+
Range de idade criado, agora você pode remover a coluna de idade, pois não precisamos mais dela.
Tratando a tabela de fContratos
O tratamento aqui será um pouco mais chatinho, pois será necessário sumarizar a coluna de cargos onde há vários tipos de ajudante, vários tipos de analista etc.
Primeiro selecione apenas as colunas que iremos utilizar. Com a tabela fContratos selecionada, vá em escolher colunas e marque a opções:
– Cód Causa Afastamento
– Cód Funcionário
– Cód Situação
– Cód T. Contrato
– Data Admissão
– Data Afastamento
– Data Cargo
– Data Salário
– Valor Salário
Com a coluna cargo selecionada:
Clique em adicionar colunas
Texto antes do delimitador, e o delimitador é espaço.
Figura 26 – Ajuste dos cargos
Na barra de fórmulas, troque "Texto Antes do Delimitador" por "Cargo Resumido".
Figura 27 – Renomeando a coluna de ajuste de cargos
Na coluna Data Afastamento, coloque tipo Data e depois substitua os erros (datas com 00/00/0000) por null.
Agora crie a segmentação dos afastamentos:
3,4,14 – Ativo
1,2,13 – Passivo
Demais Códigos – Espontâneo
Você sabe o que quer dizer cada um desses afastamentos?
Ativo é quando o funcionário pede demissão
Passivo é quando o funcionário é demitido
Espontâneo é o afastamento por morte, entre outros
A segmentação poderia ser feita no DAX, mas criar através de coluna condicional no Power Query possibilita o uso nos filtros e deixando o relatório mais dinâmico.
Na guia adicionar colunas, clique em coluna condicional, e faça a seguinte configuração:
Figura 28 – Condicional de afastamento
Coloque essa coluna com o tipo Texto.
Ufa, a primeira parte de transformações já foi, próxima etapa é tratar as fichas financeiras.
Tabela Ficha Financeira
Selecione a tabela Ficha Financeira no Power Query
Use o filtro para manter apenas os arquivos relativos à ficha financeira
Clique na área destacada na coluna Content para combinar os arquivos
Figura 29 – Tratando a ficha financeira
Lembrando que para usar esse recurso de combinar é necessário que todos os arquivos tenham a mesma estrutura, OK?
Sabe o que é o mais legal desse recurso? É que você pode colocar as fichas financeiras em uma pasta somente delas e quando entrar o mês 7, por exemplo, basta adicionar nessa pasta sem precisar refazer as transformações.
Remova a coluna Nome da Origem dessa tabela.
Na Coluna Descrição, vá no filtro, em filtros de texto, escolha a opção "Não Contém", Médi.
Figura 30 – Filtrando a descrição
Nas colunas Evento e Matrícula, clique no filtro e peça para remover vazio.
Agora a partir da ficha financeira, clique com o botão direito e crie 02 novas referências: dEvento e fFinanceiro.
Tratando a fFinanceiro
Remova as colunas de Descrição e Tipo. A tabela deve ficar conforme a figura abaixo:
Figura 31 – Tabela fFinanceiro
Tratando a dEvento
Com a tabela dEvento selecionada:
Mantenha apenas as colunas Evento, Descrição, Tipo
Na coluna Evento, clique no filtro e peça para remover vazio
Remova as duplicatas da coluna Evento
Agora crie uma coluna condicional para segmentar os eventos:
Clique em Adicionar Coluna
Nomeie como Categoria
Faça as configurações conforme demonstrado na figura 32
Configure o último argumento da Condicional
Defina o tipo dessa nova coluna como texto
Figura 32 – Condicional evento
Tabela dEvento finalizada, pode clicar em fechar e aplicar.
Figura 33 – Tabela dEvento
Antes de iniciar a modelagem, crie a tabela de datas para poder analisar os dados ao longo do tempo.
Nesse caso aqui eu vou utilizar um código que já está pronto.
Em Modelagem → Nova Tabela →Cole o código que está logo abaixo
Figura 34 – Inserir nova tabela
VAR vAnoMin =
YEAR ( MIN ( fContratos[Data Admissão] ) )
VAR vAnoMax =
YEAR ( MAX ( fContratos[Data Afastamento] ) )
DATE ( vAnoMin; 01; 01 )
DATE ( vAnoMax; 12; 31 )
CALENDAR ( vDataInicial; vDataFinal );
"Ano"; YEAR ( [Date] );
"Mês Num"; MONTH ( [Date] );
"Mês"; FORMAT ( [Date]; "mmmm" );
"Dia"; DAY ( [Date] )
Sempre lembre de classificar a coluna de mês utilizando um índice numérico, para que os meses fiquem na ordem certinha.
Clique em cima da coluna mês da tabela calendário
Na barra de ferramentas, clique em Classificar por coluna
Escolha a opção mês num
Figura 35 – Classificando o mês
Agora sim, os dados estão prontos para a modelagem.
Os relacionamentos devem ficar assim:
Figura 36 – Relacionamentos
Tá perdido?! 😲 Calma que vou te explicar. Para criar os relacionamentos entre as tabelas basta clicar no campo e arrastar para a tabela correspondente.
Relacionamento com a tabela fContratos
Arraste o Cód Funcionário (tabela dFuncionario) na direção de Cód Funcionário (tabela fContratos).
Cardinalidade: 1 para Muitos.
Direção do filtro cruzado: único.
Arraste a Data (tabela dCalendario) na direção de Data Admissão (tabela fContratos).
Relacionamento: Inativo
Arraste a Data (tabela dCalendario) na direção de Data Afastamento (tabela fContratos).
Relacionamentos com a tabela fFinanceiro
Arraste o Cód Funcionário (tabela dFuncionario) na direção de Matrícula (tabela fFinanceiro).
Arraste a Data (tabela dCalendario) na direção de Mês Pag. (tabela fFinanceiro).
Arraste o Evento (tabela dEvento) na direção de Evento (tabela fFinanceiro).
Se você quiser conferir se os relacionamentos ficaram certos.
Clique em modelagem
Gerenciar relações
Editar, caso seja necessária alguma alteração.
Figura 37 – Gerenciando relações
Antes de começarmos, crie uma tabelinha de medidas para armazenar e organizar os cálculos.
Você pode fazer isso seguindo os passos abaixo:
Clique na aba relatório
Dê à tabela o nome de Medidas
Figura 38 – Criando tabela de medidas
Sempre que for criar uma nova medida, clique com o botão direito em cima da tabela de medidas, em seguida escolha a opção Nova Medida.
Figura 39 – Nova medida
Cálculos da fFuncionários
A seguir você verá uma breve explicação sobre o cálculo a ser realizado, e em seguida tem a fórmula DAX, é só clicar em nova medida, e copiar e colar a fórmula, Ok 😉.
Contratações: cada linha da tabela de contratos é uma contratação. Lembre-se que existe um relacionamento inativo entre a fContratos e a dCalendario, dessa forma quando for analisar esse valor considerando o tempo, vai dar ruim né.
Utilize a função USERELATIONSHIP para ativar esse relacionamento.
Contratações =
COUNTROWS ( fContratos );
USERELATIONSHIP ( dCalendario[Data]; fContratos[Data Admissão] )
Demissões: as demissões são identificadas pelo código da situação igual a 7. E nesse caso aqui também é necessário ativar via DAX o relacionamento
entre dCalendario e fContratos.
USERELATIONSHIP ( dCalendario[Data]; fContratos[Data Afastamento] )
Headcount: é a quantidade de funcionários ativos no período, ou seja, deve-se levar em conta a data de afastamento e a data de admissão.
VAR vDataFinalContexto =
fContratos;
fContratos[Data Admissão] <= vDataFinalContexto
&& ( fContratos[Data Afastamento] > vDataFinalContexto
|| fContratos[Cód Situação] <> 7 )
Massa Salarial: é o salário dos funcionários ativos.
Massa Salarial =
//Somente para ativos!!!
SUM ( fContratos[Valor Salário] );
Turnover: é medido pela divisão quantidade de demissões no período atual pelo do headcount do período anterior.
% Turnover =
VAR vHCAnterior =
PREVIOUSDAY ( dCalendario[Data] )
VAR vHCNonBlank =
vHCAnterior;
[Demissões];
vHCNonBlank
Mas, e essa tal de COALESCE que eu nunca vi?!
Essa função nesse caso está sendo usada para tratar valores em branco, então, quando o Headcount anterior for branco, o valor retornado
será o Headcount atual.
Má Contratações: funcionários demitidos antes com até 60 dias.
Má contratações =
[Contratações];
fContratos[Data Admissão];
fContratos[Data Afastamento];
) < 60
&& fContratos[Data Afastamento]
Percentual de Má Contratações: é só dividir a quantidade de má contratações pelas contratações.
% Má Contratações =
[Má Contratações];
[Contratações]
Segmentação da Retenção:
1 . Clique na aba dados
2 . Clique na tabela fContratos
3 . Escolha a opção Nova Coluna
4 . Crie a medida destacada, copiando e colando a fórmula abaixo, e dê enter
Figura 40 – Segmentando a retenção
Retenção (dias) =
VAR vDataInicial = fContratos[Data Admissão]
VAR vQtdDias =
vDataInicial;
vDataFinal;
vQtdDias < 60; "Menor que 60";
vQtdDias < 366; "Entre 60 e 365";
"Maior que 365"
Turnover do ano máximo: para utilizarmos no visual de KPI
% Turnover Ano Max =
MAX ( dCalendario[Ano] )
[% Turnover];
dCalendario[Ano] = vAnoMax
Cálculos da fFinanceiro
Horas Normais =
SUM ( fFinanceiro[ Valor] );
dEvento[Categoria] = "Normais"
Horas Extras =
dEvento[Categoria] = "Extras"
% Horas Extras =
DIVIDE ( [Horas Extras]; [Horas Normais] )
Absenteísmo =
KEEPFILTERS ( dEvento[Categoria] IN { "Faltas"; "Atrasos"; "Atestados" } )
% Absenteísmo =
[Absenteísmo];
[Horas Normais];
Criando os Visuais
Aqui estamos utilizando o background que o Flávio disponibilizou.
Léo, como eu crio esse background? Você pode usar o bom e sempre atual Power Point.
Figura 41 – Background em Power Point
Mas e depois de criar? É só salvar os slides em formato SVG e importar no Power BI.
Como fazer a importação no Power BI:
Clique em Formato
Em adicionar imagem, escolha o arquivo
Ajuste a transparência para 0%
Em ajuste de imagem, escolha a opção ajuste
Figura 42 – Importando o background
Antes de criar os primeiros visuais, veja abaixo uma visão geral sobre configuração de visuais:
Figura 44 – Conhecendo o painel de visualização
Visuais padrão do Power BI. Se você clicar nos 3 pontinhos ao final da área demarcada, você abre o marketplace da Microsoft de onde você pode importar visuais diferentes.
Nessa área você colocar os eixos do gráfico (atributos descritivos) que você irá analisar, por exemplo, sexo, idade, escolaridade.
Nessa área você coloca os valores (medidas).
O rolinho expande a área de configuração de cores, tamanho de texto etc.
Área de configuração de cores, tamanhos etc.
Agora que você já sabe como criar background e importar no Power BI, e conhece o painel de visualização, pode ir para os próximos passos:
Para configurar o botão de navegação, Iniciar:
Vá em inserir
Botões -> em branco
Arraste o botão em branco para cima da imagem do botão acessar
Escolha o tipo: Navegação da Página
Destino do botão: funcionários
Pulo do Gato: para testar se deu certo, Crtl + clique com o mouse!!!
Figura 43 – Configurando o botão acessar
Página de Funcionários:
A página deverá conter todos os elementos numerados da figura 45, utilize a numeração para se orientar quanto a configuração.
Figura 45 – Página funcionários
Dê uma olhada na tabela abaixo e veja de acordo com a numeração, qual visual foi utilizado, e suas configurações de eixo e valor. As cores você pode escolher de acordo com suas preferências:
Figura 46 – Tabela de configuração dos visuais
Para configurar a seleção de medidas automática, item 14, é necessário instalar o tabular editor. Você pode baixá-lo aqui: link para dowload.
Depois de instalado, ele fica aqui.
Figura 47 – Localizando o tabular editor no Power BI
Com o seu arquivo pbix aberto:
Clique em Tabular Editor → Tables → Create New → Calculation Group → Nomeie como SelecaoMedidas.
Renomeie o campo Name para Filtro.
Figura 48 – Primeiros passos no tabular
Dentro de Calculation Items, colocaremos os itens que serão filtrados.
Clique em cima de Calculation Items > New Calculation Item
Crie um calculation item para cada medida que vamos utilizar, contratações, demissões, headcount, massa salarial, % turnover.
Figura 49 – Criando os Calculation item
Para configurar cada cálculo, siga os passos abaixo:
Com o item calculado selecionado
Expanda a tabela de medidas
Visualize a medida que você precisa, nesse caso contratações, e arraste para o campo indicado pela seta.
Repita o processo para os Calculation Items (Demissões, Headcount, Massa Salarial, % Turnover)
Figura 50 – Configurando os Calculation item
Ainda no tabular Editor:
Clique na tabela SelecaoMedidas → Create New → Measure → Nomeie como Métrica Selecionada
Figura 51 – Nova medida no Tabular Editor
Agora, crie a medida SELECTEDMEASURE () na área apontada pela seta.
Figura 52 – Criando a SELECTEDMEASURE
Clique em salvar no Tabular Editor.
A tabela Seleção Medidas criada no tabular deve aparecer no Power BI, e agora você pode pegar o campo filtro dessa tabela e colocar no campo de configuração do segmentador, item 14.
Importante: para que esse visual fique igual ao da foto, clique no rolinho de configurações e coloque a orientação como Horizontal.
Figura 53 – Configuração do item 14
Para configurar a ordem dos campos no item 14, no local apontado pela seta na figura 54, ordene conforme abaixo:
1 Demissões
3 Massa Salarial
4 % Turnover
Figura 54 – Ordenando as medidas
Agora para configurar o formato, no local apontado pela seta, faça as seguintes configurações:
"R$ 0.00" Massa Salarial
"0.0%" Turnover
Figura 55 – Configurando o formato das medidas
Agora edite as interações do item 14 com os outros visuais para que ele mude os valores apenas dos gráficos abaixo dele.
Clique no visual de segmentação
Marque a opção nenhum
Marque a opção nenhum também nos cartões, conforme destacado
Figura 56 – Editando interações entre os visuais
Clique na guia exibição
Ative o Painel de Indicadores e o de Seleção
Crie o indicador: Funcionários
Figura 57 – Exibindo os painéis de seleção e indicadores
Clicando na guia inserir, crie uma forma retângulo e cubra toda a área, nesse exemplo o retângulo está na cor azul com transparência 28%, mas
você pode fazer como achar melhor.
Crie mais uma forma retangular que é onde colocaremos o menu
Figura 58 – Retângulos inseridos
Dentro do retângulo branco, insira imagens para representar os botões
Figura 59 – Inserindo ícones
Insira uma caixa de texto do lado de cada ícone, conforme mostrado abaixo
Ícone 01 – Texto: Funcionário
Ícone 02 – Texto: Absenteísmo
Ícone 03 – Texto: Horas Extras
Figura 60 – Inserindo caixas de texto
8. Clique em Botões
Insira o Botão de Voltar
Configure a ação dos botões:
Exemplo: Absenteísmo. Tipo: Navegação na página. Destino: Absenteísmo.
Demais botões:
Funcionários. Tipo: Navegação na página. Destino: Nenhum.
Horas Extras. Tipo: Navegação na página. Destino: Horas Extras.
Voltar. Tipo: Indicador. Indicador: Funcionários.
Figura 61 – Configurando a ação dos botões
11.  Selecione os retângulos, botões e textos criados, e no painel de seleção, clique com o botão direito para agrupar, nomeie como Menu Pg1
Figura 62 – Agrupando os itens do menu
12. Crie o um novo indicador, e nomeie como Menu Pg1
No painel de seleção oculte o Menu Pg1
Clique com botão direito no indicador de funcionários e peça para atualizar
Figura 63 – Atualizando indicador
15. Tire o ocultar do Menu Pg 1 → clique com o botão direito em cima do indicador Menu Pg1 e atualize também.
Insira um botão em branco e coloque em cima do menu, a ação deve ser: tipo (indicador), Indicador (Menu).
Figura 65 – Botão do menu
Agora é por sua conta, os demais elementos da página funcionários você já pode inserir sozinho.
Gráfico do tipo sparkline que você pode importar da loja da microsoft
São filtros do tipo segmentação
Figura 64 – Demais elementos da página funcionários
Só a prática nos torna bons, por isso como tarefa, vou deixar para você criar as páginas de absenteísmo e horas extras, aproveite e acrescente outras análises que você achar necessário.