This challenge consists in develop skills in Business Intelligence and put in practice all obtained knowledge in Power BI. The challenge will take 4 weeks divided in 3 different projects with different levels of complexity:
- Week 01: Logistics Challenge Dashboard (AluraLog)
- Week 02: Coming Soon…
- Week 03 and 04: Coming Soon…
The Alura Log manager is facing some work changes due to the high demand of logistics services during the pandemic time. She wants to keep the quality of her service and she needs constantly follow up the KPI’s to take correct decisions. To help her in this analysis a logistics dashboard was created with the following information provided by the customer dataset.
✔ How many customers purchases was delivered on time?
A Card was created to represent this number including a tooltip that shows which kind of vehicle has more on time deliveries.
✔ How many customers purchase was not delivered on time ?
Also a card was created and tooltip to show which vehicle is having more problems to delivery products on time, this can be a good analysis to understand if late deliveries have any relationship with the kind of transport.
✔ Number of available vehicles
A card was created to show the total of available cars and a tooltip showing which type of cars are available in a pie chart.
✔ Calculate S2D (Ship to Door) median by month
Using a DATEDIFF and analysing the days from when the purchase was done by the Customer and when it was delivered I could find the S2D values.
A tooltip was create to show the details by year
✔ Show how many occurrences by state
A Map was created using the longitude and latitude information
Bellow you can see some of the measures I have created to develop the Power BI dashboard.
| MEASURE | FORMULA |
| % Acum Categorias | % Acum Categorias = [Soma Acum Categorias] / CALCULATE([Soma de Vendas], ALL('Tabela - Produtos')) |
| Atrasados | Atrasados = CALCULATE([Total de Pedidos], FILTER('Tabela - Pedidos', 'Tabela - Pedidos'[Data de Entrega] > 'Tabela - Pedidos'[Data Previsão])) |
| Em Transito | Em Transito = CALCULATE([Total de Pedidos], FILTER('Tabela - Pedidos', 'Tabela - Pedidos'[Status do Pedido] = "Em Transito")) |
| Entregues | Entregues = CALCULATE([Total de Pedidos], FILTER('Tabela - Pedidos', 'Tabela - Pedidos'[Status do Pedido] = "Entregue")) |
| No Prazo | No Prazo = CALCULATE([Total de Pedidos], FILTER('Tabela - Pedidos', 'Tabela - Pedidos'[Data de Entrega] <= 'Tabela - Pedidos'[Data Previsão])) |
| Qtd Produtos Vendidos | Qtd Produtos Vendidos = SUM('Tabela - Pedidos'[Quantidade]) |
| Rank de Categoria | Rank de Categoria = RANKX(ALLSELECTED('Tabela - Produtos'), [Soma de Vendas]) |
| Soma Acum Categorias | Soma Acum Categorias = CALCULATE([Soma de Vendas], TOPN([Rank de Categoria], ALL('Tabela - Produtos'),[Soma de Vendas],DESC)) |
| Soma de Vendas | Soma de Vendas = SUMX('Tabela - Pedidos', 'Tabela - Pedidos'[Quantidade] * RELATED('Tabela - Produtos'[Preço])) |
| Total de Pedidos | Total de Pedidos = COUNT('Tabela - Pedidos'[ID Pedido]) |
| Veiculos Disponiveis | Veiculos Disponiveis = CALCULATE(COUNT('Tabela - Veículos'[ID Veiculos]), FILTER('Tabela - Veículos', 'Tabela - Veículos'[Status] = "Disponivel")) |
| Veiculos Ocupados | Veiculos Ocupados = CALCULATE(COUNT('Tabela - Veículos'[ID Veiculos]), FILTER('Tabela - Veículos', 'Tabela - Veículos'[Status] = "Ocupado")) |
An extra dashboard was created showing a Pareto table used to show accumulative gains by a rank of the most sold product to the less one. In the same page a bar chart showing the category with more sold items based on quantity.
A Card also shows the total sales and total of itens sold with a filter by year and month that the user can select when necessary.
All the ETL process was done in Power Query using the main functions such as : replace values, divide columns by delimiter, change columns type, rename columns, remove empty cells with no relevant information, cleaning spaces, deleting no relevant information. In this process 4 CSV tables was refined as shows bellow:
Some relashionships was done between tables, such as the image bellow:
