DEV Community

Mohammed Swaleh
Mohammed Swaleh

Posted on Edited on

From Raw Data to Business Decisions: JCars Logistics Power BI Project

#analytics #powerfuldevs #datascience

Introduction

JCars Logistics, a leading vehicle importer and distributor in Kenya, wanted to transform its raw operational data into actionable insights. The dataset contained thousands of records on sales, customers, vehicles, branches, payments, deliveries, logistics costs, returns, and cancellations.

The challenge: build a Power BI solution that moves from messy raw data to a clean, interactive dashboard that helps management answer critical business questions.

Data Preparation

The raw dataset was full of issues:

  • Inconsistent date formats (Aug 29, 2025, 2026-13-04, not sure).
  • Mixed currencies (KES, USD, EUR, ZAR).
  • Spelling variations (Toyta, totoya).
  • Missing values (customer age, vehicle year).
  • Suspicious entries (negative revenue, units sold = -1)
  • Duplicate or inconsistent IDs (ORD1020 appears twice).
  • Mixed numeric formats (0.08M vs 80,000).
  • Text in numeric fields (Discount “ten percent”).
  • Outliers (Customer Age = 121).
  • Invalid dates (31/02/2026).

Using Power Query, the task involved the following:

  • Standardized date formats.
  • Converted all monetary values into Kenya Shillings (KES) using Central Bank exchange rates.
  • Corrected spelling and standardized categories.
  • Flagged unusual records instead of deleting them.
  • Created a clean, analysis‑ready model.
  • Normalize text (uppercase/lowercase, spelling corrections).
  • Convert numeric fields to proper data types.
  • Replace or flag missing values.
  • Standardize categorical values (Payment Status, Delivery Status).
  • Create calculated fields if needed (e.g., Gross Profit = Revenue – Cost – Logistics).

Data Modelling

I reorganized the flat file into:

  • Fact Table: Orders (sales, costs, revenue, logistics).
  • Dimension Tables: Customers, Vehicles, Branches, Sales Reps, Dates.

Relationships were defined using Order ID, Customer ID, and Branch ID.

DAX Measures

Key measures included:

Total Revenue = SUM(fact_orders[RevenueRecorded])

Gross Profit =
SUMX(
    fact_orders,
    fact_orders[RevenueRecorded] - fact_orders[UnitCost] - fact_orders[LogisticsCost]
)

Gross Profit Margin = DIVIDE([Gross Profit], [Total Revenue], 0)

Return Rate =
DIVIDE(
    CALCULATE(COUNTROWS(fact_orders), fact_orders[Returned] = "Yes"),
    COUNTROWS(fact_orders),
    0
)
Enter fullscreen mode Exit fullscreen mode

These measures allowed management to track profitability, efficiency, and customer behavior.

Executive Dashboard

The one‑page dashboard highlighted:

KPIs:

Revenue (KES 1.44B), > Units Sold (458), > Orders (276), > Avg Order Value (KES 5.6M).

Trends: Monthly revenue fluctuations.

Breakdowns: Revenue by Region, Vehicle Type, Sales Rep, Lead Source.

Alerts: High cancellations, unusual logistics costs.

order dashboard

Detailed Reports

Additional pages allowed deeper investigation:

Regional Performance: Rift Valley leads with 25.3% of revenue.

Vehicle Performance: SUVs dominate with 57.8% of revenue.

Sales Rep Analysis: Faith Achieng contributes 14.5% of revenue.

Lead Source Analysis: Instagram and Facebook drive the highest sales.

Returns & Cancellations: Certain models (e.g., Toyota LC200, Subaru XV) show higher return rates.

Insights

SUVs are the backbone of JCars revenue, but pickups and sedans provide stronger margins.

Rift Valley dominates regional sales, while Nairobi suffers from cancellations.

Digital channels outperform traditional lead sources — Instagram and Facebook are critical.

Top reps drive disproportionate revenue — Faith Achieng alone contributes 14.5%.

Future‑dated and cancelled orders distort reporting, requiring stricter validation.

Recommendations

  • Investigate Nairobi operations to reduce cancellations and improve delivery reliability.
  • Expand SUV inventory but balance with pickups and sedans for profitability.
  • Invest further in digital marketing, especially Instagram and Facebook.
  • Replicate top rep practices through training and mentorship.
  • Strengthen data governance to prevent future‑dated and erroneous entries.

Conclusion

This project demonstrates how Power BI can transform raw, inconsistent data into a decision‑support tool. By cleaning, modelling, and visualizing the dataset, JCars Logistics now has a clear view of performance drivers, risks, and opportunities.

The journey from raw CSV to polished dashboard highlights the importance of data quality, modelling discipline, and actionable insights in business intelligence.

Top comments (0)