From Data to Decisions: Power BI Analytics for JCars Logistics

Introduction

• This week the main focus was to analyze Jcars Logistics which is a company that imports, sells, and delivers vehicles to customers across different regions in Kenya. This project seeks to convert a raw, uncleaned flat dataset comprising sales, vehicles, and customer records into a reliable and interactive Power BI solution. The solution will enable management to make informed decisions in the following areas: Sales performance, Profitability, Branch and geographic performance and Customer value.
• In addition, the solution will identify business exceptions and anomalies that warrant further investigation, thereby strengthening oversight and supporting data driven management practices.

Data Study

• First before any cleaning or transformation, I studied the dataset to understand its structure and contents. The dataset contains 32 columns and 276 rows, with each row describing an individual customer.
• The data grain is recorded at the level of an individual customer order so each row represents vehicle sale. Every record captures:
  • Customer details: name, age, customer type
  • Order timeline: the date the order was placed and the date the vehicle was delivered
  • Vehicle details: the make, model, units sold, year of manufacture, fuel type, color, vehicle type
  • Sales representative: the team member who served the customer, lead source
  • Branch: the car yard and its location
  • Financials: unit price, discount status (awarded or not), delivery fee, delivery status and recorded revenue

Data Quality Audit

• Upon first glance on the raw flat table dataset, it looked okay but upon further study I realized that the data needed alot of cleaning due to various inconsistencies on the:

Texts - Misspelled names eg Fielder is represented as Fieldar, Nairobi as Nbr. Inconsistent font case, standardizing Unknown and empty values.

Dates - Order and delivery dates in the dataset are recorded in inconsistent formats, including Excel date serials eg
46178 in stead of 06/05/2026.

Currency - Prices were recorded in different currencies such as Euros, Dollars, ZAR. Monetary value written as 4.2M in stead of 4,200,000. The final report was expected to be in Kenyan Shillings(Ksh).

Discount - Discounts values were recorded in different formats such as 7%, 0.07, ten percent.

A section of the raw dataset as loaded into Power BI, before any cleaning or transformation

Data Cleaning

• Power Query was the primary tool for cleaning the dataset because it keeps every transformation step repeatable, traceable, transparent, and easy to maintain. I used a number of functions to apply changes across multiple records at once rather than handling each record individually, which saved time and ensured the changes were applied accurately and consistently.
  • fnCat
• This function was applied to categorical data. Instead of replacing individual spellings one by one, it used mapping tables to convert recognized variations into standardized categories Example: Kambu➡️Kiambu, Iszu➡️Isuzu
  • fnDate
• This function standardizes the date fields by identifying all invalid formats and converting it to a valid date using the appropriate method and returns null for any value that cannot be parsed so it can be flagged for review.
  • fnMoneyKsh
• The function standardizes all monetary values into Kenyan Shillings (Ksh).Any value without a currency marker was assumed to be Ksh Each amount is converted using the following exchange rates
  • Ksh = 1
  • USD = 129.79
  • EUR = 147.22
  • ZAR = 7.92
• The shorthand notation M was also handled. A value such as 0.03M was interpreted as 30,000 so that large monetary figures were converted to their full numeric values.
  • fnDate
• This function standardizes all age records to ensure that values fall within the range of 18 to 100 years. The 18-year minimum reflects the legal requirement in Kenya that a customer must be an adult to purchase a vehicle, while the upper limit screens out unrealistic entries.
• For the remaining categorical columns such as County, Vehicle Model, Sales Rep, Delivery Status, Branch, and Color, I used mapping tables to apply multiple corrections across all records at once.
• Invalid entries, including "", "n/a", "na", "null", "not sure", "unknown", "tbd", "none", and "-", were identified and standardized. In text columns, these values were replaced with Unknown, while in numeric columns they were converted to Null, so that missing data would not distort calculations.

Data Validation.

• During validation, I flagged questionable records rather than silently assuming values. The following flags were applied:
  • Order/Delivery Date Missing- applied where the order or delivery date was missing.
  • Price Estimated- applied where the unit price was missing and had to be derived.
  • Cost Estimated- applied where the logistics cost, delivery fee, or unit selling price was missing, and the final selling price was determined using the known values.
• When reviewing the initial Revenue Recorded values, I found that some were inaccurate. I therefore derived a new revenue figure for each record, calculated as:

[Units Sold] * [Unit Selling Price] * (1 - ([Discount]) + ([Delivery Fee])

The number of units was included because some customers purchased more than one vehicle. This ensured that revenue reflected the actual value of each transaction.

Data Modelling

• Now the cleaned dataset was in a flat table which would not be ideal for me use in Power Bi for analysis, therefore I chose to adopt a star schema model as an alternative. I first identified the variables to use for my fact and dimension tables before creating the tables.
• I created the following dimension tables for my model:
  • Dim_Customer containing the descriptive customer details,
  • Dim_Vehicle containing the descriptive vehicle details,
  • Dim_SalesRepcontaining the salesrep name
  • Dim_Branches containing the descriptive location details. I deleted all duplicates first and since the source data did not provide unique identifiers, I generated a primary key for each dimension table. These keys were then added to the the fact table as foreign keys, allowing each transaction to be linked accurately to its correspondent.
• The Fact_Table contained the foreign keys of the dimension tables as well as all the other values provided in the initial source data.
• All dimension tables were linked to the fact table using one-to-many relationships, with each dimension on the "one" side and the fact table on the "many" side. Filtering was set to a single direction, which keeps the model simple and ensures that slicers and visuals return accurate results. Star schema data model showing the relationships between the fact and dimension tables.

Development of DAX Measures

• Creating DAX measures was the next step after data modelling was done. I performed the following calculations that established the core financial logic of the dashboard to be created:

Total Revenue
Total Revenue = SUM(Facts_table[Revenue])

Total Orders
Total Orders = COUNTA(Facts_table[OrderID])

Total Logistics Cost
Total Logistics Cost = SUM(Facts_table[Logistics Cost])

Total Vehicle Cost
Total Vehicle Cost = SUMX(Facts_table, Facts_table[Unit Cost]*Facts_table[Units Sold])

Gross Profit
Gross Profit = [Total Revenue]-[Total Vehicle Cost]

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

Total Returned Cars
Total Returned Cars = CALCULATE([Total Orders], Facts_table[Returned]="Yes")

Rate at which cars are returned to the yard
Rate of Return = DIVIDE([Total Returned Cars], [Total Orders])

Total units that have been delivered so far
Total Delivered Units = CALCULATE([Total Orders], Facts_table[Delivery Status] ="Delivered")

Dashboard Designing

• The main dashboard page displayed the KPI metrics relevant for the management to see, these were: Total Revenue, Total Units Sold, Gross Profit, Total Delivered Units, Gross Profit Margin, Total Orders as shown below:
• The second page contained analysis on Sales Performance based on the Vehicles as shown below:
• The third page contained analysis on Branches and Sales Rep as shown below:
• The last page had analysis on Customers and Payments as shown below:

Key Findings

• The business recorded Ksh1.898bn in revenue from 466 units sold generating Ksh415.35M in gross profit and a profit margin of about 21.9%.
• Revenue and gross profit peaked sharply in April above all other months which stayed at a much lower and relatively stable level. This suggests a seasonal or promotional driver worth investigating.
• Toyota leads revenue by a wide margin close to Ksh 400M. The remaining car makes each contribute a much smaller share, so revenue is heavily concentrated in one brand.
• Thika is the strongest performing branch generating a revenue of about Ksh400M with a gross profit of about Ksh144M making Kiambu county the best performing.
• Only 146 of 466 units are recorded as delivered. This is a significant gap that may indicate a delivery backlog or incomplete status records worth investigating.
• Nairobi HQ ranks fifth in revenue yet it is second in gross profit earning much healthier margins than Kakamega, Kisumu, and Eldoret. These branches record high revenue but low profit which suggests heavy discounting or high logistics costs.
• Grace Njeri was the top performing sales rep generating a gross profit of Ksh151M to the company with revenue sales of ksh346M.
• Dealers generate the highest revenue about Ksh540M, followed by Government Ksh430M implying that Dealers are the highest business drivers for the company.
• M-Pesa generates the most gross profit (Ksh170.39M) followed by Bank Transfer (Ksh114.14M). Together they account for about 69% of profit generated.
• Website, Walk-in, and Instagram are the top revenue drivers, each generating a revenue between roughly Ksh270M and Ksh320M.
• Paid transactions account for most revenue nearly Ksh1.0bn, but a large share sits in Partially Paid, Pending, Cancelled, and Refunded statuses. Together these represent a considerable portion of revenue that is not yet fully collected or has been lost.

Recommendations

• Reduce dependence on Toyota by promoting other makes with healthy margins.
• Study Thika branch sales approach and apply it to underperforming branches.
• Review the delivery process to close the gap between units sold and units delivered.
• Share the sales practices of Faith and Grace through coaching or mentoring across the team.
• Investigate cost drivers at Kakamega, Kisumu, and Eldoret, such as logistics costs and discounts, since their revenue is strong but profit is weak.
• Consider growing coastal and northern coverage, as Mombasa is the only branch outside the central and western corridor.
• Strengthen relationships with Dealers and Government clients, as they drive most of the profit.
• Prioritize M-Pesa and Bank Transfer as preferred payment options, and review why Asset Finance and Loan sales contribute so little.
• Follow up on Partially Paid and Pending transactions to improve collection, and investigate the causes of Cancelled and Refunded sales.
• Consider whether asset finance or flexible payment plans could help Individual buyers, who are the lowest-revenue segment among named customer types.

Conclusion

• This project turned a raw uncleaned dataset into a reliable Power BI solution for JCars Logistics. It was also a valuable learning experience as it strengthened my understanding of data cleaning, modeling, and dashboard design. I now feel more confident handling larger datasets and continuing to grow in my craft.
• The complete project can be accessed on GitHub using the repository link below: https://github.com/Tracy-Kihara/JCras_Logistics_Analysis