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.

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

Developed by Muhammad Zahid