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: Marketing Dashboard (AluraShop)
- Week 03 and 04: Financial Challenge Dashboard (AluraStore)
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.
Take a look in the online dashboard: https://bit.ly/3F6UYKL
✔ 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:
The Alura Shop have invested in publicity to get more visibility into the market and now its managers needs to know if the investment was really effective.
Take a look in the online dashboard: https://bit.ly/2Y31t0k
My mission was elaborate an strategic marketing dashboard with the main purpose to monitor the campaign applied in July 2021. Some metrics was presented to the manager as you can see below:
✔ Calculate the total of sales
In this card I am also using a line chart representing the total of sales in days. The column “Compras” from “Idade e Gênero” table was used to show the result.
✔ Calculate Purchases Conversion value
The column “” from “Idade e Gênero” table was used to show the result just by selecting formatting as money.
✔ Show all invested money during the campaign
In this card I am also using a line chart representing the total invested money in days. The column “quantia_gasta_brl” from “Idade e Gênero” table was used to show the result.
✔ Calculate cost by click
A measure was created to show this value and it was formatted to be displayed as money (BRL):
DAX Formula: Custo por clique = SUM(Dispositivos[quantia_gasta_brl]) / SUM(Dispositivos[cliques_em_links])
✔ Show the purchasing user journey
The purchasing user journey was done by selecting page visualization, added products to the cart, initiated checkouts and sum of the products that was in fact bought by the Customer.
✔ Conversion Tax calculation
Here we need to show our management the tax of customers who accessed the website and in fact have bought something.
This calculation was done by dividing purchasing quantity with page visualization.
DAX Formula: Taxa de Conversão de Compras = SUM('Idade e Gênero'[compras]) / SUM('Idade e Gênero'[visualizacoes_por_pagina])
✔ Show the occurrences by devices
The purpose of this chart is to show the average value of sales by device .
DAX Formula: Ticket Médio Por Dispositivo = SUM(Dispositivos[quantia_gasta_brl]) / SUM(Dispositivos[compras])
✔ Calculate ROAS (Return On Investment)
This chart shows the amount of money the company earned with users separated by age and genre.
DAX Formula: ROAS = SUM('Idade e Gênero'[valor_de_conversao_de_compras]) / SUM('Idade e Gênero'[quantia_gasta_brl])
✔ Calculate purchasing value by day
This value can be show by the sum of the column “valor_de_conversao_de_compras” from table “Idade Gênero”.
✔ Show the last time data was updated
An extra table was created with the name “Atualização” where you can find two columns named “Data e Hora” and “Data e Hora Certa” where the first one shows the time of your computer location and the second one shows the time based on the local time you choose, this is important because on Power BI service maybe the time server is not the same of your computer. For this, the following M formulas was used:
M Formula for “Data e Hora” Column: DateTime.LocalNow()
M Formula for “Data e Hora Certa” Column : DateTimeZone.SwitchZone([Data e Hora], -3)
As requested by the user, the data refresh is scheduled to everyday at 9am, configured in Power BI Service.
✔ Some additional information was added to the report, as a word cloud chart with the platforms with more colocation.
The total of clicks was added in a card with a line chart showing the clicks during the month.
✔ The ETL (Extract Transform and Load) process was done in python, using basically pandas librarie and then inserting all data in a PostgreSQL data base.
Pandas Code is located in the links bellow using Jupyter Notebook:
https://github.com/flaviolandim/alurachallengebi/blob/master/Week02/AluraShop_IdadeGenero.ipynb
https://github.com/flaviolandim/alurachallengebi/blob/master/Week02/AluraShop_Dispositivos-V2.ipynb
In this dashboard my mission was to develop a financial report with two pages in which the first one will show an overview of all financial area and the second one will focus on a scenario analysis.
Take a look in the online dashboard: https://bit.ly/3zOdoMA
The customer asked to show the following information in the dashboard:
✔ Calculate de company gain
To calculate this information it was necessary to create another 3 measures Revenue / Total Production Cost / Expenses and result would be like that:
Lucro = [Receita] - [Custos total de produção] - [Despesas]
✔ Show the Total Production Costs
For this data it was necessary to multiply the number or requests by the individual costs, using a SUMX function, considering a line to line calculation:
Custos total de produção = SUMX(Pedidos, Pedidos[quantidade] * RELATED(Produtos[Custos]))
✔ Show the expense Information
To show this value the it was used the SUMX function and sum the taxes with shipment:
Despesas = SUMX('Notas Fiscais', 'Notas Fiscais'[imposto] + 'Notas Fiscais'[Frete])
✔ Show the revenue
Revenue was calculates also using SUMX and RELATED function to multiply requests quantity by its costs:
Custos total de produção = SUMX(Pedidos, Pedidos[quantidade] * RELATED(Produtos[Custos]))
✔ Filter by year
✔ The gain was compared with product salle and also exhibited in a individual cascade chart.
✔ A rank of the best sallers was created
✔ Variables was created to simulate some scenarios
