Introduction
A dashboard can look impressive, but its real value depends on what happens before the first chart is created. For this project, I worked with a raw JCars Logistics dataset containing information on vehicle sales, customers, branches, sales representatives, payments, deliveries, logistics costs, returns, and customer experience. At first glance, it looked like a straightforward dataset ready for analysis. However, a closer look revealed a much bigger challenge: the data was not yet ready to be trusted.
The dataset contained missing values, inconsistent categories, different date formats, unusual figures, spelling variations, mixed currencies, and other quality issues that could easily distort business results. This meant that creating visuals immediately would only make the problems look more attractive rather than solving them.
My goal was not simply to build a Power BI dashboard. I wanted to take the data through a complete journey—from raw information and investigation, through cleaning and modelling, to meaningful analysis and business recommendations. The final solution would help JCars Logistics understand its performance, identify areas requiring attention, and turn its data into evidence that can support better business decisions.
Dataset Understanding
Once the dataset was loaded, the next step was to understand what I was actually working with. I first looked through the JCars data to understand the type of information available, the meaning of each column, and the questions the data could potentially answer.
The dataset contains information about different cars and their characteristics, including details such as the vehicle make, model, year, mileage, fuel type, transmission, engine specifications and selling price. Each row represents a car listing, while the columns describe different attributes of that listing. This helped me see that the dataset could be used to explore patterns in car prices and understand how different vehicle characteristics relate to the asking price.
I also paid attention to the difference between categorical and numerical fields. For example, make, fuel type and transmission describe categories, while price, mileage and year contain values that can be measured or analysed mathematically. Recognising these differences was important because Power BI handles each type of field differently when creating calculations and visualisations.
This initial exploration also helped me think beyond individual columns. I started considering questions that the final dashboard should answer: Which cars are listed at higher prices? How does mileage vary across vehicles? Which makes appear most frequently? Does the year of manufacture seem to influence price? These questions provided a practical direction for the analysis.
Understanding the dataset became the foundation for the rest of the project.
Data Quality Investigation
Before transforming the data, I needed to find out whether there were any problems that could affect the analysis. A dashboard is only as reliable as the data behind it, so I checked the dataset for issues such as missing values, duplicate records, inconsistent formats and unusual entries. This was important because even a well-designed visual can give misleading results if the underlying data is incomplete or inconsistent.
There are blank cells in 21 of the 32 columns. The most frequent blanks are in Returned, Review Count, Discount, Vehicle Year, and Customer Rating. Dates and sales values, also contain blanks.
The Order ID field has 257 distinct values across 276 rows, and some IDs repeat. Several entries are placeholders such as N/A or -, so the repeated values need to be checked before using Order ID to count orders.
The file also mixes formats in ways that Power BI may not interpret consistently. Order and delivery dates appear in several formats, including written dates, day/month/year dates, Excel-style serial numbers, and invalid or unclear entries. Price and cost fields mix plain numbers with currency labels, commas, abbreviated values such as 4.2M, and text such as not available, missing, or #VALUE!. Discounts mix percentage signs, decimals, words, and values such as 120%.
Category labels are inconsistent, for example, fuel type includes Petrol, PETROL, Gasoline, and PMS; transmission includes Automatic, AUTO, AT, and A/T. Make, region, status, and other categories also vary in spelling and capitalization. Some apparent variants may mean the same thing, but they should be reviewed before being combined.
I also found values that need investigation rather than automatic deletion. Vehicle Year includes text entries, 1899, and 2032. Customer Rating includes -1, 6, and Excellent, alongside numeric ratings and missing markers. Units Sold includes text such as two and 3 cars, as well as 0 and -1. These values can’t be treated as ordinary numeric entries without checking what they represent.
This check gave me a clearer picture of what needs attention before building the report: standardise formats and categories, decide how to handle missing or invalid values, and confirm which unusual entries are errors. I should keep the original data intact while investigating so I can make and explain those decisions carefully.
Data Cleaning
After preparing the JCars data for review, I began cleaning it in Power Query, working through the columns. The goal was to make values consistent and usable in Power BI while keeping uncertain information available for review.
I started with text fields. Trim removes extra spaces at the beginning or end of a value, while Clean removes hidden characters. These steps help Power BI recognise matching labels consistently. I applied them to fields such as Order ID, customer and location details, lead source, vehicle information, fuel type, transmission, and color.
I checked that each column had a suitable data type. Order IDs, names, and categories are text. Customer Age and valid Units Sold values are whole numbers. I kept Order Date and Delivery Date as text for now because they contain mixed date formats, Excel-style serial numbers, blanks, and unclear entries. Vehicle Year also needs review before conversion because it contains text and unusual values.
I checked for duplicates before removing any records. The file contains no exact duplicate rows—no rows where every field is identical. However, some Order ID values repeat, and some IDs are placeholders such as N/A or -. Removing duplicates based on Order ID alone could delete records that contain different information, so I kept these rows for review.
I also made capitalization consistent where appropriate. This improves the appearance of labels, but it doesn’t correct misspellings or prove that two different labels mean the same thing. Possible variants in fields such as Region, County, City, Car Make, Fuel Type, and Transmission need to be verified before they are combined. Names and model codes also need care because automatic capitalization can change how they should appear.
I reviewed blanks and placeholder text such as N/A, NULL, and -. When a value clearly meant that information was missing, it could be treated as blank. I kept unclear entries for review instead of guessing. A blank can mean different things in different columns, so I need to consider the field before deciding how to handle it.
I checked numerical values against what each field represents. Customer Age includes values such as -5 and 121, so I filtered the data to keep ages from 20 to 65, inclusive. Vehicle Year includes entries such as Twenty Twenty, 202A, 1899, and 2032. Units Sold includes text such as two and 3 cars, as well as 0 and -1. I flagged these for review rather than changing them without checking the source.
When a data type conversion produces an error, the value needs to be checked before the row is removed. If I can verify and correct the source value, I can try the conversion again. If it remains uncertain, I can leave it flagged or treat it as missing where appropriate. Removing errors or blank rows should only happen after checking what information would be lost.
These cleaning steps made the data easier to work with while preserving records that still need investigation. The mixed date formats, placeholder IDs, and uncertain spelling or numerical variants remain items to verify before they are used in analysis.
Data Modelling
After cleaning the JCars data, I checked how the table was set up for analysis in Power BI. Each row represents a sales record, and the file currently has one table. That means I don’t need to create relationships between tables at this stage.
I also considered how Power BI should handle each field. Order ID identifies a record, so adding its values together would have no meaning. Units Sold can be summed to show the number of cars sold, while Customer Age can be averaged to compare the ages of customers. Choosing these settings helps prevent Power BI from producing totals that don’t describe the data accurately.
The data model will become more useful as I clean the remaining fields, especially prices, costs, discounts, dates, and revenue. Those columns need reliable values and suitable data types before I use them in calculations. For now, I set the fields I had cleaned to behave appropriately and kept the JCars table ready for the next stage of the project.
Building the First Visuals
I started building visuals to make the information easier to understand. The first question I wanted the report to answer was how many vehicles were sold in total. From there, I could compare the number of units sold across car makes and explore how the results varied by region or vehicle characteristics.
I began with a Total Units Sold card. A card is useful for a headline figure because it presents one important number clearly. I used the Units Sold field and set it to Sum, so Power BI adds the recorded quantities together. This gives a total of units, rather than simply counting how many rows are in the table.
I then created a bar chart to compare units sold by car make. The length of each bar makes it easy to see which makes have larger or smaller totals. Before using this chart to draw conclusions, I need to make sure each make is represented consistently.
To make the report interactive, I added a Region slicer. A slicer lets the reader choose a region and see the connected visuals update. This gives the reader a way to explore the data without changing the report itself. Vehicle Type, Fuel Type, Transmission, and Vehicle Year can also be used as slicers once their values have been reviewed for consistency.
I kept the page focused on a few visuals with clear purposes: the card summarises the total, the bar chart compares categories, and the slicer helps explore the results. I also checked that the chart and card responded to the slicer selection. This helped confirm that the fields were placed correctly and that the report was filtering as expected.
Reviewing the Regional Sales Visual
With the region names beside the bars, I could compare the sales totals by location directly. Each bar represents one region, and its length shows the sum of Units Sold there. The Total Units Sold card gives me an overall figure to compare with the regional breakdown.
I then used the Region slicer to explore the report. Selecting a region filters the chart and card to that selection; clearing it returns the report to the overall view. This made it easier to focus on one location while keeping the wider picture available.
Before describing what I found, I checked that the chart was grouped by Region, that Units Sold was summarised by Sum, and that the region names were readable. I can now use the chart to identify which regions have higher or lower totals and record those observations from the values shown in Power BI.
Analysis
The dashboard provides an opportunity to examine the business from several perspectives. The purpose of the analysis is to understand the relationships and patterns within the data and what they may indicate about the company's sales and operational performance.
The first area of analysis is sales volume and revenue. Total Units Sold provides an indication of the number of vehicles sold, while Total Revenue measures the financial value generated from those sales. Examining these measures together gives a clearer picture of sales performance than either measure on its own. A high number of units sold does not necessarily translate into the highest revenue, particularly where vehicle prices differ substantially between categories. Therefore, the dashboard should be used to compare sales volume with revenue across vehicle types, regions, and time periods.
The second area is profitability. Gross Profit and Gross Margin % provide a deeper financial perspective by showing what the business retains after relevant costs and how efficiently revenue is converted into gross profit. This distinction is important because strong revenue performance does not automatically indicate strong profitability. A segment may generate considerable sales while operating with a relatively narrow margin. Analysing revenue alongside gross profit and gross margin therefore helps identify areas where sales activity and financial returns do not necessarily move together.
Regional performance is another important part of the analysis. The Western Revenue measure allows the performance of the Western region to be examined specifically and compared with other regions. Differences between regions may reflect variations in customer demand, vehicle availability, pricing, market size, or other business factors. The dashboard does not by itself establish the cause of these differences, but it provides evidence of where further investigation should be concentrated.
The analysis also considers performance over time. Trends in units sold and revenue can reveal periods of growth, decline, or fluctuations in business activity. Looking at these trends can help distinguish an isolated change from a recurring pattern. Where substantial changes are visible, additional analysis of vehicle categories, regions, or operational indicators can help determine what factors coincide with the change
The dashboard provides an opportunity to connect sales performance with operational outcomes. Delivery status, returns, and cancellations can be examined alongside sales and financial measures. This is important because the completion of a sale is only one part of the customer journey. Patterns in cancellations, returns, or delivery performance may indicate areas requiring operational attention and can provide useful context when interpreting overall business performance.
Insights and Recommendations
Measured Insights
JCars recorded a strong overall gross profit position.
The business generated total sales revenue of approximately KES 8.21 billion from 255 transactions and 446 units sold. Total recorded cost was approximately KES 1.36 billion, resulting in a gross profit of KES 6.85 billion and a gross profit margin of 83.39%. The figures show that the recorded sales generated substantially more revenue than the associated costs.
Revenue was spread across multiple vehicle models rather than being dominated by one model.
The Toyota Corolla and Mazda Demio each contributed 4.5% of total revenue. The Corolla generated approximately KES 83,600 per car, showing that model-level performance can differ from simply looking at total units sold. Comparing revenue and profitability by vehicle model can therefore provide a more meaningful view of the sales mix.
Revenue per car varied across locations.
Nairobi HQ recorded the highest revenue per car at approximately KES 78,300, followed by Mombasa at KES 74,900 and Athi River at KES 72,300. The difference indicates that location-level performance is not uniform and that factors such as pricing, vehicle mix and customer demand may be influencing revenue generated per vehicle.
NGOs were the most active customer segment by number of orders.
NGOs recorded the highest number of orders, with 58 orders. This makes the segment an important part of JCars' customer base and highlights the value of analysing customer categories separately. However, order volume should also be compared with revenue and profit to determine the financial contribution of each segment.
Recommendations
Use profit-based performance measures when evaluating sales.
JCars should avoid relying on sales volume or revenue alone when assessing performance. With KES 6.85 billion in gross profit and an 83.39% gross margin, management should continue monitoring revenue alongside costs and gross profit to identify sales that create the greatest financial value.
Develop a vehicle-level performance strategy.
JCars should regularly compare each vehicle model using units sold, revenue per car, total revenue and profit margin. Since the Corolla and Demio each account for 4.5% of revenue, model-level analysis can help management identify which vehicles are generating stronger returns and make better-informed purchasing and sales decisions.
Investigate and address location-level performance differences.
The difference between Nairobi HQ's KES 78,300, Mombasa's KES 74,900 and Athi River's KES 72,300 revenue per car should be investigated further. Management should examine pricing, vehicle mix, customer composition and operating costs at each location and use the findings to improve lower-performing areas.
Strengthen relationships with high-activity customer segments.
Since NGOs generated the highest number of orders at 58, JCars should maintain and develop this customer segment while also examining the revenue and profit generated from it. Customer-segment dashboards can help identify repeat customers, high-value accounts and opportunities for targeted sales or partnership strategies.
Use the dashboard for continuous performance monitoring.
The Power BI dashboard should not be treated only as an assessment deliverable. JCars can use the same approach to monitor revenue, costs, profit margin, vehicle performance, location performance and customer activity on a regular basis. Updating these measures over time would allow management to identify changes early and make decisions based on current business performance rather than relying solely on historical reports.
Conclusion
The JCars Logistics analysis demonstrates the value of turning raw data into a clear business story. By cleaning the dataset, building a structured data model, creating meaningful DAX measures and presenting the results through an interactive Power BI dashboard, complex business information became easier to understand and act on. The findings provide JCars with a clearer view of its revenue, profitability, vehicle performance, locations and customer activity. The project shows how data analytics can move a business from simply recording transactions to using those transactions to support better, evidence-based decisions.
Top comments (0)