This document outlines the requirements for the Power BI report that analyzes the supply chain process, focusing on revenue, stock availability, defect rates, order fulfillment, and SKU-wise supplier performance. The objective is to enable effective monitoring and decision-making through interactive visualizations.

2. Objectives

  • To track total revenue, quantity sold, and stock availability.
  • To analyze order fulfillment rates and defect percentages.
  • To assess supplier performance based on SKU-wise sales.
  • To evaluate carrier efficiency in delivery.

3. Data Requirements The report utilizes the following key data points:

  • Financial Metrics: Total revenue, revenue per SKU, revenue per supplier.
  • Sales Metrics: Quantity produced, quantity sold, available stock.
  • Performance Indicators: Order fulfillment rate, average defect rate.
  • Supplier Analysis: Sales by supplier and SKU.
  • Carrier Analysis: Performance of different carriers in delivering orders.

4. Report Components The report consists of various visualizations, including:

  1. Total Revenue Overview: A card visualization displaying overall revenue.
  2. Quantity Sold: Representation of units sold across different SKUs.
  3. Stock Analysis: Availability of stock for different SKUs.
  4. Order Fulfillment Rate: A gauge or KPI displaying order fulfillment percentages.
  5. Average Defect Rate: KPI showing the average defect rate for produced goods.
  6. Supplier Performance Matrix: Sales performance of different suppliers.
  7. Carrier Performance: Delivery analysis across different carriers.

5. Functional Requirements

  • Interactivity:
    • Filters for selecting year, supplier, and SKU.
    • Dynamic highlighting of suppliers and SKUs upon selection.
  • Customization:
    • Users should be able to modify color schemes and labels.
    • Drill-through capability for detailed SKU-level insights.
  • Comparative Analysis:
    • YoY revenue trends.
    • Supplier performance comparison.
    • Order fulfillment and defect rate analysis.

6. User Interface Requirements

  • Intuitive layout with clear labels and descriptions.
  • Consistent color schemes to differentiate KPIs.
  • Hover tooltips for detailed metric insights.

7. Performance Requirements

  • The report should load within 5 seconds.
  • It should handle large data volumes efficiently.

8. Deployment & Access

  • The report will be published to the Power BI service.
  • Access will be provided to supply chain managers and relevant stakeholders.
Supply Chain AnalysisDownload

9. Project Completion Method

9.1 SQL Queries

  • Data extraction from the supply chain database using SQL queries.
  • Example SQL query to fetch revenue and sales data: SELECT Region, Year, SUM(Revenue) AS TotalRevenue, SUM(QuantitySold) AS TotalQuantity FROM SalesData GROUP BY Region, Year;
  • Query to fetch SKU-wise supplier performance: SELECT SKU, Supplier, SUM(Revenue) AS SupplierRevenue FROM SupplierSales GROUP BY SKU, Supplier;

9.2 Power Query

  • Data transformation and cleaning using Power Query.
  • Key steps include:
    • Removing duplicates and handling missing values.
    • Merging supplier data with sales data.
    • Creating calculated columns for performance analysis.
  • Example Power Query transformation: let Source = Sql.Database("ServerName", "DatabaseName"), FilteredRows = Table.SelectRows(Source, each ([Year] >= 2024)), ChangedTypes = Table.TransformColumnTypes(FilteredRows,{{"Revenue", Currency.Type}}) in ChangedTypes

9.3 DAX (Data Analysis Expressions)

  • DAX measures for report calculations.
  • Example DAX measures:
    • Total Revenue: TotalRevenue = SUM(SalesData[Revenue])
    • Order Fulfillment Rate: OrderFulfillmentRate = DIVIDE(SUM(Orders[Fulfilled]), SUM(Orders[TotalOrders]), 0) * 100
    • Average Defect Rate: AvgDefectRate = AVERAGE(Production[DefectRate])

10. Conclusion This Power BI report provides a comprehensive analysis of the supply chain, helping stakeholders make data-driven decisions. The report ensures efficiency, interactivity, and real-time monitoring of key supply chain metrics.

sample data files.

  1. Download SupplyChain_SalesData.csv
  2. Download SupplyChain_StockData.csv
  3. Download SupplyChain_CarrierData.csv
SupplyChain_SalesDataDownload
SupplyChain_StockDataDownload
SupplyChain_CarrierDataDownload