W3SKILLSET

Superstore DB Performance Power BI Report

By /

Requirement Document for Superstore DB Performance Power BI Report

1. Introduction

This document outlines the requirements for the Superstore DB Performance Report created using Power BI. The report provides insights into sales revenue, profit, customer segments, categories, and shipping modes, helping stakeholders make data-driven decisions.

2. Purpose

The purpose of this Power BI report is to analyze sales performance across various segments, categories, and geographies. It will help:

  • Monitor sales trends over time.
  • Identify profitable categories and segments.
  • Analyze sales distribution across different regions.
  • Evaluate the impact of shipment modes on sales and profit.

3. Data Sources

The report uses the Super Store Database as its primary data source. The dataset includes:

  • Sales Transactions
  • Customer Segments
  • Product Categories
  • Shipment Modes
  • Regional Sales Data

4. Report Features

4.1 Key Performance Indicators (KPIs)

  • Total Sales Revenue: $2.30M
  • Total Profit: $286.40K
  • Total Quantity Sold: 238.17K
  • Sales by Segment:
    • Consumer: 50.56%
    • Corporate: 30.74%
    • Home Office: 18.7%
  • Sales by Category:
    • Technology: 36.5%
    • Furniture: 35.87%
    • Office Supplies: 27.62%

4.2 Sales Breakdown

  • Sales by Year: Tracks revenue and profit from 2019-2022.
  • Sales by Region: Sales performance across different geographic locations.
  • Sales by Segment: Distribution across Consumer, Corporate, and Home Office.
  • Sales by Category: Performance of Technology, Furniture, and Office Supplies.

4.3 Profit Analysis

  • Profit by Segment:
    • Consumer: $134.12K
    • Corporate: $91.98K
    • Home Office: $60.3K
  • Profit by Region: Detailed profit insights across different areas.
  • Profit by Shipment Mode: Identifies the most profitable delivery methods.

4.4 Shipment Analysis

  • Sales by Ship Mode: Standard Class, Second Class, First Class, Same Day.
  • Impact of Ship Mode on Profit: Analyzes cost-effectiveness.

5. Functional Requirements

5.1 Data Filters

  • Date Range: Filter transactions by specific timeframes.
  • Geographical Filters: Select country, state, and city for localized insights.
  • Category Filters: Drill down into product categories.
  • Segment Filters: View sales and profit for Consumer, Corporate, or Home Office.

5.2 Visualizations

  • Bar Charts: Sales by Category, Segment, and Year.
  • Line Graphs: Sales and Profit Trends.
  • Pie Charts: Sales and Profit Distribution.
  • Maps: Geographical distribution of sales.

5.3 Interactivity

  • Drill-down capability: Users can explore data at different levels.
  • Dynamic filtering: Adjust data in real-time based on selected criteria.

6. Security & Access Control

  • Role-based access for different user groups.
  • Restricted data views based on user privileges.

7. Performance & Optimization

  • Optimized DAX queries for faster report loading.
  • Aggregated data for improved efficiency.
Super Store DBDownload

1. Data Extraction and Loading (SQL Queries)

The Super Store Database is used as the primary data source. SQL queries are utilized to extract relevant sales, profit, customer, and shipping data.

1.1 Extract Sales Data

sqlCopySELECT 
    OrderID,
    OrderDate,
    CustomerID,
    Segment,
    Category,
    SubCategory,
    Sales,
    Profit,
    Quantity,
    ShipMode,
    Region,
    Country,
    State,
    City
FROM SalesData
WHERE OrderDate BETWEEN '2019-01-01' AND '2022-12-31';

1.2 Aggregate Sales by Category and Segment

sqlCopySELECT 
    Category,
    Segment,
    SUM(Sales) AS TotalSales,
    SUM(Profit) AS TotalProfit,
    SUM(Quantity) AS TotalQuantity
FROM SalesData
GROUP BY Category, Segment;

1.3 Sales by Shipment Mode

sqlCopySELECT 
    ShipMode,
    Category,
    SUM(Sales) AS TotalSales,
    SUM(Profit) AS TotalProfit
FROM SalesData
GROUP BY ShipMode, Category;

2. Data Transformation (Power Query – ETL Process)

The extracted SQL data is processed in Power Query to clean, transform, and model data efficiently before loading it into Power BI.

2.1 Data Cleaning

  • Remove duplicate records.
  • Convert data types (e.g., Date format for Order Date).
  • Handle missing values by replacing NULL with appropriate defaults.

2.2 Creating New Columns

Using Power Query (M Language) to generate additional columns.

  • Extract Year from Order Date:
mCopyYearColumn = Date.Year([OrderDate])
  • Extract Month Name:
mCopyMonthName = Date.ToText([OrderDate], "MMMM")

2.3 Data Merging and Relationships

  • Merge Customer and Sales Data using CustomerID.
  • Merge Geographical Data for region-based analysis.
  • Establish relationships between Sales, Customers, and Shipment Mode tables.

3. Data Modeling and DAX Calculations

DAX (Data Analysis Expressions) is used to create calculated columns, measures, and KPIs.

3.1 Key Metrics (KPIs)

  • Total Sales
DAXCopyTotalSales = SUM(SalesData[Sales])
  • Total Profit
DAXCopyTotalProfit = SUM(SalesData[Profit])
  • Profit Margin
DAXCopyProfitMargin = DIVIDE([TotalProfit], [TotalSales], 0)
  • Sales Growth YoY
DAXCopySalesGrowthYoY = 
VAR PrevYearSales = CALCULATE([TotalSales], SAMEPERIODLASTYEAR(SalesData[OrderDate]))
RETURN IF(NOT ISBLANK(PrevYearSales), ([TotalSales] - PrevYearSales) / PrevYearSales, BLANK())

3.2 Sales Breakdown by Segment

DAXCopySalesBySegment = 
CALCULATE([TotalSales], ALLEXCEPT(SalesData, SalesData[Segment]))

3.3 Running Total of Sales

DAXCopyRunningTotalSales = 
CALCULATE(
    [TotalSales],
    FILTER(
        ALLSELECTED(SalesData[OrderDate]),
        SalesData[OrderDate] <= MAX(SalesData[OrderDate])
    )
)

3.4 Rank Products by Sales Performance

DAXCopyProductRank = RANKX(ALL(SalesData[SubCategory]), [TotalSales], , DESC, DENSE)

3.5 Sales Contribution by Category

DAXCopySalesContribution = 
DIVIDE([TotalSales], CALCULATE([TotalSales], ALL(SalesData[Category])), 0)

4. Power BI Visualization and Dashboard Design

After the data transformation and modeling, the final step is designing the Power BI Dashboard with the following components:

4.1 Interactive Visuals

  • Sales & Profit Trends (Line Chart)
  • Sales by Category (Bar Chart)
  • Sales by Segment (Pie Chart)
  • Profit by Region (Map Visualization)
  • Top Selling Subcategories (Table with Rankings)

4.2 Filters and Slicers

  • Date Range Selector
  • Category and Subcategory Filters
  • Segment Filter (Consumer, Corporate, Home Office)
  • Geographic Filters (Region, Country, State, City)

4.3 KPI Cards

  • Total Sales
  • Total Profit
  • Sales Growth YoY
  • Profit Margin

5. Deployment and Performance Optimization

5.1 Performance Optimization

  • Use Aggregated Tables for large datasets.
  • Optimize DAX Measures using SUMX instead of SUM when necessary.
  • Implement Incremental Refresh to speed up report updates.

5.2 Deployment Steps

  • Publish the report to Power BI Service.
  • Set up Scheduled Data Refresh for real-time insights.
  • Assign Role-Based Access for different stakeholders.

6. Conclusion

This Power BI Sales Performance Report provides a data-driven approach to analyze sales, profit, customer segments, and shipment trends. The use of SQL, Power Query, and DAX ensures efficient data extraction, transformation, and visualization, enabling stakeholders to make informed business decisions.

Sample data file that contains structured data similar to the Super Store dataset. It includes fields like Order ID, Order Date, Customer Segment, Category, Subcategory, Sales, Profit, Quantity, Ship Mode, Region, Country, State, and City.

Super_Store_SampleDownload
Scroll to Top