Global Super Store Analysis using Power BI

Introduction

In today’s data-driven world, the ability to analyse and visualize data effectively is a critical skill. To showcase my proficiency in this area, I undertook a Power BI project titled “Global Super Store Analysis.” This project involves several stages, including ETL (Extract, Transform, Load), data modelling, DAX calculations, and data visualization. This blog will walk you through each stage, detailing the steps and methodologies I employed to create a dynamic and insightful Power BI report.

ETL (Extract, Transform, Load)

The first step in any data analysis project is the ETL process. This stage involves extracting data from various sources, transforming it to fit the desired format, and loading it into the data model.

Key Tasks in ETL:

1. Unnecessary Columns: I began by identifying and removing unnecessary columns that did not contribute to the analysis. This step is crucial to streamline the data and improve performance.
2. Data Type Identification: Ensuring each column had the correct data type was essential for accurate calculations and visualizations. I reviewed and modified data types where necessary to match the expected format.

Data Model

After completing the ETL process, the next step was to create a robust data model. A well-structured data model is the backbone of any Power BI report.

Data Model Structure:

1. Star Schema: I designed a star schema data model, which is known for its simplicity and efficiency. This model consists of one central fact table and multiple dimension tables.
2. Fact and Dimension Tables: The central fact table in my model is the Sales table. From this, I derived several dimension tables, including Category, Sub Category, Customer, Region etc. each linked to the fact table through one-to-many relationships.
3. Calendar Table: To facilitate time-based analysis, I created a calendar table. This table is essential for performing time intelligence calculations such as year-to-date (YTD) and previous period comparisons.

Data Model

DAX Calculations

DAX (Data Analysis Expressions) is a powerful formula language in Power BI used for creating custom calculations.

Key DAX Calculations:

1. Performance YTD: Calculated the year-to-date performance metrics.
2. Previous Period Calculation: Compared current period metrics with previous periods to identify trends and changes.
3. Target Calculation: Set and measured against business targets.
4. Average and Percentage Calculations: Computed averages and percentage contributions of various metrics.
5. Field Parameter: Created a field parameter to dynamically switch between sales, profit, and orders in the visualizations.
6. Dynamic Formatting: Used DAX to format numbers dynamically, displaying values in billions, millions, or thousands as appropriate.

Data Visualization

With the data model and calculations in place, I moved on to creating the visualizations. My Power BI report consists of four pages, each designed to provide insights into different aspects of the business.

Report Pages:

1. Overview Page: This page displays top-level metrics such as total sales, profit, and orders. It provides a high-level summary of the store’s performance.

Overview Page

2. Product Page: Focused on product-related insights, this page explores metrics by category, sub-category, and individual products. It helps identify top-performing products and categories.

Product Page

3. Customer Page: This page analyses customer-related data, including top customers, shipping modes, and order trends. It provides insights into customer behaviour and preferences.

Customer Page

4. Region Page: Displays region-wise data, highlighting sales, profit, and orders by country. It helps in understanding geographical performance and identifying key markets.

Region Page

5. Filter Pane: Synchronized filter to filter specific data across the report.

Filter Pane

Dashboard Link

Global Super Store Analysis using Power BI

Insights

1. Best Selling and Most Profitable Category

Technology Category: Sales: ₹4.7M, Profit: ₹663.8K

2. Best Selling and Most Profitable Subcategory

Best Selling Subcategory:

Phones: Sales: ₹1.7M, Profit: ₹216K

Most Profitable Subcategory:

Copiers: Sales: ₹1.5M, Profit: ₹258K

3. Top Selling Subcategories

1st Place: Phones

2nd Place: Copiers

3rd Place: Chairs

4. Most Profitable Customer Segment

Consumer Segment: Profit: ₹749K

5. Preferred Ship Mode

Standard Class: Total Orders: 30.8K

6. Most Profitable Region

Central Region: Profit: ₹311K

7. City with the Highest Number of Sales

New York City: Sales: ₹256K

Conclusion

The Global Super Store Analysis project provides a clear understanding of business performance across various dimensions. The Technology category and Phones subcategory lead in sales, while Copiers are the most profitable. The Consumer segment and Standard Class shipping mode are the top performers in their respective areas. Regionally, the Central region is the most profitable, and New York City has the highest sales.

These insights can guide strategic decisions in inventory management, marketing, and resource allocation, enhancing efficiency and profitability. This project demonstrates the power of Power BI in converting raw data into actionable insights and showcases my skills in data analysis and visualization.

Please leave a comment and applaud my work if you like it.