DataCo Supply Chain Dashboard

The problem

DataCo is a global retailer shipping to customers in markets around the world. Management's reports only showed basic totals, which made it hard to compare delivery performance across markets, shipping modes, and product categories. The goal was to compare scheduled and actual shipping times alongside sales and benefit per order, and to find where delivery problems and revenue concentration sit.

Who the reports were for

• Operations Manager. Where delays happen and which shipping modes perform best or worst.
• Sales Director. Which markets and product categories generate the most sales and profit.
• Supply Chain Manager. Shipping constraints and markets with frequent late deliveries.
• Customer Experience team. How late deliveries affect the outcome of an order.

Preparing the data

The source data covered 2015 to 2018 across five tables: order details, product, category, department, and customer. Before modeling, I audited it and documented each problem and the decision I made about it.

• Types. Order and ship dates arrived as text and were converted to dates. Currency fields carried far more decimal places than reporting needs and were set to two decimals.
• Text. Applied TRIM and CLEAN and standardized casing on city, state, region, category, and shipping mode so duplicates could not split totals.
• Duplicate risk. Order Id repeats because an order can have several line items. Uniqueness is Order Id plus the item id, so I validated at that grain and removed no rows.
• Unusable fields. Product description was empty and product status was all zeros, so both were left alone and kept out of reporting.
• Scope discipline. Power Query was used only for preparation. Aggregations, KPIs, and relationships were deliberately kept out of it.

Data model

The five source tables became one fact table and four dimensions, with category and department folded into the product dimension. The grain is one row per order line.

Star schema in Power BI model view

• Relationships. Customer, product, and order geography each relate to the fact table many-to-one. The date dimension has an active relationship on order date and a second, inactive one on ship date.
• Filtering. Single-direction filters and no many-to-many relationships.
• Validation. Tested the model by summing sales by product, splitting revenue by customer segment and by year, and confirming totals broke when a relationship was removed and recovered when it was restored.

Executive summary

Figures shown with all years selected. The page adds revenue by category, top products, revenue over time, a top-10 customer table, and a geographic view of revenue by city using Azure Maps, with a year slicer.

Executive Summary

Shipping performance

This is the more analytical page. It tracks on-time percentage, late shipment count, and average shipping days, then breaks late shipments down by region and average delay by product category. In the view shown, 57.2% of shipments arrived late, with Western Europe and Central America accounting for the most late shipments.

Shipping Performance

What the data shows

• Revenue is concentrated. Fishing is the largest category at $8.8M, and a handful of categories account for most revenue.
• Late delivery is common. In the shipping view shown, 57.2% of shipments arrived late, with Western Europe and Central America contributing the most late shipments.

What I'd recommend

• Grow revenue in lower-performing product categories, or reconsider carrying them.
• Target mid-level customers to increase spend and reduce reliance on a few large buyers.

Measure decisions

• Late-only delay. Average delay is calculated over late shipments only. Averaging across all shipments would dilute the number with on-time orders and understate how bad late deliveries are.
• Scheduled vs. actual. The Late Orders table compares scheduled and real shipping days per order, so you can see exactly where the gap opens up.