1. Introduction
Business data is only useful when it can support better decisions.
For JCars Logistics, vehicle sales records contain information about customers, vehicles, branches, payments, deliveries, revenue and costs. However, the original dataset contained inconsistencies that could affect the reliability of analysis.
This project uses Power BI to transform the raw vehicle-sales data into a structured business intelligence solution.
The analysis focuses on four main areas:
- Sales performance
- Financial performance
- Operational efficiency
- Customer and market behaviour
Rather than looking only at total sales, the project investigates where revenue is coming from, where costs are affecting profitability, how branches are performing, and which transactions require further attention.
Project Objective:
To transform raw vehicle sales data into an interactive Power BI solution that helps management understand performance, identify operational issues and make data-informed decisions.
2. Understanding the Dataset
The original dataset contained 276 transaction records and 32 columns.
Each row represents one vehicle sold in one sales transaction.
The dataset contained information from several areas of the business:
| Business Area | Examples |
|---|---|
| Transactions | Order ID, Order Date, Delivery Date |
| Customers | Customer Name, Customer Type, Age |
| Vehicles | Make, Model, Vehicle Type, Fuel Type |
| Geography | Region, County, City, Branch |
| Sales | Sales Representative, Lead Source |
| Finance | Selling Price, Cost, Discount, Revenue |
| Operations | Delivery, Logistics, Payment |
| Customer Experience | Rating, Reviews |
The transaction-level grain was retained during the transformation process.
3. Data Quality Assessment
Before creating the Power BI model, the dataset was assessed for data-quality problems.
Several issues were identified, including:
- Inconsistent Order ID formats
- Multiple date formats
- Invalid customer ages
- Multiple currencies
- Inconsistent category names
- Incorrect or questionable zero values
- Invalid vehicle years
- Inconsistent discount formats
- Invalid customer ratings
- Branch and yard naming differences
- Spelling errors in sales representative names
For example, customer ages included unrealistic values such as 0, 5, 121 and -5.
The dataset also contained financial values in KES, USD, EUR and ZAR, making currency standardisation necessary before financial analysis.
4. Data Cleaning Approach
The cleaning process was based on the business meaning of each field rather than applying one rule to every column.
Examples of Cleaning Decisions
| Data Issue | Action Taken |
|---|---|
| Inconsistent Order IDs | Standardised the identifier format |
| Invalid dates | Converted valid dates and changed unrecoverable values to null |
| Unrealistic ages | Changed unreliable values to null |
| Multiple currencies | Converted monetary values to KES |
| Category inconsistencies | Standardised spelling, casing and abbreviations |
| Invalid vehicle years | Corrected values where evidence existed; otherwise used null |
| Invalid discounts | Unreliable values were changed to null |
| Invalid ratings | Converted valid text formats and removed unreliable values |
| Sales representative spelling errors | Matched names against the available representative list |
The objective was not simply to make the dataset look clean, but to make the cleaned values suitable for analysis.
5. Currency Standardisation
All monetary values were converted to Kenya Shillings (KES).
The exchange rates used were:
| Currency | Rate to KES |
|---|---|
| USD | 129.54 |
| EUR | 147.84 |
| ZAR | 7.93 |
The corrupted ? currency symbol was investigated by comparing the affected values with related financial information. The values were treated as USD where the surrounding evidence supported that interpretation.
Example Power Query Function
fnMoneyKES = (v as any) as nullable number =>
let
raw = Text.Trim(Text.From(v) ?? ""),
upperRaw = Text.Upper(raw),
currency =
if Text.Contains(upperRaw, "KES")
or Text.Contains(upperRaw, "KSH") then "KES"
else if Text.Contains(raw, "$")
or Text.Contains(upperRaw, "USD") then "USD"
else if Text.Contains(upperRaw, "EUR") then "EUR"
else if Text.Contains(upperRaw, "ZAR")
or Text.StartsWith(Text.Trim(raw), "R ") then "ZAR"
else if Text.Contains(raw, "?") then "USD"
else "KES",
numText = Text.Select(raw, {"0".."9", "."}),
numVal = try Number.From(numText) otherwise null
in
if numVal = null then null
else if currency = "USD" then numVal * 129.54
else if currency = "EUR" then numVal * 147.84
else if currency = "ZAR" then numVal * 7.93
else numVal
6. Data Validation
After cleaning, additional checks were performed to identify inconsistencies that could affect the analysis.
One important validation involved comparing:
Payment Status
↓
Delivery Status
This identified 14 inconsistent records:
-
10 records were marked as
Paidbut hadDelivery Cancelled -
4 records were marked as
Payment Cancelledbut hadDelivered
These records were not automatically changed because there was insufficient evidence to determine which field was incorrect.
Instead, they were flagged for further investigation.
Other validation checks included:
- Comparing Car Model with Vehicle Type
- Checking Branch and City relationships
- Checking Sales Representative and Branch relationships
- Investigating missing Vehicle Type values
- Reviewing financial values across related columns
7. Data Model
The cleaned flat table was transformed into a Star Schema.
The model contains:
Fact Table
FactSales
Dimension Tables
DimDateDimCustomerDimVehicleDimLocationDimSalesRepDimPaymentDimLeadSourceDimDeliveryStatus
8. Model Structure
DimDate
|
|
DimCustomer ---- FactSales ---- DimVehicle
|
|
DimLocation
|
|
DimSalesRep
|
|
DimPayment
|
|
DimLeadSource
|
|
DimDeliveryStatus
The relationships were configured mainly as:
Dimension [1] ───────── [*] FactSales
where [1] represents the one side and [*] represents the many side.
9. FactSales Table
The FactSales table contains transaction-level information.
| Column | Role |
|---|---|
| OrderID | Transaction identifier |
| OrderDate | Transaction date |
| DeliveryDate | Delivery date |
| CustomerID | Foreign Key |
| VehicleID | Foreign Key |
| LocationID | Foreign Key |
| SalesRepID | Foreign Key |
| PaymentID | Foreign Key |
| LeadSourceID | Foreign Key |
| DeliveryStatusID | Foreign Key |
| Returned | Transaction attribute |
| UnitsSold | Measure |
| UnitSellingPrice | Measure |
| UnitCost | Measure |
| DiscountPct | Measure |
| DeliveryFee | Measure |
| LogisticsCost | Measure |
| RevenueRecorded | Measure |
| CustomerRating | Measure |
| ReviewCount | Measure |
10. Dimension Tables
| Dimension | Primary Key | Main Attributes |
|---|---|---|
DimDate |
Date | Year, Quarter, Month, MonthYear |
DimCustomer |
CustomerID | Customer Name, Type, Age |
DimVehicle |
VehicleID | Make, Model, Type, Year, Fuel |
DimLocation |
LocationID | Region, County, City, Branch |
DimSalesRep |
SalesRepID | Sales Representative |
DimPayment |
PaymentID | Payment Method, Status |
DimLeadSource |
LeadSourceID | Lead Source |
DimDeliveryStatus |
DeliveryStatusID | Delivery Status |
11. Customer Data Limitation
The source dataset did not contain a unique customer identifier.
Therefore, the customer dimension was created using a combination of:
Customer Name
+
Customer Type
+
Customer Age
This means that customer-level findings should be interpreted carefully because the dataset does not provide a verified master customer list.
12. DAX Measures
Several DAX measures were created to support the analysis.
Total Revenue
Total Revenue =
SUM(FactSales[RevenueRecorded])
Total Gross Profit
Total Gross Profit =
SUMX(
FactSales,
FactSales[RevenueRecorded]
- (FactSales[UnitsSold] * FactSales[UnitCost])
)
Gross Profit Margin
Gross Profit Margin % =
DIVIDE(
[Total Gross Profit],
[Total Revenue]
)
Return Rate
Return Rate % =
DIVIDE(
CALCULATE(
COUNTROWS(FactSales),
FactSales[Returned] = "Yes"
),
COUNTROWS(FactSales)
)
Logistics Cost Percentage
Logistics Cost % of Revenue =
DIVIDE(
[Total Logistics Cost],
[Total Revenue]
)
13. Business Questions
Instead of designing the dashboard around visuals first, the analysis was driven by specific business questions.
Question 1
Which vehicle categories generate high revenue but weak profitability?
Question 2
Which regions and branches contribute the most revenue?
Question 3
Is there an observable relationship between customer ratings and transaction value?
Question 4
Which locations experience higher delivery times or logistics costs?
Question 5
Which transactions contain conflicting payment and delivery information?
Question 6
How concentrated is revenue among high-value customers?
14. Dashboard Structure
The Power BI report was divided into five analytical pages.
Page 1 — Business Overview
The first page provides a high-level summary of the business.
Key visuals
- Total Revenue
- Gross Profit
- Gross Profit Margin
- Return Rate
- Revenue Trend
- Revenue by Region
- Revenue by Vehicle Make
Main question
How is the business performing overall?
Page 2 — Product & Sales Performance
This page focuses on vehicle sales.
Analysis includes
- Vehicle Make
- Vehicle Model
- Vehicle Type
- Units Sold
- Revenue
- Gross Profit
- Gross Profit Margin
Main question
What are we selling, and are those sales profitable?
Page 3 — Regional & Branch Analysis
This page examines geographical performance.
Hierarchy
Region
↓
County
↓
City
↓
Branch
Metrics
- Revenue
- Gross Profit
- Logistics Cost
- Delivery Time
- Units Sold
Main question
Where is the business performing well, and where are operational issues appearing?
Page 4 — Customers & Sales Channels
This page focuses on customer and sales performance.
Analysis includes
- Customer Revenue
- Customer Ratings
- Lead Sources
- Sales Representatives
- Payment Methods
- Customer Type
Main question
Who is generating value and how are customers reaching the business?
Page 5 — Operations & Exceptions
This page focuses on records that require further investigation.
Analysis includes
- Payment/Delivery mismatches
- Delivery duration
- Logistics Cost %
- Returned vehicles
- Cancelled transactions
Main question
Which operational records require management attention?
15. Key Findings
Revenue Concentration
The cleaned dataset recorded approximately:
KSh 1.48 Billion in total revenue
Toyota contributed the largest share among vehicle makes.
Rift Valley and Central were also major contributors to regional revenue.
This indicates that overall revenue is influenced by both vehicle demand and regional performance.
Customer Revenue Concentration
The top 10 customers generated approximately:
KSh 299.3 Million
This represents approximately 20% of total revenue.
This provides an opportunity to monitor high-value customers separately from the wider customer base.
Revenue vs Profitability
High revenue did not always translate into positive profitability.
| Vehicle Type | Gross Profit Margin |
|---|---|
| SUV | 8% |
| Sedan | -9% |
| Crossover | -6% |
| Van | -28% |
| Truck | -66% |
SUVs generated the highest revenue among the vehicle types at approximately KSh 845 Million, while some categories recorded negative margins.
These results should be interpreted alongside the data-quality limitations identified during cleaning.
16. Delivery Performance
The analysis identified a noticeable difference in average delivery time.
| Metric | Days |
|---|---|
| Nairobi Average Delivery Time | 26.29 |
| Overall Average Delivery Time | 15.18 |
Nairobi therefore requires additional operational investigation, particularly when delivery time is considered alongside logistics costs.
17. Exception Analysis
One of the most important findings was the presence of conflicting payment and delivery statuses.
14 Inconsistent Transactions
│
├── 10 → Paid + Delivery Cancelled
│
└── 4 → Payment Cancelled + Delivered
These records were not changed automatically.
Instead, they were flagged for investigation because the available information did not establish which status was incorrect.
18. Vehicle Profitability Concerns
Several vehicle categories and makes showed negative margins.
Examples identified in the analysis include:
| Vehicle Make | Gross Profit Margin |
|---|---|
| BMW | -37% |
| Isuzu | -31% |
The results should be investigated against:
- Unit Cost
- Selling Price
- Currency Conversion
- Discounts
- Missing Financial Values
This is important because data-quality issues can influence profitability calculations.
19. Management Focus Areas
Based on the analysis, several areas deserve further investigation.
1. Vehicle Pricing
Review vehicle categories recording negative margins to understand whether acquisition costs, discounts or selling prices are contributing to the results.
2. Payment and Delivery Reconciliation
Review the 14 inconsistent transactions against the original business records.
3. Nairobi Delivery Operations
Investigate the reasons behind the relatively long average delivery time and associated logistics costs.
4. High-Value Customers
Monitor the contribution of high-value customers to understand customer concentration.
5. Data Collection
Improve future data capture by introducing:
- Unique Customer IDs
- Standardised currencies
- Validated dates
- Controlled category fields
- Consistent payment and delivery statuses
20. Project Limitations
The analysis has several limitations.
Customer Identification
There was no unique customer identifier in the source data.
Missing Financial Information
Some Unit Cost and Unit Selling Price values could not be reliably recovered.
Conflicting Operational Records
Some payment and delivery statuses were inconsistent.
Source Dataset
The analysis represents the information available in the supplied dataset and may not capture every factor affecting actual business performance.
21. Lessons Learned
This project reinforced an important principle:
Data cleaning is part of data analysis, not a separate task.
A value cannot always be judged as incorrect simply because it looks unusual.
Sometimes another column provides the context needed to understand it.
For example:
Unclear Currency
↓
Compare Financial Fields
↓
Determine Most Supported Interpretation
Another example:
Vehicle Type Missing
↓
Check Car Model
↓
Compare With Similar Records
↓
Determine Whether Inference Is Supported
And:
Payment Status
↓
Compare With Delivery Status
↓
Identify Possible Exceptions
The project therefore highlighted the importance of making cleaning decisions based on evidence.
When there was not enough evidence to confidently correct a value, it was safer to use null or flag the record for investigation.
22. Conclusion
The JCars Logistics dataset provided an opportunity to examine the business from several perspectives.
Using Power Query, Power BI data modelling, DAX and interactive visualisations, the raw transaction data was transformed into a structured analytical solution.
The final report connects:
Sales
↓
Revenue
↓
Costs
↓
Profitability
↓
Operations
↓
Customer Experience
The project demonstrates how Power BI can transform raw business records into an interactive decision-support tool.
More importantly, it shows that meaningful analytics depends not only on creating attractive dashboards, but also on understanding the data, documenting assumptions, validating relationships and questioning unusual results.
Top comments (0)