Ravindra BagaleCourses & study guides मराठी Track your progress

Logistics KPIs in Excel and Power BI

Ten important KPIs for courier, transport and warehouse business. Each card has what it is, why it matters, the Excel formula, the DAX measure and a tiny worked example. Every card shows the Excel formula next to the Power BI DAX measure. For Excel, put the card’s example table in cell A1 (labels in column A, numbers in column B).

About this data: All examples are made-up teaching numbers for logistics firms in Mumbai, Rotterdam, Chicago, Delhi, Birmingham, Chennai, Hamburg, Atlanta, Kolkata and Leeds, in ₹, €, $ and £. Table names (Shipments, Orders, Costs, Trips, Fleet, Bins, Receipts, Fuel) are sample names.
Logistics KPIs in Power BI overview card
On-Time Delivery % KPI card: what, why, DAX and example
On-Time Delivery %: Share of shipments delivered by the promised time.DAX: OTD % = DIVIDE(CALCULATE(COUNTROWS(Shipments), Shipments[DeliveredAt] <= Shipments[PromisedAt]), COUNTROWS(Shipments))Excel: =B3/B2
Cost per Shipment KPI card: what, why, DAX and example
Cost per Shipment: Average transport cost of one shipment.DAX: Cost per Shipment = DIVIDE(SUM(Costs[Amount]), COUNTROWS(Shipments))Excel: =B2/B3
Perfect Order Rate % KPI card: what, why, DAX and example
Perfect Order Rate %: Share of orders delivered complete, on time and undamaged.DAX: Perfect Order % = DIVIDE(CALCULATE(COUNTROWS(Orders), Orders[HasIssue] = FALSE()), COUNTROWS(Orders))Excel: =B3/B2
Fleet Utilisation % KPI card: what, why, DAX and example
Fleet Utilisation %: Share of truck-days the fleet was actually working.DAX: Fleet Util % = DIVIDE(SUM(Trips[TruckDays]), DISTINCTCOUNT(Fleet[TruckID]) * COUNTROWS('Date'))Excel: =B4/(B2*B3)
Average Delivery Time KPI card: what, why, DAX and example
Average Delivery Time: Average hours from pickup to delivery.DAX: Avg Delivery Hours = AVERAGEX(Shipments, DATEDIFF(Shipments[PickedAt], Shipments[DeliveredAt], HOUR))Excel: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
Freight Cost per Km KPI card: what, why, DAX and example
Freight Cost per Km: Running cost of the fleet for each kilometre.DAX: Cost per Km = DIVIDE(SUM(Costs[Amount]), SUM(Trips[Km]))Excel: =B2/B3
Warehouse Space Utilisation % KPI card: what, why, DAX and example
Warehouse Space Utilisation %: Share of pallet slots in use.DAX: Space Util % = DIVIDE(SUM(Bins[UsedSlots]), SUM(Bins[TotalSlots]))Excel: =B3/B2
Dock-to-Stock Time KPI card: what, why, DAX and example
Dock-to-Stock Time: Average hours from truck arrival to stock on shelf.DAX: Dock to Stock Hrs = AVERAGEX(Receipts, DATEDIFF(Receipts[ArrivedAt], Receipts[ShelvedAt], HOUR))Excel: =B2/B3
Shipment Damage Rate % KPI card: what, why, DAX and example
Shipment Damage Rate %: Share of shipments that arrived damaged.DAX: Damage % = DIVIDE(CALCULATE(COUNTROWS(Shipments), Shipments[Damaged] = TRUE()), COUNTROWS(Shipments))Excel: =B3/B2
Fuel Efficiency (Km per Litre) KPI card: what, why, DAX and example
Fuel Efficiency (Km per Litre): Kilometres driven for each litre of diesel.DAX: Km per Litre = DIVIDE(SUM(Trips[Km]), SUM(Fuel[Litres]))Excel: =B2/B3

Quick quiz

1. On-Time Delivery %: Shipments delivered 5,000, Delivered on time 4,650. The answer is…

2. Cost per Shipment: Transport cost €126,000, Shipments 4,200. The answer is…

3. Perfect Order Rate %: Orders shipped 2,000, Orders with no error 1,940. The answer is…

4. Which KPI means: “Share of truck-days the fleet was actually working.”

← All business KPI sets (KPI Hub)