This project involves the comprehensive analysis of retail sales data for a Dubai-based company to drive data-informed business decisions. The solution was built using Power BI, leveraging Power Query for data transformation, Star Schema for data modeling, and Advanced DAX for calculating complex business metrics.
To convert raw "dirty data" into an analysis-ready format, the following steps were taken:
- Handling Null Values: Removed unnecessary rows where critical information like
CustomerIDorProductIDwas missing. - Data Imputation: Filled missing
UnitPriceandQuantityvalues using mean/average imputation to maintain data integrity. - Text Standardization: Applied
TrimandCleanfunctions to remove erratic spacing and non-printable characters. - Categorical Alignment: Standardized inconsistent entries for 'Gender' and 'Membership' by grouping missing values under 'Unknown'.
- Date Normalization: Unified various date formats (e.g., MM-DD-YYYY, DD/MM/YYYY) into a consistent
DD/MM/YYYYstandard.
The project implements a robust Star Schema to ensure optimal performance and scalability:
- Fact Table:
Sales_Fact(Contains transactional data). - Dimension Tables:
Customers,Products,Geography, andCalendar. - Relationships: Established 1: (One-to-Many)* relationships between dimensions and the fact table to enable seamless filtering.
- Customer Loyalty: A high Retention Rate indicates strong brand loyalty among existing customers.
- Regional Dominance: The Central Region (Dubai) emerged as the primary revenue driver for the business.
- The Profitability Paradox (Crucial Finding): While Electronics leads in total revenue, Furniture and Clothing offer significantly higher Profit Margins.
- Strategic Opportunity (Clothing): Despite having the highest margins, Clothing accounts for less than 20% of total sales volume. There is a massive opportunity to increase overall profitability by focusing marketing efforts on this category.
- Peak Sales Periods: Sales and footfall significantly spike during Weekends, suggesting a need for optimized staffing and targeted weekend promotions.
- Total Revenue:
SUMX(Sales_Fact, [Quantity] * [UnitPrice]) - Total Profit:
[Net Revenue] - [Total Cost] - Retention Rate %:
DIVIDE([Returning Customers], [Total Customers], 0) - Profit Margin %:
DIVIDE([Total Profit], [Net Revenue], 0)
- Download the
.pbixfile included in this repository. - Open it using Power BI Desktop.
- Interact with the Slicers to filter data by Year, Month, or City to explore specific trends.
Inamul Hasan Junior Data Analyst | Reporting Analyst