1. Project Background and Business Objective
JCars Logistics imports, sells and delivers vehicles to customers across Kenya. Management requested a Power BI solution to understand the company's overall performance and identify areas that may need attention. The analysis covers sales, revenue, costs and profitability, vehicle performance, branch and regional performance, sales representatives, lead sources, payments, deliveries and logistics, returns and cancellations, customer experience and unusual transactions that may need further investigation.
This report explains the journey from the raw dataset to the final Power BI solution. It covers data cleaning, data modelling, DAX measures, dashboard development and the main insights and recommendations from the analysis.
2. Dataset and Grain
The source data was a single raw flat file containing 32 columns and 276 rows. Each row represents one vehicle sold in one sales transaction. This level of detail was kept throughout the project. No aggregation or splitting was needed when creating the fact table.
The columns were grouped into several main areas:
- Transaction information such as Order ID and dates
- Customer information
- Vehicle information
- Location information such as Region, County, City and Branch
- Sales information such as Sales Rep and Lead Source
- Financial information such as prices, costs, discounts, fees and revenue
- Operational information such as payment, delivery, returns and customer ratings
Screenshot: Raw dataset showing the original columns and sample records before cleaning.
3. Data Quality Audit: Key Issues Identified
More than ten major data quality issues were identified and resolved during the cleaning process. The most important ones are summarised below. The same cleaning approach was applied consistently across the dataset.
| # | Issue Found | Why It's a Problem | How It Was Handled |
|---|---|---|---|
| 1 | Order IDs were recorded in four different formats (LC1000, LCL-1001, ORD1004) |
The same type of identifier had different formats, making it difficult to identify records consistently | The digits were extracted and standardized to ORD-####. The original value was kept separately |
| 2 | Dates appeared in 5+ formats, including Excel serial numbers such as 45671, as well as invalid dates such as 31/02/2026 and April 31 2026
|
The dates could not always be read correctly and some of them did not exist on the calendar | Excel serial numbers were converted using the Excel date system. Day-first formatting was used for ambiguous dates. Invalid or unrecognizable dates such as #DATE!, "not sure" and impossible calendar dates were changed to null |
| 3 | Customer Age contained impossible values such as 0, 5, 121 and -5
|
These values are not realistic for the customer information being analysed | Unrealistic ages were changed to null. Negative signs were removed from otherwise realistic ages where they appeared to be data entry errors |
| 4 | Monetary columns contained 5 different currencies: KES/KSh, USD ($ and text), EUR, ZAR/R and a corrupted symbol (?) |
Values from different currencies cannot be directly compared or added together | The currency was identified for each value and converted to KES. Values without a currency label were treated as KES based on the assessment instructions |
| 5 | Categorical fields such as Customer Type, Car Make, Car Model, Vehicle Type, Fuel Type, Color, Payment Method/Status, Delivery Status, Returned, Region, County, City and Branch had multiple spelling, casing and abbreviation variations | The same category could appear as different categories in the analysis | The values were standardized into one consistent category using evidence from related fields where necessary |
| 6 | Zero values in monetary columns did not always mean the same thing | A KSh 0 selling price for a paid transaction is unlikely to be valid, while KSh 0 revenue for a cancelled transaction can be reasonable | Zero values were assessed separately for each column based on the business context |
| 7 | Vehicle Year included 1899, 2032, 202A and Twenty Twenty
|
These values would affect year-based analysis and some were clearly invalid |
202A was corrected to 2024 based on the Order Date. Twenty Twenty was changed to 2020. 1899 and 2032 were changed to null because there was not enough evidence to recover the correct values |
| 8 | Discount values appeared as percentages, decimals, words and included an outlier of 120%
|
A discount above 100% is not a realistic business value | Values were standardized to decimal format. Negative discounts and discounts above 50% were treated as unrecoverable errors and changed to null |
| 9 | Customer Rating included numbers, "X out of 5", "X/5", "Excellent" and invalid values such as -1 and 6
|
Ratings outside the expected range would affect customer satisfaction analysis | Text formats were converted to numbers. "Excellent" was mapped to 5 and values outside the 0 to 5 range were changed to null |
| 10 | Some locations were labelled as both a "Branch" and a "Yard", such as "Eldoret Branch" and "Eldoret Yard" | A branch and a yard can represent different operational locations rather than spelling variations | The distinction was kept so that branch and yard performance could be analysed separately |
| 11 | Some Sales Rep names contained number-for-letter errors such as "Wanj1ku" instead of "Wanjiku"
|
These errors created separate and incorrect versions of the same sales representative | The corrupted characters were corrected and matched against the verified list of 10 sales representatives |
4. Currency Standardization
All monetary values were converted to Kenya Shillings (KES) so that the report could use one consistent reporting currency. This also followed the assessment instruction that values without a currency label should be treated as KES.
Exchange rates used:
| Currency | Rate to KES |
|---|---|
| USD | 129.54 |
| EUR | 147.84 |
| ZAR | 7.93 |
| GBP | Not applicable, investigated and confirmed absent from the dataset |
The ? symbol that appeared before some monetary values was investigated by comparing the affected Unit Selling Price with the Unit Cost in the same row. The values consistently matched USD-level pricing, so the ? symbol was treated as a corrupted USD symbol rather than making an unsupported assumption.
Zero-value business rules:
- Unit Selling Price / Unit Cost: zero was treated as missing because a vehicle would not normally have zero acquisition or selling value.
- Delivery Fee: zero was accepted because free delivery is possible.
-
Logistics Cost: zero was treated as missing where
DeliveryStatusshowed that the vehicle had actually been delivered. -
Revenue Recorded: zero was accepted where
PaymentStatuswas Cancelled or Refunded. In other cases it was treated as missing.
This was the formula format I used to work on the monetary columns with different currencies:
fnMoneyKES = (v as any) as nullable number =>
let
raw = Text.Trim(Text.From(v) ?? ""),
upperRaw = Text.Upper(raw),
isMissing = raw = "#VALUE!" or raw = "#ERROR" or raw = "-" or raw = ""
or upperRaw = "NULL" or upperRaw = "TBD"
or Text.Contains(upperRaw, "MISSING") or Text.Contains(upperRaw, "NOT"),
hasM = Text.EndsWith(upperRaw, "M"),
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 isMissing or numVal = null then null
else if hasM then Number.Abs(numVal) * 1000000
else if currency = "USD" then Number.Abs(numVal) * 129.54
else if currency = "EUR" then Number.Abs(numVal) * 147.84
else if currency = "ZAR" then Number.Abs(numVal) * 7.93
else Number.Abs(numVal),
5. Data Validation Performed
Several validation checks were carried out after the main cleaning process:
- Cross-checked currency assumptions against Unit Cost and Unit Selling Price values in the same row before finalizing the conversion logic.
- Compared Payment Status and Delivery Status for logical consistency. This identified 14 records where the two fields did not agree. There were 10 records showing "Paid but Delivery Cancelled" and 4 showing "Payment Cancelled but Delivered". These records were kept as they were and flagged for investigation rather than being changed without evidence.
- Checked the two blank Vehicle Type records against their Car Model. Each model had a consistent Vehicle Type in the other records, allowing the missing values to be inferred.
- Checked Branch and City relationships before creating the location hierarchy. Each branch consistently belonged to one city.
- Checked Sales Rep and Branch relationships. Sales representatives worked across multiple branches, which supported creating Sales Rep as a separate dimension.
6. Data Model
I transformed the raw flat file into a star schema consisting of one fact table and eight dimension tables.
Grain of FactSales: one row represents one vehicle sold in one transaction. This is the same grain as the original dataset.
FactSales (Fact Table)
| Column | Role |
|---|---|
| OrderID | Degenerate dimension |
| OrderDate, DeliveryDate | Relate directly to DimDate |
| CustomerID | FK → DimCustomer |
| VehicleID | FK → DimVehicle |
| LocationID | FK → DimLocation |
| SalesRepID | FK → DimSalesRep |
| PaymentID | FK → DimPayment |
| LeadSourceID | FK → DimLeadSource |
| DeliveryStatusID | FK → DimDeliveryStatus |
| Returned | Degenerate attribute |
| UnitsSold, UnitSellingPrice, UnitCost, DiscountPct, DeliveryFee, LogisticsCost, RevenueRecorded, CustomerRating, ReviewCount | Measures |
Dimension Tables
| Table | Primary Key | Columns |
|---|---|---|
| DimDate | Date (direct relate, no primary key needed) | Date, Year, Quarter, Month, MonthYear, Day, DayName, WeekdayNumber, IsWeekend |
| DimCustomer | CustomerID | CustomerName, CustomerType, CustomerAge |
| DimVehicle | VehicleID | CarMake, CarModel, VehicleType, VehicleYear, FuelType, Transmission, Color |
| DimLocation | LocationID | Region, County, City, Branch |
| DimSalesRep | SalesRepID | SalesRep |
| DimPayment | PaymentID | PaymentMethod, PaymentStatus |
| DimLeadSource | LeadSourceID | LeadSource |
| DimDeliveryStatus | DeliveryStatusID | DeliveryStatus |
Relationships: all dimensions relate to FactSales as One-to-Many with single-direction cross-filtering. FactSales relates to DimDate twice, using OrderDate as the active relationship and DeliveryDate as the inactive relationship. The DeliveryDate relationship is activated in specific measures using USERELATIONSHIP.
Model View showing FactSales at the centre and the eight dimension tables connected through One-to-Many relationships, including the two DimDate relationships for Order Date and Delivery Date.
Modelling limitation: the dataset does not contain a unique customer identifier. DimCustomer was therefore built from distinct combinations of CustomerName, CustomerType and CustomerAge.I did this by selecting all the columns in it and removing duplicates.
This means that "customer" in this analysis refers to the customer as recorded per transaction rather than a verified master customer record. Two different people with the same name and similar profile be treated as one customer.
I considered this limitation when presenting customer-level findings.
7. Key DAX Measures
The following are some of the main DAX measures I used in the analysis.
Total Revenue = SUM(FactSales[RevenueRecorded])
Total Gross Profit =
SUMX(FactSales, FactSales[RevenueRecorded] - (FactSales[UnitsSold] * FactSales[UnitCost]))
Gross Profit Margin % = DIVIDE([Total Gross Profit], [Total Revenue])
Return Rate % =
DIVIDE(
CALCULATE(COUNTROWS(FactSales), FactSales[Returned] = "Yes"),
COUNTROWS(FactSales)
)
Logistics Cost % of Revenue = DIVIDE([Total Logistics Cost], [Total Revenue])
Customer Total Revenue =
CALCULATE([Total Revenue], ALLEXCEPT(FactSales, DimCustomer[CustomerName]))
Examples of the measures I created for the analysis by use of DAX.
Business definition: Gross Profit was defined as Revenue minus (Units Sold times Unit Cost).I excluded Delivery Fee and Logistics Cost because I treated them as operating expenses rather than the direct cost of the vehicle.
Limitation: some Unit Cost and Unit Selling Price values could not be recovered during cleaning and were changed to null. Therefore, Gross Profit and Gross Profit Margin may be affected for transactions where cost or price information was missing.
8. Dashboard and Report Structure
| Page | Purpose |
|---|---|
| 1. Executive Dashboard | High-level summary showing KPIs, revenue trend, branch and make comparison, payment mix and a logistics cost attention indicator |
| 2. Vehicle/Branch Performance | Analysis of Make, Model, Vehicle Type and Year performance, branch revenue share and regional drill-down |
| 3. SalesRep/LeadSource Performance | Sales representative performance and lead source value |
| 4. Revenue/Logistics | Payment status and method, delivery performance and logistics cost efficiency |
| 5. TopCustomers | Top 10 customers by revenue and the relationship between revenue and ratings |
Interactivity was also added to the report. Region and Car Make slicers are synced across pages. A Region → County → City → Branch drill-down was created within the matrix visual and visuals on each page can interact through cross-filtering.
The Executive Dashboard showing KPI cards, revenue trend, branch and make comparison, payment status mix and logistics cost attention indicator.
How the dashboard looks like when the slicer only applies to Central region.
9. Investigation: Records Requiring Management Attention
- 14 Payment/Delivery Status inconsistencies: 10 records show Payment Status = Paid while Delivery Status = Cancelled. Another 4 show Payment Status = Cancelled while Delivery Status = Delivered. These records were not corrected because the data does not show which field is inaccurate. They were instead flagged for manual review.
- Truck vehicle type shows a -66% gross profit margin: this is the vehicle type with the most negative margin in the dataset. It should be investigated to determine whether the result comes from a genuine pricing or cost issue or from remaining data quality problems.
- BMW (-37%) and Isuzu (-31%) show negative margins: both makes show significant negative margins even though their selling prices are comparable with other makes. The Unit Cost data for these makes should therefore be reviewed to check whether currency conversion or missing-value handling affected the results.
- Nairobi has the highest average delivery time: Nairobi has an average delivery time of 26.29 days compared with the company-wide average of 15.18 days. Since Nairobi hosts the company's headquarters, this difference is worth investigating to understand what may be causing the longer delivery times.
10. Management Insights
- Revenue is concentrated in a small number of vehicle makes and regions. Toyota accounts for the largest share of revenue among the vehicle makes in the dataset. Rift Valley (KSh 381.9M) and Central (KSh 244.6M) also contribute a large share of the total KSh 1.48bn revenue. This means overall performance is strongly connected to demand for Toyota vehicles and performance in these regions.
Customer revenue is concentrated but not extreme.
The top 10 customers out of 276 account for approximately KSh 299.3M, which is around 20% of total revenue. This shows that a meaningful share of revenue comes from a relatively small group of high-value customers.Some vehicle categories are being sold at negative margins.
SUVs generated the highest revenue among vehicle types at approximately KSh 845M and had a positive margin of 8%. However, Sedans had a -9% margin, Crossovers -6%, Vans -28% and Trucks -66%. Since these are categories with meaningful sales volumes, the results may point to wider pricing or cost issues rather than a few individual transactions. However, these findings should be considered alongside the data quality issues identified during cleaning.Customer ratings do not show a clear relationship with transaction value.
The scatter plot comparing Customer Rating and Revenue Recorded shows that ratings generally cluster around 3 to 4 across different transaction values. There is no clear upward or downward pattern in the data. Based on this dataset, higher transaction values do not appear to be directly associated with higher customer ratings.
- Logistics efficiency varies across branches. Nairobi HQ has the highest Logistics Cost % of Revenue among the branches. Nairobi also has the longest average delivery time at 26.29 days compared with the overall average of 15.18 days. These two findings together suggest that Nairobi's logistics performance deserves further investigation.
Note: Return Rate (31.88%) and Gross Profit Margin (2.53%) were calculated from the cleaned dataset. Both figures should be treated carefully because missing or null values in Unit Cost, Unit Selling Price and Returned may affect the results. Additional source data could therefore change these figures.
11. Management Recommendations
Review pricing and costs for Trucks, Vans, Sedans and Crossovers. These vehicle categories show negative gross margins at meaningful sales volumes. Management should review their selling prices and acquisition costs to determine whether current pricing is covering the cost of the vehicles.
Review the 10 Paid but Cancelled transactions. The 10 records showing Payment Status = Paid and Delivery Status = Cancelled should be reviewed to determine whether customers are owed refunds or whether the delivery status is incorrect.
Investigate Nairobi's delivery times and logistics costs. Nairobi has both a relatively high logistics cost share and the longest average delivery time. Management should investigate the reasons behind this before treating it as normal regional variation. Possible areas to review include traffic, yard-to-customer handoffs, delivery scheduling and available resources.
12. Analyst-Defined Business Questions
In addition to the questions provided in the assessment brief, five additional questions were identified as useful for JCars Logistics management. Three were answered directly by the Power BI analysis.
- Does higher customer satisfaction translate into higher-value sales, or are they unrelated? (Answered: Section 10, Insight 4)
- Which lead source delivers the best return per lead, and is marketing investment allocated accordingly? (Answered via the Avg Revenue per Lead by Lead Source visual)
- Are certain branches carrying disproportionate logistics costs relative to the revenue they generate? (Answered: Section 10, Insight 5)
Does vehicle age affect profitability, suggesting that older stock should be discounted or phased out?
How concentrated is revenue among the top customers, and does this represent a meaningful business risk? (Answered: Section 10, Insight 2)
13. Assumptions and Business Rules Summary
- Monetary values without a stated currency were treated as Kenya Shillings based on the assessment instructions.
- Exchange rates used were USD 129.54, EUR 147.84 and ZAR 7.93 to KES.
- Gross Profit = Revenue minus (Units times Unit Cost). Delivery Fee and Logistics Cost were treated as separate operating expenses.
- "County Government" was standardized under "Government" because the dataset did not provide enough information to distinguish between different levels of government.
- "Retail" was kept as its own Customer Type category rather than being combined with "Individual."
- Branch and Yard designations for the same city were kept as separate locations.
- Ambiguous dates were interpreted using the day-first UK/Kenya convention.
- Ratings outside 0 to 5 and discounts below 0% or above 50% were treated as unreliable and changed to null rather than being manually corrected.
- DimCustomer was created using Name + Type + Age because the source data did not contain a unique customer identifier. Customer-level findings should therefore be interpreted with this limitation in mind.
14. Challenges Encountered and Learning
The biggest challenge in this project was the number and variety of data quality issues spread across the dataset.
Many cleaning decisions could not be made by simply applying the same rule to every column. Instead, related fields had to be compared to understand what the data was actually showing.
For example, Unit Cost and Unit Selling Price were compared when resolving unclear currencies. Car Model was compared with Vehicle Type when filling missing Vehicle Type values. Payment Status was also compared with Delivery Status to identify records that needed further investigation.
For me this project solidified the importance of making data cleaning decisions based on evidence wherever possible. When the available information was not enough to confidently correct a value, the value was left as null or flagged for investigation instead of making an unsupported assumption.
The process also showed that data cleaning is not just about making a dataset look neat. Every cleaning decision can affect the final analysis and the business conclusions drawn from it. Clearly documenting these decisions is therefore an important part of producing a reliable Power BI solution.
Top comments (0)