Power BI Dashboard Design Guide - Supply Chain Delay Analysis

This guide provides instructions and structural layouts to build an interactive, professional dashboard in Power BI using the populated MySQL database (supply_chain_db).

1. Data Connection & Model Schema

Connecting Power BI to MySQL

Open Power BI Desktop.
Click Get Data > MySQL database.
Set Server to localhost:3306 and Database to supply_chain_db.
In the credentials prompt, use the Database tab and input:
- Username: root
- Password: admin123
Select all tables: departments, categories, customers, products, orders, and order_items. Click Load.

Data Modeling Relationships (Star/Snowflake Schema)

Ensure Power BI sets up the correct schema relationships under the Model View:

customers (customer_id) 1 ---- * orders (order_customer_id)
orders (order_id) 1 ---- * order_items (order_id)
products (product_card_id) 1 ---- * order_items (order_item_cardprod_id)
categories (category_id) 1 ---- * products (product_category_id)
departments (department_id) 1 ---- * products (No direct relationship; create link if needed or link through custom schemas. Otherwise, departments can be linked to products via category if category stores department, or directly as normalized in schema.)

2. Calculated Measures (DAX Formulas)

Create a new table _Measures and write the following DAX calculations:

Total Orders:
```
Total Orders = COUNT(orders[order_id])
```
Total Sales (Gross):
```
Total Sales = SUM(order_items[sales])
```

Total Profit:

Total Profit = SUM(order_items[benefit_per_order])

Delayed Orders:

Delayed Orders = CALCULATE([Total Orders], orders[late_delivery_risk] = 1)

Late Delivery Rate:

Late Delivery Rate = DIVIDE([Delayed Orders], [Total Orders], 0)

Average Shipping Delay (Actual vs Scheduled):

Avg Actual Days = AVERAGE(orders[days_for_shipping_real])

Avg Scheduled Days = AVERAGE(orders[days_for_shipping_scheduled])

3. Dashboard Layout & Visualization Pages

To ensure a premium design, use a Dark Slate Theme (Background: #1A1D24, Cards: #252932, Accents: Teal #00B4D8 for on-time, Coral/Red #FF5C5C for delays, and Gold/Yellow #F2C94C for warnings).

Page 1: Executive Logistics Overview

Purpose: High-level dashboard for logistics managers.

Top KPI Cards (3-4 columns):
- Total Sales (Format: $0.0M)
- Total Profit (Format: $0.0M)
- Total Orders (Format: 0.0K)
- Late Delivery Rate (Format: 0.0% with conditional text color: Red if >50%, Green if <10%)
Main Visual (Left): Map or Filled Map
- Location: orders[order_country]
- Bubble Size: [Total Orders]
- Color Saturation: [Late Delivery Rate] (diverging from light teal to deep red)
Main Visual (Right): Donut Chart
- Legend: orders[shipping_mode]
- Values: [Total Orders]
Trend Visual (Bottom): Area Chart
- Axis: orders[order_date] (grouped by Month)
- Values: [Late Delivery Rate] and [Avg Actual Days]

Page 2: Supply Chain Delay Bottlenecks

Purpose: Deep-dive into delayed areas and bottlenecks.

Slicers (Top Ribbon): Market, Customer Segment, Shipping Mode
Horizontal Bar Chart: Delay Rate by Region
- Y-Axis: orders[order_region]
- X-Axis: [Late Delivery Rate]
Column Tree/Matrix Visual: Delay Rate by Department & Category
- Rows: departments[department_name], categories[category_name]
- Columns: [Total Orders], [Delayed Orders], [Late Delivery Rate]
Scatter Plot: Sales vs Profit Loss by Country
- X-Axis: [Total Sales]
- Y-Axis: [Late Delivery Rate]
- Details: orders[order_country]

Page 3: Financial Risk & ML Insights

Purpose: Analyzing the business cost of delays and predictions.

KPI Cards:

Profit Lost on Late Deliveries:

Profit Lost = CALCULATE([Total Profit], orders[delivery_status] = "Late delivery")

Bar Chart: Sales & Profits by Delivery Status
- X-Axis: orders[delivery_status]
- Y-Axis: [Total Sales] and [Total Profit] side-by-side
Python Prediction Integration:
- You can load the test predictions or use a Python Script inside Power Query Editor to run the saved model best_xgb_model.pkl on new orders, adding a Predicted_Delay_Risk column to flag future high-risk orders.