Data Analysis — Supply Chain Dashboard: How to visualize late delivery
In the last article, I showed a supply chain dashboard that visualizes sales in many aspects such as by country, and category.
If you never read that article, you could read it here (recommend reading the previous article before this one for better understanding):
In this article, I will show another interesting insight you can extract from this dataset. I will use the same tool for data analysis, Power BI.
When talking about the supply chain, 1 parameter often being discussed is the delivery time since it relates to customer satisfaction and company profit. Let’s see an example dashboard.
💽 Dataset
Data used in this article comes from
In this example dataset, there are 2 columns including “Days for shipping (real)” and “Days for shipment (scheduled)” which could be analyzed to find the delivery time insight.
📊 Visualization
I created another page in the same file as the previous article to focus only on late delivery.
- Create a new column, Is_Late = Days for shipping (real) > Days for shipment (scheduled). This column will be 1 if the order was delivered later than scheduled.
- Created a new measure, late ratio. This measure will be used to visualize the frequency of late delivery.
Is_Late_Ratio = sum(DataCoSupplyChainDataset[Is_Late])/count(DataCoSupplyChainDataset[Is_Late])This is a snapshot of my dashboard.
I designed this dashboard using the concept “Read From Left To Right and Top To Bottom”. It could be divided into 6 parts as shown below.
- Part 1 is a filter plain including market and time filters.
- Part 2 is a summary of interesting parameters including % of late delivery (Is_Late_Ratio), the total number of items sold (order id), and the total number of transactions. There is also a graph to illustrate the movements of Is_Late_Ratio with time. I also added zoom sliders for the X-Y axis so that users can zoom in and out along this axis because it will be difficult to see the movement of Is_Late_Ratio which often lies between 0.5 and 0.6.
- The next 4 parts will show more detail about % of late delivery which is the main parameter of this page.
- Part 3 is a filled map by Is_Late_Ratio. Green means that country has a low Is_Late_Ratio. Yellow means that the country has a medium Is_Late_Ratio and Red means that country has a high Is_Late_Ratio (usually deliver later than planned). I decided to use a filled map instead of a bubble plot (like in the previous article) because the Is_Late_Ratio of each country is not much different and it will be easier to spot a country with an abnormal Is_Late_Ratio (very low/high) using color instead of circle size.
- Part 4 is aimed to understand the correlation between late delivery and scheduled days for shipment like how much percent of late delivery when planning 4 shipment days. A bubble plot is the most suitable for this case since the circle size on the X-Y axis represents the number of late shipments for each shipment days
- Part 5 is the breakdown of Is_Late_Ratio based on customer segment and department name. I set a threshold for red color at 57% (assume that this is the late delivery target)
- Part 6 shows sales by category sorted by alphabetic order. The bar color is set using the same criteria as part 5.
You can filter data by just selecting the filter pane in part 1 to clicking on the category you want to filter.
❓ Question
These are examples of questions that could be answered using this dashboard.
- Which country has the highest/lowest percentage of late shipments? The map in part 3 shows that Surinam, Chad, Saudi Arabia, and Serbia are green which means no late shipment. However, when filtering by clicking on those countries, there are only 1 or 2 transactions for each country (in the summary of part 2). On the other hand, Sudan del Sur has the highest percentage of late shipments (red on the map). When filtering Sudan del Sur, there is only 1 transaction and it is a late shipment, so the percentage of late shipments becomes 100%.
Since the number of transaction in those countries are low, the percentage of late shipments might not represent actual delivery efficiency (it is easy to get 0% or 100% if there are only 1 or 2 transactions). Therefore, I exclude countries with transactions lower than 10 times by adding a filter on this page.
The map in part 3 currently is filled with fewer countries and the color range is narrower from 0–1 to 0.39–0.78 which makes it easier to differentiate between low and high late shipment countries. Uzbekistan become the country with the lowest percentage of late shipments at 39%. Gabon, Tunez, and Bosnia have the highest percentage of late shipments at 78%.
- Which product had the highest percentage of late shipment in 2017? Filter year in part 1 to 2017 and sort category in part 6 by Is_Late_Ratio descending.
DVDs were always delivered late (100% late and 7 transactions). Men’s clothing and Lacrosse were following at above 65%. I can’t figure out the reason for the late shipment of these categories from this dataset alone. This insight can only be a guideline for further investigation of late shipments.
- What is the correlation between late delivery and scheduled days for shipment?
An answer is illustrated in part 3 using a bubble plot.
When scheduling 1 shipment day, the actual shipment always is 2 days (100% late).
When scheduling 2 shipment days, the actual shipment could be 2–6 days almost equally. That means scheduling 2 shipment days is difficult to achieve.
When scheduling 4 shipment days, actual shipment days can be lower and higher than that (this makes sense since scheduling 4 shipment days can mean high uncertainty on shipment day). In this case, half of the shipments are late but not later than 6 days. In summary, the late shipment has a higher chance when planning a lower shipment day.
This dashboard could provide an overview of late shipments but more actions are required for process improvement such as talking with the supply chain team about process improvement. At least we know where to start.
Thank you for reading. If you have any suggestions, please let me know.
