Introduction
JCARS Logistics imports,sells and delivers vehicles to customers across different regions in Kenya.
To help management track business growth and perfomance,i built a power bi analytics solutions.
This article breaks down how raw data was transformed into revenue,profits and insights using data modelling and DAX.
Objective
My objective is to develop a solution that enables J cars logistics management to understand the performance of the business and investigate factors contributing to the performance.
The data that was collected is below.
I started by cleaning the dataset inside the power query
Cleaned dateset inside powerquery
Referenced the flat table inorder to get dimension tables and a fact table
I connected the fact table to the dimension table by generating a primary key for each dimension table and merging it to the fact table(foreign key).
Star schema
Data Analysis Expressions (DAX)
In J Cars and Logistics data,after cleaning,in the power query i used DAX to transform raw transactional records into financial KPIs and breakdowns. By establishing measures across my star schema connecting sales facts with customer, regional and sales representative dimension tables.
Some of the dax functions i used are:
Total Units Sold: Sums all transactions of quantities across regions.
Total Units Sold = SUM(Fact[Unit Count])
Total Revenue: Aggregates gross sales value across all vehicle transactions to yield KSh. 1.35 Billion.
Total Revenue = SUM(Fact[Revenue])
Gross Profit: Evaluates actual earnings by subtracting unit cost from total revenue, resulting in KSh. 515.45 Million
Gross Profit = [Total Revenue] - SUM(Fact[Unit Cost])
Gross Margin Percentage: Utilizes the error-safe DIVIDE function to determine profitability margin without risking divide-by-zero errors when handling zero-revenue filters.
Gross Margin % = DIVIDE([Gross Profit], [Total Revenue], 0)
Resulting in a consistent business margin of 30% or 0.30 across current sales operations
Regional Revenue Analysis: Evaluates specific regional contributions, such as isolating the Eastern region's KSh. 151.36M revenue share within trend lines.
Eastern Revenue = CALCULATE([Total Revenue], Region[Region Name] = "Eastern")
My dashboard
Business Insights
- Total Revenue stands at 1.35 Billion from 466 units sold.The gross profit is at 515.45 Million delivering a gross margin % of 30%.
The Rift valley generates the highest volume of units sold,followed n=by Western,cental and Nyanza Regions
Lower Volume markets include Nairobi,Coast and Eastern Regions by showing lower unit sales relative to the top perfoming regions.The Unknown region category also recorded minor traces of sales.Higher perfoming sales representativees include Aisha Mohammed (sold 6 units of Axio),Grace Njeri selling 4 units of 320i and Brian Otieno selling 2 units of 320i.
Faith Achieng,Kevin Mwangi,Macy Atieno and Samuel Mutua each registered individual sales for models such as 320i.The total sum pf units sold was 466 units across all models,regions and sales representatives.
Challenges Faced
- Regional Performance Disparity- Strong concentration of sales in Rift Valley and Western highlights an underperformance or untapped potential in key major urban hubs like Nairobi. - Data Quality & Tracking Issues: The presence of an "Unknown" region entry suggests data entry gaps or unassigned sales locations in the source data.
- Revenue heavily dependant on a single dominant vehicle eg. Toyota drive a significant portion of representative sales, while other brands along the bottom chart eg Mazda, Mercedes-Benz, Mitsubishi,Honda,Subaru, show flat or very low individual counts in comparison. - Dependence on Specific Sales Representatives: Volume is heavily dependent on a few key sales representatives (e.g., Aisha Mohamed and Grace Njeri)therefore posing potential operational risks if staff turnover occurs.
Assumptions
- Data Completeness: The dataset represents the entire operational transaction history within the defined reporting period (no unrecorded offline transactions).
- Region Categorization: Sales are assigned to regions based on customer location or vehicle delivery points rather than dealership headquarters.
- Product Classification: Each unit sold corresponds to a single vehicle unit across the listed makes and models.
- Uniform Market Availability: Vehicle inventory and model availability availability were evenly distributed across regions during the reporting period.
Conclusion
J Cars and Logistics demonstrates strong performance in key regions like Rift Valley and Western, backed by consistent contributions from top sales reps. However,to drive sustained growth, the business needs to address regional disparities—particularly by boosting sales in major regions like Nairobi and clean up reporting anomalies like the "Unknown" region. Standardizing sales strategies across all representatives and balancing model availability across all brands will help convert overall unit volume into long term market leadership and profits.
Click here to access the full power bi report file on my github repository
Top comments (0)