Transforming Dirty Automotive Logistics Data into Executive Decision Support in Power BI
Building business intelligence dashboards for messy, real-world data requires far more than dropping charts onto a canvas. It demands rigorous exploratory auditing, robust Power Query ETL pipelines, structured DAX modeling, and executive-level storytelling.
In this project, I engineered an end-to-end Power BI reporting solution for JCars, a multi-branch automotive dealership and logistics network. Here is how I took an uncleaned transactional flat file and transformed it into a decision-support platform tracking KES 1.905B in revenue across 276 customer orders.
1. The Challenge: Uncleaned Flat File Data
The source data (Jcars_data.csv) captured transaction-level vehicle sales, delivery tracking, and financing data. However, the raw data presented major data quality challenges:
-
Mixed Currencies: Monetary columns contained mixed string prefixes (
USD,KES) alongside bare numbers. -
Inconsistent Categorical Text: Branch locations, vehicle makes, and delivery statuses suffered from varied casing (
toyota,TOYOTA) and split synonyms (Delivered,completed,Done). -
Locale-Sensitive Dates: Conflicting
DD/MM/YYYYandMM/DD/YYYYformats caused parse errors and nulls. - Data Governance Traps: Dispatched units showing delivery timestamps prior to order placement dates, and cancelled orders retaining "Paid" settlement statuses.
2. Power Query ETL & Data Standardization
To ensure the downstream semantic model remained performative and reliable, all cleaning logic was addressed upfront:
Currency Normalization
All values were standardized to Kenya Shillings (KES). Text extraction logic stripped currency prefixes, applied an exchange rate baseline of 1 USD = 130 KES, and cast columns to native currency data types:
Final KES = Raw Numeric Value * Exchange Rate
Date Engine Resiliency
To resolve locale parsing errors across operating systems, I implemented locale-aware date parsing with conditional fallbacks, preserving chronological continuity across all order and delivery timelines.
Categorical Bucketing
Using DAX and M transformations, over a dozen fragmented delivery labels were mapped into four unified operational states:
Delivered (encompassing "Delivered", "Completed", "Done")
In Transit (encompassing "On the way", "At the yard", "Dispatched")
Cancelled
Returned
3. Dimensional Modeling & DAX Architecture
Rather than computing ad-hoc aggregations inside visual fields, I established a dedicated _Measures table housing normalized DAX logic:
// 1. Top-Line Turnover
Total Orders = DISTINCTCOUNT(Jcars_data[Order ID])
Total Units Sold = SUM(Jcars_data[Units Sold])
Total Revenue = SUMX(Jcars_data, Jcars_data[Units Sold] * Jcars_data[Unit Selling Price])
// 2. Cost & Profitability Mechanics
Total Cost = SUMX(Jcars_data, (Jcars_data[Units Sold] * Jcars_data[Unit Cost]) + Jcars_data[Clean_Logistics_Cost])
Gross Profit = [Total Revenue] - [Total Cost]
Gross Profit Margin = DIVIDE([Gross Profit], [Total Revenue], 0)
// 3. Operational Friction
Return Rate =
DIVIDE(
CALCULATE([Total Units Sold], Jcars_data[Clean_Delivery_Status] = "Returned"),
[Total Units Sold],
0
)
Cancellation Rate =
DIVIDE(
CALCULATE([Total Orders], Jcars_data[Clean_Delivery_Status] = "Cancelled"),
[Total Orders],
0
)
Core Benchmark Results:
Total Revenue: KES 1,904,596,846.00
Total Gross Profit: KES 393,327,843.00
Gross Profit Margin: 20.65%
Units Sold: 466 Units across 276 Orders
Return Rate: 31.88%
Cancellation Rate: 13.41%
4. The 3-Page Executive Dashboard
The report was organized into three purpose-built views with persistent button navigation:
1. Executive Overview: 6 high-level KPI cards, revenue ranking by vehicle make, branch turnover distribution, and monthly sales trends.
2. Vehicle Performance: A hierarchical drill-down matrix (Make, Model), vehicle body type share, and a Margin % vs. Return Rate scatter chart with reference quadrants separating star performers from high-risk liabilities.
3. Operations & Logistics: Detailed operational metrics comparing order delivery statuses, branch order volumes, and a clustered column chart evaluating Collected Delivery Fees vs. Actual Logistics Costs.
5. Key Strategic Takeaways
The High-End Return Paradox: While luxury brands like BMW drive healthy unit margins, they exhibit an alarming 55.56% return rate. Recommending mandatory pre-delivery mechanical inspections (PDI) before customer dispatch.
Logistics Subsidy Leakage: Upcountry and high-volume branches frequently incur logistical costs that exceed the flat delivery fees billed to customers. Introducing dynamic, distance-based freight pricing will protect operating margins.
Pre-Delivery Churn: Extended fulfillment delays account for a 13.41% cancellation rate, indicating an urgent need to automate inventory release workflows at regional holding yards.
Conclusion
A successful analytics project connects low-level data transformation directly to executive decision-making. By pairing resilient ETL processes with intentional visual design, this dashboard turns fragmented logistics records into actionable operational clarity.
