Introduction
As part of my business intelligence coursework, I was given a raw, messy dataset from a fictional company called JCars Logistics, a vehicle sales and delivery business in Kenya. My task was to turn this raw data into a working Power BI solution that management could actually use to understand the business. This article walks through my process: investigating the
data, cleaning it, building a model, writing DAX measures, and designing a dashboard including the mistakes I made along the way.
Understanding the Data
Before touching anything, I looked at what one row in the dataset actually represented: one vehicle sales order. Columns captured customer details, vehicle details, pricing, discounts, delivery information, and payment status. This told me I was dealing with a flat transactional dataset that would eventually need to be split into a proper fact and dimension structure.
Data Quality Audit
Raw data is never clean, and this dataset proved it. Some of the most significant issues I found:
- Negative discount values (e.g., -0.1 instead of 0.1): likely sign entry errors that would have inflated revenue instead of reducing it
- Discount values over 100% (e.g., 1.20): almost certainly misplaced decimals
- "Paid" transactions with blank Revenue Recorded : a direct contradiction, since confirmed payment should mean recorded revenue
- Duplicate Order IDs: 276 rows but only 255 distinct order identifiers
- A negative revenue value paired with an invalid customer rating (-1) on the same row, flagged as an exception rather than guessed at
For each issue, I documented what I found, why it mattered, and how I decided to handle it, full detail is in my GitHub repository's data quality audit document.
Currency Standardization
The dataset contained monetary values in multiple currencies — KES, USD, EUR, and ZAR. This was one of the trickiest parts of the project. I'll be honest: during cleaning, I removed currency symbols to convert text values into numbers, but didn't preserve which currency each row originally came from. This means I couldn't retroactively convert foreign-currency rows to KES with confidence. I've documented this as a known limitation in my project, rather than presenting unconverted figures as if they were fully accurate, an important lesson in why preserving original data markers matters before transforming values.
Cleaning in Power Query
Using Power Query, I:
- Trimmed and standardized inconsistent text (customer names had extra spaces, inconsistent capitalization)
- Fixed data types across numeric and date columns
- Corrected the discount issues found during the audit
- Recalculated missing revenue values for confirmed "Paid" orders using Unit Selling Price × Units Sold × (1 − Discount)
Power Query editor showing applied steps
Data Validation
I didn't assume cleaning automatically meant correctness. I independently recalculated revenue and compared it against the recorded values. The initial discrepancy was tiny (a few hundred KES, clearly rounding), but after investigating further with a row-level comparison, I found a larger residual gap of roughly KES -468.51M (~36% of total revenue). I documented this honestly as an unresolved limitation requiring further investigation, rather than forcing the numbers to match artificially.
Building the Data Model
I organized the flat dataset into a star schema:
- CarSalesFacts (fact table)
- DimCarDetails, DimLocation, DimVehicleSpecs, DimDate(dimension tables)
Model view showing relationships
One real challenge I hit: after a Power BI crash during a transformation step, my relationship between CarSalesFacts and DimCarDetails broke silently; about 96% of rows stopped matching to a car make. I only caught this because a DAX measure threw an error, which led me to rebuild the relationship properly. It was a good reminder to validate relationships after any crash or recovery, not just assume they held.
I also built a dedicated Date table (DimDate), related it to Order Date as the active relationship, and to Delivery Date as an inactive one, switched on only inside specific measures using USERELATIONSHIP, since Power BI only allows one active date path at a time.
DAX Measures
I wrote measures to support reusable business calculations rather than relying on default aggregations, including:
- Total Revenue, Total Cost, Gross Profit, Gross Profit Margin
- Total Orders (using DISTINCTCOUNT to handle duplicate Order IDs correctly)
- Return Rate, Cancellation Rate, Avg Customer Rating
- Revenue YoY % (year-over-year growth, using SAMEPERIODLASTYEAR)
Executive Dashboard
Page 1 is a single-page overview for management: KPI cards for revenue, gross profit, margin, orders, units sold, and avg rating, plus a revenue trend by year, revenue by car make, revenue by region, and an order status breakdown.
Detailed Report Pages
I built three focused pages rather than one visual per question:
- Sales & Revenue : performance by car make/model, branch, sales rep, and lead source
- Customers & Payments : customer type, payment method/status, and rating-vs-revenue relationship
- Logistics & Returns : logistics cost, return/cancellation rates, delivery status by region, and a detailed table of returned/cancelled orders
Key Insights
- Order volume declined sharply from 158 orders in 2025 to 75 in 2026, despite full-year data coverage, a real trend worth investigating, not an artifact of incomplete data.
- Toyota dominates vehicle revenue, led by the Prado TX at KES 195.5M.
- Thika branch outperforms all others, while Nairobi HQ despite being a major hub, underperforms an unexpected pattern worth a closer look.
- SUVs carry the highest logistics cost of any vehicle type.
- Data entry issues (invalid discounts, missing revenue on paid orders) materially affected reported figures before correction.
Recommendations
- Investigate the 2026 order decline, starting with Lead Source and Sales Rep performance.
- Review Nairobi HQ's branch-level performance given its underwhelming results relative to smaller branches.
- Introduce data entry validation rules for Discount and Revenue fields to prevent recurring data quality issues.
What I Learned
This project taught me that cleaning data is never just mechanical, every fix requires a judgment call, and documenting why you made a decision matters as much as the decision itself. I also learned the hard way that currency and identifier information should be preserved before transforming data, and that crashes can silently break relationships in ways that aren't obvious until something downstream fails.
Project Links
- GitHub Repository: [https://github.com/Asma-Salah/jcars-logistics-power-bi.git
- Full data quality audit and assumptions documentation available in the repository.
Top comments (0)