1. Objective

To design and maintain an interactive Power BI Dashboard that provides a comprehensive overview of key supply chain performance indicators including sales, inventory, suppliers, shipping, and regional performance. This dashboard aims to enable timely data-driven decisions, optimize operations, and improve overall supply chain efficiency.

2. Scope

The dashboard covers the following primary areas:

1. Overview
   • Total Sales
   • Units Sold
   • Average Supplier Lead Time
   • Inventory Summary
   • Shipping Summary
2. Inventory
   • Current Stock Levels
   • Inventory Turnover Ratio
   • Days Inventory on Hand (DOH)
   • SKU-level Inventory Details
   • Stockout Rates
   • Low/Out-of-Stock Items
3. Sales & Costs
   • Total Sales & Units Sold
   • Cost of Goods Sold (COGS)
   • Profit Margins
   • Average Order Value
   • Sales vs COGS by Product Type
   • Top SKUs by Profitability
   • Total Cost Breakdown (Manufacturing, Transportation, Storage, Shipping)
4. Suppliers
   • Supplier Lead Time
   • Supplier Defect Rates
   • On-Time Delivery %
   • Cost of Defective Items
   • Revenue Contribution by Supplier
   • Supplier Compliance by Inspection Results
5. Shipping
   • Average Shipping Time
   • On-Time Shipping Rate
   • Shipping Cost per Order
   • Volume and Cost by Route
   • Carrier Performance Comparison
6. Territory/Region Performance
   • Sales, Units Sold, and Profit % by City/Region
   • Regional Shipping Time Analysis
   • Demand Coverage by Region
7. Product Details
   • SKU-level insights across all metrics
   • Stock, Availability, Units Sold, Production
   • DOH, Lead Time, Defect Rate, Total Sales, Profit Margin

3. Functional Requirements

4. Data Sources

• ERP system for Sales & Inventory
• Supplier database for lead time and defect data
• Shipping/logistics software for carrier performance
• Financial system for COGS and manufacturing cost

5. Users & Roles

6. KPIs Tracked

• Total Sales & Units Sold
• Avg. Supplier Lead Time
• Supplier Defect Rate & On-Time Delivery %
• Stockout Rate & Inventory Turnover Ratio
• Days Inventory on Hand
• Profit Margin & Average Order Value
• COGS Breakdown
• Shipping Cost as % of Sales
• Demand Coverage by Product/Region

7. Visual Components

• KPI Cards
• Trend Charts (Monthly)
• Stacked Column/Bar Charts
• Heatmaps (by City/Region)
• Tables with conditional formatting
• Interactive slicers & filters
• Map visualizations (Bing Maps integration)

8. Refresh Schedule

• Frequency: Daily
• Time: 12:58 AM (as per current configuration)
• Method: Auto-refresh via Power BI Gateway

9. Security & Permissions

• Row-level security enabled by user role
• Dashboard shared within secure workspace
• Export restricted to authorized personnel only

10. Assumptions and Constraints

• Data accuracy depends on source system integrity
• Performance may vary with data volume increases
• Timely data entry from suppliers and logistics teams is essential

11. Project Completion Methodology

The development of the Power BI Dashboard was completed using a combination of data engineering and analytical tools including SQL, Power Query, and DAX. Below is the step-by-step methodology used during the project.

11.1 Data Extraction (SQL Queries)

Objective: Extract raw data from different source systems (ERP, Supplier Database, Logistics Platform).

Tools: Microsoft SQL Server, Azure SQL Database, PostgreSQL

Examples of SQL Queries Used:

• Sales Transactions:

sqlCopyEditSELECT 
    OrderID,
    OrderDate,
    ProductID,
    Quantity,
    UnitPrice,
    TotalAmount,
    Region,
    City
FROM Sales_Orders
WHERE OrderDate >= '2025-01-01';

• Inventory Levels:

sqlCopyEditSELECT 
    SKU,
    ProductType,
    StockLevel,
    WarehouseLocation,
    LastUpdated
FROM Inventory_Stock
WHERE LastUpdated = (SELECT MAX(LastUpdated) FROM Inventory_Stock);

• Supplier Lead Time and Defects:

sqlCopyEditSELECT 
    SupplierID,
    ProductID,
    AVG(LeadTimeDays) AS AvgLeadTime,
    SUM(DefectiveUnits) * 1.0 / SUM(DeliveredUnits) AS DefectRate
FROM Supplier_Performance
GROUP BY SupplierID, ProductID;

• Shipping Performance:

sqlCopyEditSELECT 
    Carrier,
    OriginCity,
    DestinationCity,
    ShippingMode,
    ShippingTimeDays,
    Cost,
    OnTimeFlag
FROM Shipping_Logistics;

11.2 Data Transformation (Power Query)

Objective: Clean, reshape, and normalize raw data into model-ready format.

Tools: Power Query Editor in Power BI

Transformations Applied:

• Date Formatting & Hierarchy:
  • Converted order dates into proper Date type and created Year-Month hierarchies.
• Null Handling & Type Casting:
  • Replaced nulls with defaults and enforced data types (e.g., Decimal, Whole Number).
• Merge Queries:
  • Merged Sales and Inventory tables on SKU.
  • Joined Shipping and Orders tables on OrderID.
• Column Renaming & Filtering:
  • Renamed technical columns to user-friendly names.
  • Filtered out incomplete or test records.
• Conditional Columns:
  • Added “Stock Status” column: mCopyEditif [StockLevel] = 0 then "Out of Stock" else if [StockLevel] < 2000 then "Low Stock" else "In Stock"

11.3 Data Modeling & Calculations (DAX)

Objective: Create calculated columns, measures, and KPIs for analysis and visuals.

Tools: DAX (Data Analysis Expressions) in Power BI

Key Measures Created:

• Total Sales:

DAXCopyEditTotal Sales = SUM(Sales_Orders[TotalAmount])

• Units Sold:

DAXCopyEditTotal Units Sold = SUM(Sales_Orders[Quantity])

• Profit Margin (%):

DAXCopyEditProfit Margin % = DIVIDE([Total Gross Profit], [Total Sales], 0)

• Inventory Turnover Ratio:

DAXCopyEditInventory Turnover = DIVIDE([COGS], [Average Inventory Value], 0)

• Days Inventory On Hand (DOH):

DAXCopyEditDOH = DIVIDE(365, [Inventory Turnover])

• Average Supplier Lead Time:

DAXCopyEditAvg Lead Time = AVERAGE(Supplier_Performance[LeadTimeDays])

• On-Time Shipping Rate:

DAXCopyEditOn-Time Rate = 
DIVIDE(
    CALCULATE(COUNTROWS(Shipping_Logistics), Shipping_Logistics[OnTimeFlag] = TRUE),
    COUNTROWS(Shipping_Logistics),
    0
)

• Demand Coverage:

DAXCopyEditDemand Coverage = 
DIVIDE([Units Produced], [Units Sold], 0) * 100

• Shipping Cost per Order:

DAXCopyEditShipping Cost per Order = 
DIVIDE(SUM(Shipping_Logistics[Cost]), COUNTROWS(Sales_Orders))

Here is your downloadable sample data file containing sheets for Sales, Inventory, and Supplier data:

Developed by Muhammad Zahid

Connect on LinkedIn