Portfolio – Diego Cerros

Data Analysis | Data Visualization | Figma | Power BI

458

Power BI Financial Report

Power BI Financial Report

Client Fictional company
Date May 2024

LIKE THIS 458

Objective:

This project aimed to develop a comprehensive, friendly, and visually appealing Power BI dashboard to analyze the finances from a company, providing real-time insights for stakeholders.

Goals:

◾ Track key performance metrics (KPIs) and trends for the company.
◾ Empower stakeholders with real-time data for better decision-making.
◾ Make it as simple and comprehensive as possible so that anyone can understand it.

Activities:

◾ Data Integration: Connected to various data sources like CSV and Excel files to gather transaction information.
◾ Data Cleaning & Transformation: Prepared the data by ensuring accuracy and consistency, making it suitable for analysis.
◾ Optimize the model: Make key DAX measurements that allow us to have all the information needed without loading the model.
◾ Dashboard Design: Utilize Figma to make a powerful dashboard design with a bit of complexity to make it appealing for everyone while not loading the data model with unnecessary Power BI design options.

Key Findings and recommendations:

◾ Review the enterprise category: While analyzing the data, by first hand you can notice that the “Enterprise” Category is only showing losses throughout the whole period filtered.

Actions:

Consider closing this category and focus on boosting the other low profit categories.
Consider keeping only 2 categories, which represent 80% of the company’s income, government, and small business.

◾ Review the cost of goods: it is easy to notice that the profit made is approximately 13% of the gross sales.

Actions:

Theres a lot of opportunities to optimize the cost of good. It can be either by getting better raw material deals or by optimizing the involved departments processes, such as including automations, building up an efficient logistic strategy, reduce waste during manufacture, and more.

◾ Reevaluate the discounts: We can see in the dashboard that the total amount of discounts provided are more than half of the profit made.

Actions:

Review the discount policy and to whom the discounts are applicable.

DAX Formulas used:

Gross Sales = SUMX(Sheet1,Sheet1[Units Sold]*Sheet1[Sale Price])

Profit = SUMX(Sheet1,[Sales]–Sheet1[COGS])

Sales = SUMX(Sheet1,[Gross Sales]–Sheet1[Discounts])

DateTable =

ADDCOLUMNS (

CALENDAR(MINX(‘Sheet1’,‘Sheet1’2024),MAXX(‘Sheet1’,‘Sheet1’2024)),

“DateAsInteger”, FORMAT ( 2024, “YYYYMMDD” ),

“Year”, YEAR ( 2024 ), “MonthNo”, FORMAT ( 2024, “MM” ),

“YearMonthNo”, FORMAT ( 2024, “YYYY/MM” ),

“YearMonth”, FORMAT ( 2024, “YYYY/mmm” ),

“MonthShort”, FORMAT ( 2024, “mmm” ),

“MonthLong”, FORMAT ( 2024, “mmmm” ),

“WeekNo”, WEEKDAY ( 2024 ),

“WeekDay”, FORMAT ( 2024, “dddd” ),

“WeekDayShort”, FORMAT ( 2024, “ddd” ),

“Quarter”, “Q” & FORMAT ( 2024, “Q” ),

“YearQuarter”, FORMAT ( 2024, “YYYY” ) & “/Q” & FORMAT ( 2024, “Q” ))

Screenshots:

Data Model:

Data Analysis | Data Visualization | Figma | Power BI

381

Power BI Space Missions

Power BI Space Missions

Client Fictional Company
Date May 2024

LIKE THIS 381

Objective:

The objective is to provide stakeholders with insights into mission details, success rates, and the usage of space rockets, facilitating informed decision-making and enhancing understanding of past, current, and future space missions.

Goals:

◾ Track key performance metrics (KPIs) and trends for the company.
◾ Empower stakeholders with real-time data for better decision-making.

◾ Make it as simple and comprehensive as possible so that anyone can understand it.

Activities:

◾ Data Integration: Connected to a CSV file to gather Space Missions information.
◾ Data Cleaning & Transformation: Used Power Query to Prepare the data by ensuring accuracy and consistency, making it suitable for analysis.

◾ Optimize the model: Make key DAX measurements that allow us to have all the information needed without loading the model.

◾ Dashboard Design: Utilize Figma to make a beautiful and minimalist dashboard design able to showcase the most important information for the stake holder. It is intended to have an intuitive and friendly user experience. Since it has a fresh and minimalist look, a Q&A visual was added on the filter pane so that the user can get any other analytic that is not shown on the dashboard.

Key Findings and recommendations:

◾ Success rate: It is safe to say that most of the missions performed have been successful.
◾ Prelaunch Failures: The prelaunch failures are less than 1% of the total missions performed, which means that the process involved has been carefully crafted.

DAX Formulas used:

Failure =

CALCULATE(

COUNTA(space_missions[MissionStatus]),

FILTER(space_missions,space_missions[MissionStatus]=“Failure”)

) + 0

Partial Failure =

CALCULATE(

COUNTA(space_missions[MissionStatus]),

FILTER(space_missions,space_missions[MissionStatus]=“Partial Failure”)

) +0

Prelaunch Failure =

CALCULATE(

COUNTA(space_missions[MissionStatus]),

FILTER(space_missions,space_missions[MissionStatus]=“Prelaunch Failure”)

) +0

Success =

CALCULATE(

COUNTA(space_missions[MissionStatus]),

FILTER(space_missions,space_missions[MissionStatus]=“Success”)

) +0

Success Rate = [Success]/COUNTA(space_missions[MissionStatus])

Screenshots:

Data Model:

Data Analysis | Data Visualization | Figma | Power BI

364

Power BI Electronic Retailer

Power BI Electronic Retailer

Client Fictional Company
Date May 2024

LIKE THIS 364

Objective:

This project aims to create a dynamic and user-friendly Power BI dashboard for an electronic retailer, offering in-depth analysis of sales data and customer behavior.

Goals:

◾ Track key performance metrics (KPIs) and trends for the company.
◾ Empower stakeholders with real-time insights for better decision-making.
◾ Make it as simple and comprehensive as possible so that anyone can understand it.
◾ optimize sales strategies, and enhance overall business performance in the competitive electronic retail market.

Activities:

◾ Data Integration: Connected to various CSV and excel files to gather sales and customer behavior information.
◾ Data Cleaning & Transformation: Used Power Query to Prepare the data by ensuring accuracy and consistency, making it suitable for analysis.

◾ Optimize the model: Make key DAX measurements that allow us to have all the information needed without loading the model as well of a more complex formula to connect the exchange rate. It was also added a date table for avoiding many to many relationships between the exchange table and the sales table.

◾ Dashboard Design: Utilize Figma to make an informative dashboard design able to showcase the most important information for the stake holder.

Key Findings and recommendations:

◾ Least sold products: there are quite a few products that were sold only once.

Actions:

Make an in-depth revision of the products that have been sold only once. Removing these items from the inventory can lead to a cost saving in logistics and/or operational processes.

◾ Profit fluctuation over the year: At first glance, it seems that there is an unusual behavior in the profit trend.

Actions:

Check if the reason for this fluctuation is seasonal or because of customer dissatisfaction.

◾ Bost Online Sales: As of now, online sales are the most beneficial for the company, leaving almost half of the profit of the year.

Actions:

Designate resources to strengthen online sales even more.

◾ Customer’s age: Most of the customers range from 23 to 47 years old.

Actions:

Using this dashboard can accurately show what the niche of the marketing campaign should be. It can be filtered by product, area or even customer’s gender. It is crucial to exploit this feature since it will lead to the correct alignment of the customer’s needs and consequently higher sales.

DAX Formulas used:

COGS =

SUMX(

Sales,Sales[Quantity]*

RELATED(Products[Unit Cost USD])*

LOOKUPVALUE(Exchange_Rates[Exchange],

Exchange_Rates[Date], Sales[Order Date].[Date],

Exchange_Rates[Currency],Sales[Currency Code]

))

Gross Sales =

SUMX(

Sales,Sales[Quantity]*

RELATED(Products[Unit Price USD])*

LOOKUPVALUE(Exchange_Rates[Exchange],

Exchange_Rates[Date], Sales[Order Date].[Date],

Exchange_Rates[Currency],Sales[Currency Code]

))

Profit =

SUMX(

Sales,

Sales[Quantity]*

(RELATED(Products[Unit Price USD]) – RELATED(Products[Unit Cost USD]))*

LOOKUPVALUE(Exchange_Rates[Exchange],

Exchange_Rates[Date], Sales[Order Date].[Date],

Exchange_Rates[Currency],Sales[Currency Code]

))

DateTable =

ADDCOLUMNS (

CALENDAR(MINX(‘Sales’,‘Sales'[Delivery Date]),MAXX(‘Sales’,‘Sales'[Delivery Date])),

“DateAsInteger”, FORMAT ( 2024, “YYYYMMDD” ),

“Year”, YEAR ( 2024 ), “MonthNo”, FORMAT ( 2024, “MM” ),

“YearMonthNo”, FORMAT ( 2024, “YYYY/MM” ),

“YearMonth”, FORMAT ( 2024, “YYYY/mmm” ),

“MonthShort”, FORMAT ( 2024, “mmm” ),

“MonthLong”, FORMAT ( 2024, “mmmm” ),

“WeekNo”, WEEKDAY ( 2024 ),

“WeekDay”, FORMAT ( 2024, “dddd” ),

“WeekDayShort”, FORMAT ( 2024, “ddd” ),

“Quarter”, “Q” & FORMAT ( 2024, “Q” ),

“YearQuarter”, FORMAT ( 2024, “YYYY” ) & “/Q” & FORMAT ( 2024, “Q” ))

Screenshots:

Data Model: