Navigating the KES 1.85B Revenue Reality: An Enterprise Power BI Solution for JCars Logistics

How Data Auditing and Custom DAX Uncovered a 70.46% vs 10.64% Regional Margin Gap and a 68-Day Fulfillment Delinquency.

Executive Summary and Business Context

J-Cars Logistics is a major automotive logistics and dealership enterprise in Kenya, specializing in importing, marketing, and delivering imported and local vehicles to corporate, commercial, and individual clients across 7 geographic regions (Central, Coast, Eastern, Nairobi, Nyanza, Rift Valley, and Western). The business operates through 8 main branches located in Thika, Kakamega, Nakuru, Nairobi, Kisumu, Mombasa, Eldoret, and Athi River, supported by a network covering 11 cities and 8 counties.

The raw, unclean flat table provides was transformed into an enterprise grade Power BI decision support solution by the team. The dataset contained multiple anomalies, multicurrency transactions, unstandardized text, attributes, and invalid dates undermining management's ability to evaluate true operational profitability and logistical performance.

The article presents, complete architectural journey: from data auditing and Power Query ETL transformations, to modelling, advanced DAX logic development, Dashboard design, and strategic recommendations founded in verified data performance.

Performance Summary

Dataset Grain and Quality Audit

Before modelling, a thorough audit was executed defining the grain and underlying anomalies. The dataset grain was established as one row per sales transaction item, representing a sales order line item containing all details pertaining the transaction; order details, branch details, customer details, sale rep details, payment details, delivery details, rating, and reviews attributes.

The following were critical data quality issues uncovered that posed a challenge to the management in reporting accurately if unattended.

1. Multicurrency Inconsistencies: Monetary fields(Unit Selling Price, Unit Cost, Logistic Cost, Delivery Fees) contained mixed currencies. The currencies were KES for Kenya, USD and $ for US dollar, EUR for the Euro, and ZA/ZAR/R for the South African Rand. Solved the issue by standardizing all non Kenyan Currency using the documented CBK average for the exchange rates in the year 2026, by creating an exchange rate table with the currency symbol and rate columns and splitting the fields using the digit and non digit values to obtain amount value and currency symbol. Those without currency symbol were assumed as KES. Also, formatting of some unit values were indicated i.e. as 1.4M implying they were in millions, we used a helper column to get a multiplier i.e. for any Value ending in M the Multiplier would be 1000000, K = 1000, else 1.
2. Negative and Zero Selling Prices, Unit Cost, Logistic Cost Delivery Fees, and Revenue Recorded: Test entries or erroneous records showing Zero/negative selling prices were flagged and excluded from core revenue measures. The following steps were followed for each:

Unit Selling Price
A negative Unit Selling Price usually means on of the following:
a. A Customer Return/Refund
b. A promotional credit.
c. a data corruption error

We were not provided with sufficient information to rule in favor of promotional credit. However, we had to determine for true refunds and find corrupted data. Before that we need to know and understand how to handle the errors.

Decisions

1. Legitimate Returns: Keep the rows and fix the Math. Total revenue will be correct, but your Unit Cost metrics will be skewed. To fix make the Selling price positive and shift the negative sign to the quantity column.

if [Unit Selling Price] < 0 then ([Unit Sold] * -1) else [Unit Sold] // Create a corrected Unit Selling Price column by converting it to an absolute value Number.Abs([Unit Selling Price]) 

Remember to account for this while performing aggregations.

1. If both the Unit Selling Price and Units Sold are null, remove the records
2. Removed rows: Both Units sold and Unit Selling price is null where only 1 row was affected
3. Null Selling Price(1 row)
4. Errors (9 rows affected)
5. Units Sold is null (6 rows)

A total of 17 rows were removed.

Logistic Costs
Zero Logistics Cost in a data source, it usually implies one of two scenarios: a Free Shipping Promotion offered to the customer, or a System Drop-ship/Pick-up where no internal shipping occurred.
To handle the issues, we kept the following in mind:

• Legitimate Reversals: kept them negative if it was a product return. This offset total expenses and allows us to calculate true net logistics spend.
• Outflow Typos(Convert to positive): If a row was negative and it was a standard forward shipment but logged as negative it was considered a formatting error, thus converted to it is absolute value. -In the case of pure corruption: Converted to null.

Unit Cost
Required a distinct approach to handle simply because they can stem from completely different issues:

1. Negative: Are almost always data corruption or a system error since a product can not cost less than nothing to produce.
2. Zero Cost: Also most always legitimate such as giveaways, samples, bundle items, or inventory adjustments. However, if a standard item has 0 cost, it will artificially inflate profit margins.

To handle the errors, for negative Unit Cost we converted them too null.

Delivery Fee
For all negative delivery fees we removed them from the data. A total of 7 rows were affected.

After making all these changes, we removed all extra dirty columns to clean our data.

Recorded Revenue
From our findings, Recorded Revenue was a calculated field. After cleaning all our monetary values we calculated recorded revenue, handling it using the following DAX function in the Power BI editor and having it in a new column:

Revenue Recorded =  (FactOrders[Unit Selling Price(KES)]*FactOrders[Units Sold]*(1-FactOrders[Discount])) + FactOrders[Delivery Fees(KES)] 

1. Date Format Mismatches: Mixed date strings (DD/MM/YYYY vs MM/DD/YYYY). Parsed explicitly in Power Query using Locale setting (English - Kenya). Other errors were replaced with null after setting the correct type for both the Order Date and Delivery Date. The following custom M code handled the date issue i.e. for the Order Date by creating a new column. Follow the comments in the code for the steps:

// 1. Clean the text, but keep slashes/hyphens for now rawTxt = Text.Clean(Text.Trim(Text.From([Order Date]))), // 2. Try standard parsing first (handles Excel serial numbers and system-matching dates) parseStandard = try Date.From(Number.FromText(rawTxt)) otherwise try Date.FromText(rawTxt) otherwise null, // 3. If standard parsing fails, strip out hyphens, slashes, and dots to inspect the raw digits cleanDigits = Text.Select(rawTxt, {"0".."9"}), len = Text.Length(cleanDigits), // 4. Conditional logic to extract components from the raw digits parseCustom = if parseStandard <> null then parseStandard else if len = 8 and (Text.StartsWith(cleanDigits, "20") or Text.StartsWith(cleanDigits, "19")) then // Handles YYYYMMDD (Assumes Year first) try #date(Int16.From(Text.Start(cleanDigits, 4)), Int16.From(Text.Middle(cleanDigits, 4, 2)), Int16.From(Text.End(cleanDigits, 2))) // Alternate backup: If Month > 12, handles YYYYDDMM otherwise try #date(Int16.From(Text.Start(cleanDigits, 4)), Int16.From(Text.End(cleanDigits, 2)), Int16.From(Text.Middle(cleanDigits, 4, 2))) otherwise null else if len = 8 then // Handles MMDDYYYY (Assumes Year is at the end) try #date(Int16.From(Text.End(cleanDigits, 4)), Int16.From(Text.Start(cleanDigits, 2)), Int16.From(Text.Middle(cleanDigits, 2, 2))) // Alternate backup: If Month > 12, handles DDMMYYYY otherwise try #date(Int16.From(Text.End(cleanDigits, 4)), Int16.From(Text.Middle(cleanDigits, 2, 2)), Int16.From(Text.Start(cleanDigits, 2))) otherwise null else null in parseCustom 

1. Missing Transmission, Color, Returned, Customer Name, and Vehicle Year, Region, Customer Type, Sales Rep, Lead Source, Car Make, Transmission, Vehicle Type, Fuel Type, Color, Payment Method, Delivery Status, Payment Status, City, Branch, County, and City Naming Variations: Null values in Transmission, Color, Returned and Customer Name columns were Categorized as Not Provided and Unknown respectively to prevent aggregation loss. For the Vehicle year it was set to text type and the missing values categorized as null.

Branches

Some branches had name variations i.e. one branch would be referred to different names but practically it was one branch. The variations were rectified and one branch would be referred to only one standard name. Example: Eld, Eldoret, Eldoret Branch were referring to the Eldoret branch. We replaced these values with Eldoret. Similarly, we did this for the other branches.

County

One county would also be referred to more than one name with spelling variation i.e. Kambu and Kiambu County were referring to the county government of Kiambu. We Replaced values with only Kiambu

Color
After doing replacement, we used mode to determine between Grey and Green which one we were to use as a replacement value for Gre. We used Grey.

This was also done to other fields that had similar issues replacing them with more relevant values for that particular columns. This was essential not to affect aggregations and other actions performed.

1. Mixed and Incorrectly Formatted Order ID: The Order ID was incorrectly formatted as a text field where non digits and digit values were combined for some IDs while some had either the digit part or the non digit part only and some blank. After observations, the field had a sequential digit part starting at 1000. To solve this, we decided to use only the digit part to fill down the Order ID column by creating a new Custom Index Column.

Steps
In Power Query

• On the Ribbon click on Add Columns
• From the General group, click on the drop down arrow Index Column
• Choose Custom instead of From 0 or From 1
• On the Dialog box specify Starting Index = 1000, and the Increment = 1 and click OK. Our IDs will begin at index 1000 and be incremented by 1. Getting the index range of 1000 - 1275 for our Order ID column.
• Remove the old formatted Order ID, rename the new Index Column to Order ID

1. Delivery Status Discrepancies: Orders marked 'Delivered' or 'Yard' but showing empty delivery fees the delivery fee was set to 0.
2. If the Delivery Fee = null and Delivery Fee < 0 and the Delivery Status = "Cancelled" and Delivery Status = "Held" then Delivery Fee = null.
3. If Delivery Fee = null and Delivery Status = "Yard" then Delivery Fee = 0
4. Else Delivery Fees

We removed all errors and nulls in the Delivery Fees column affecting a total of 9 rows.

1. Incorrect type for Discount, Rating, Reviews Count, and Age: Before converting to the correct type, we had to Trim and Clean the fields to get rid of leading and trailing whitespaces and extra spaces. Then for:

Discount
To handle the field was very critical. First, we hard to Standardize the Text to Numbers by creating a custom column, that detects if a value ends with a "%" sign, strips it, divides it by 100, and otherwise parses the value as a decimal.
Use the following procedure:

• Open Power Query Editor (click Transform Data in Power BI).
• Go to the Add Column tab and click Custom Column.
• Name your column (e.g., Cleaned Discount) and use the following M formula:

let // Remove any accidental leading/trailing spaces(Trim) CleanDisc = Text.Trim([Discount]), // Check if the value ends with a percentage sign IsPercent = Text.EndsWith(CleanDisc, "%"), // Extract the numeric part of the text RawValue =  if IsPercent then Text.Start(CleanText, Text.Length(CleanDisc) - 1)  else CleanText, // Convert text to a number (using Invariant Culture to handle dot decimals safely) NumberValue = Number.FromText(RawValue, Culture.Current) in // If it was a percentage text (like "10"), divide by 100 to get 0.10. Otherwise, keep the decimal. if IsPercent then NumberValue / 100 else NumberValue 

• Click OK.
• Change the data type of this new column to Percentage or Decimal Number by clicking the icon next to the column header

Secondly, to handle outliers that is any discount value greater than 100% or greater than 1 is replaced by null in order not to skew the analysis and profit margins. A discount greater than 100% generally means the transaction is invalid.

• If you want to keep the rows but change anything over 100% down to 100%, go to Add Column > Custom Column and use:

if [Cleaned Discount] > 1 then 1 else [Cleaned Discount]

• For discounts less 0, they set to 0

if [Cleaned Discount] < 0 the 0 else [Cleaned Discount]

Finally, we cleaned our data by removing the old Discount field from the data to remain with our cleaned discount field and renamed it to Discount.

Age
The age field was also incorrectly formatted with mixed types. So;

1. Changed the data type to a Whole Number
2. Replaced Error with null
3. Replaced null values with the average age 45
4. Also, replaced age below 18 years with 18. Since in Kenya the minimum age for one to obtain official identification is 18 years, enabling one to make independent decisions and purchases that requires official identification i.e. Tax certificate.

Review Count
For the Review Count column:

1. We Trimmed and Cleaned the text.
2. Replaced ten with 10, N/A and - with blanks.
3. Change type to a Whole Number
4. We converted the negative numbers to absolute values
5. Select the Review Count column
6. On the Ribbon > Transform Tab (Number Group)
7. Click on the Scientific the from the options choose Absolute Value

Assumption: All negative reviews were not counted to be negative counts.

Customer Rating

1. Text Trimmed/Cleaned
2. Replaced Values out of 5 with blanks, Excellent with 5, N/A or - with blank, 4.7/5 with an average of 4.9.
3. Changed type to Decimal Number with 1 decimal place and converted negative values to their absolute equivalent.

Data Modelling

After successful data cleaning, we modelled our data into dimensional and fact tables. We modelled our data into a star schema with seven Dimensional tables and 1 fact table. Modelling reduces redundance and optimizes DAX engine evaluation.

The following tables were created, merged and relationships established:

After creating the tables in our models we established the following relationships as shown on the diagram below.

Note: All relationships enforce 1-to-Many (1:*) cardinality with a single direction filtering (Dimension to Fact), eliminating circular dependencies and ambiguity.

DAX Calculations and Reusable Business Logic

To enforce consistent business rules across all visual elements, DAX measures were constructed. Core DAX formulas used include:

1. Gross Revenue(Create a new custom column)

Gross Revenue = FactOrders[Units Sold] * FactOrders[Unit Selling Price(KES)]

1. Recorded Revenue(Create a new custom column)

Revenue Recorded = (FactOrders[Unit Selling Price(KES)]*FactOrders[Units Sold]*(1-FactOrders[Discount])) + FactOrders[Delivery Fees(KES)]

1. Discount Amount(Create a new custom column)

Discount Amount = (FactOrders[Unit Selling Price(KES)]/(1 - FactOrders[Discount])) - FactOrders[Unit Selling Price(KES)]

1. Net Profit Margin(New custom column)

Net Profit Margin = FactOrders[Revenue Recorded] - ((FactOrders[Units Sold]*FactOrders[Unit Cost(KES)])+FactOrders[Logistic Cost(KES)]+FactOrders[Delivery Fees(KES)])

1. Volume Quarter 1 2025

VolumeQtr125 = CALCULATE(SUM(FactOrders[Units Sold]), AND(QUARTER(FactOrders[Order Date]) = 1, YEAR(FactOrders[Order Date]) = 2025), NOT(ISBLANK(FactOrders[Order Date])))

1. Percentage Volume Quarter 1(Compare between Q1 2025 and Q1 2026 Performance. Create a new measure)

VolumePercentageQ1 = (([VolumeQtr126]-[VolumeQtr125])/[VolumeQtr125])

1. Total Revenue Recorded

TotalRevenueRecorded = SUM(FactOrders[Revenue Recorded])

Dashboard

After cleaning and the required aggregations, we designed the following executive dashboard to help the executive understand, draw insights for informed decision making.

Business Questions and Findings

Question 1: What is the overall general business performance and comparisons?

The business had:

• Total Recorded Revenue: A revenue of 1.77Bn was recorded with 2025 recording KSh. 1.12Bn(63.36%)8 and 2026 recording *0.KSh. 53Bn(30.63%) of the total revenue recorded. Only 6.11% equating to KSh. 0.1Bn of the total revenue was not classified to either of the years. Thika branch recorded the most recorded revenue of KSh. 385.02 million compared to the Athi River branch which recorded the lowest revenue of KSh. 158.42 million. By region the Central region had the highest revenue accounting for KSh. 385.02 million compared to the Eastern Region with a revenue of KSh. 158.42 million.
• Profit and Profit Margin: A net profit of KSh. 447.52Mn was recorded. Nairobi branch recorded 151.37Mn compared to Eldoret branch with a 21.34Mn profit. Grace Njeri earned KSh. 141.26M while Kevin Mwangi earned KSh. 8.59M in profits.
• Order to Delivery Days(OTD): An overall average of 23 days were required for an order to be delivered. However, Nairobi region had the highest OTD of 40 days while Central had 17 days. By channel, Facebook had the highest OTD, 32 days and Instagram had 16 days.
• Cancellation Rate:* An average cancellation rate of 7.35% was observed. Nairobi branch had an historical cancellation of 15% the highest while Mombasa had 2.78%. By channel, Tenders had the highest cancellation rate of 18.75% while Instagram had 2.94%.
• Transit Days: An average of 26 days compared to 23 days of OTD.
• Orders: A total of 245 orders were made, 408 units were sold. Referrals had the highest number of units sold, 61 compared to WhatsApp. Toyota pushed 116 units about 25% of the total units sold while BMW sold 11 unit which is about 2.7%.
• Discount and Discount Margins: Toyota contributed to the highest discount amount of 34.46M while Isuzu contributed to the lowest discount amount of 1.73M.
• Customers: A total of 245 customers who made a total of 245 orders purchasing 408 units across a period of about 2 years.

Question 2: What is the relationship between Discount Amounts and Profit Margin across branches?

Analysis revealed that high discounting does not correlate with higher net profit. Thika issued Ksh. 14.76M in discounts while maintaining a 35.91% margin due to premium vehicle mix. In contrast, Nakuru issued Ksh. 15.49M in discounts but achieved only a 12.42% margin, demonstrating margin erosion from excessive price concessions on low-margin inventory.

Question 3: Which sales channels exhibit severe delivery latencies and high logistics cost ratios?

WhatsApp leads generate high unit volume (35 units) but suffer from an extreme Average Order-to-Delivery latency of 68 days—more than double the company average of 26 days. Tenders also exhibit a high cancellation rate (18.75%) and long transit times (42 days).

Question 4: How does vehicle performance vary when evaluated by Profit Margin Tier vs. Volume Share?

Volkswagen models represent only 4.17% of total units sold (17 units) but generate an extraordinary 77.19% profit margin (Ksh 126.50M net profit). Conversely, Toyota accounts for 28.43% of total volume (116 units) with a solid 29.21% margin (Ksh 201.98M net profit). Nissan and Mitsubishi represent 'fleet volume' categories with profit margins below 10%.

Question 5: Is customer rating correlated with sales representative revenue productivity?

Interestingly, top revenue producer Faith Achieng (Ksh 347.30M revenue) holds a moderate customer rating of 3.37, while Peter Kiptoo holds the lowest rating (2.98) alongside low profitability (Ksh 15.49M). High-performing rep Grace Njeri maintains both strong revenue (Ksh 295.00M) and solid margins (Ksh 141.26M profit).

Question 6: What is the impact of payment methods on transaction cancellation and cash flow stability?

Mpesa is the dominant payment channel by volume (101 units, 24.75%) and value (Ksh 513.17M, 29.06%). Asset Financing accounts for 66 units (Ksh 219.51M) but exhibits higher processing delays, impacting delivery completion rates.

Key Management Insights

Insight 1: Premium Trim Pricing Power Drive Executive Margin Growth

Volkswagen (77.19% margin, Ksh 126.50M profit) and Toyota (29.21% margin, Ksh 201.98M profit) generate the vast majority of company net profit. High-end SUV trims (Prado, Harrier, CX-5, C200, E250) exhibit strong pricing power, whereas hatchback/sedan volume models (Fit, Demio, Axio) operate on slim margins below 10%.

Insight 2: Severe Regional Profit Disparity Between Nairobi and Rural Branches

Nairobi branch achieves an unparalleled 70.46% profit margin (Ksh. 151.37M profit on Ksh. 214.84M revenue), driven by luxury sales with low delivery overhead. Conversely, Kisumu branch yields a modest 10.64% profit margin (Ksh. 21.34M profit) burdened by Ksh. 11.94M in customer refunds and Ksh. 9.00M in discounts.

Insight 3: High Digital Lead Volume Belied by Severe Logistics Friction

Website (Ksh. 316.25M revenue) and Walk-Ins (Ksh. 300.99M revenue) represent the primary profit drivers. However, social media and messaging channels—specifically WhatsApp—suffer from acute supply chain friction, recording a 68-day average OTD time that severely harms customer satisfaction.

Insight 4: Tender Sales Suffer from Abnormally High Cancellation Rates

Tender sales channel records an alarming 18.75% cancellation rate—more than 2.5 times the company baseline of 7.35%. This stems from protracted procurement timelines (42 days transit) and strict contract specs that fail post-award inspection.

Insight 5: Concentration of Sales Rep Profitability Creates Key-Person Risk

Two sales representatives—Faith Achieng (Ksh. 131.61M profit) and Grace Njeri (Ksh. 141.26M profit)—generate Ksh. 272.87M, or 60.98% of total company net profit. The remaining 8 sales reps combined generate less than 40% of net profits.

Recommendations

Realign Vehicle Inventory Procurement Toward High-Margin Trims

• Shift procurement capital away from low-margin fleet models (Nissan, Mitsubishi) toward high-margin Crossover/SUV models (Volkswagen Tiguan/Golf, Toyota Prado/Harrier, Mercedes C200/E250).
• Expanding high-margin trim volume by 15% will increase total company net profit by an estimated Ksh. 65M annually without increasing overall unit storage overhead.

Overhaul WhatsApp Order Fulfillment & Digital Logistics Workflows

• Establish dedicated digital logistics fulfillment protocols for social and messaging leads (WhatsApp/Instagram) to compress OTD latency from 68 days down to the company benchmark of 28 days.
• Eliminating fulfillment bottlenecks will reduce customer cancellations, decrease refund liabilities (currently Ksh. 15.07M), and elevate average customer rating scores above 4.0.

Enforce Discount Guardrails & Restructure Branch Pricing Governance

• Institute a strict discount approval matrix limiting branch managers to a maximum 5% discount on vehicles yielding margins under 15%. Require executive approval for discounts exceeding Ksh 500,000.
• Curbing unwarranted discounting in Nakuru, Kisumu, and Eldoret will recover an estimated Ksh. 18M to Ksh. 25M in annual profit leakage.

To view and see the project visit my GitHub Page at Jcars Logistics Executive BI Solution