Introduction
Business Intelligence is not simply about creating attractive dashboards. A reliable dashboard starts with understanding the data, identifying quality problems, applying appropriate transformations, building a suitable analytical model, and developing calculations that answer meaningful business questions.
For this project, I worked with the JCars Logistics vehicle sales and operations dataset to develop an interactive Power BI solution for analysing sales performance, profitability, customers, vehicles, branches, logistics operations, and unusual business patterns.
The project followed a complete BI workflow:
Raw Data → Data Investigation → Cleaning & Transformation → Data Modelling → DAX → Dashboard Development → Analysis → Insights → Recommendations
The goal was to transform a raw and inconsistent dataset into a structured analytical solution that could support management-level decision-making.
In this article, I will walk through the problems I encountered in the raw data, how I cleaned and prepared it, how I designed the data model, how I developed the DAX calculations, and how the final report was designed to turn the data into useful business information.
Receiving and Understanding the Raw Dataset
The project started with a raw CSV dataset containing vehicle sales, customer, financial, operational, and location-related information.
At first, the dataset looked like a straightforward table that could be imported directly into Power BI. However, before building any visuals, I needed to determine whether the data was actually reliable enough for analysis.
This was an important step because errors in the source data could eventually appear as incorrect KPIs, misleading comparisons, or inaccurate business conclusions.
What I Investigated
I performed a data-quality investigation covering several areas:
- Missing and blank values
- Null values
- Duplicate or repeated records
- Invalid numerical values
- Negative values
- Text appearing in numerical fields
- Inconsistent currency symbols and labels
- Values containing
KandMsuffixes - Invalid and inconsistent dates
- Placeholder values such as
-andTBD. - Ratings outside the expected range
- Inconsistent discount formats
- Inconsistent categorical values
Rather than assuming that every unusual value was an error, I considered the meaning of each field and determined an appropriate treatment.
For example, a negative value could indicate a genuine business adjustment or could be a data-entry or scraping issue. Automatically deleting such records could therefore remove potentially useful information.
Why the Investigation Mattered
The investigation established that data preparation needed to happen before the analytical model was built.
This changed the workflow from simply:
Import → Visualize
to:
Investigate → Clean → Validate → Model → Analyze
That distinction became one of the most important lessons from the project: a dashboard is only as reliable as the data and business rules behind it.
Data Cleaning and Transformation with Power Query
After identifying the data-quality issues, I moved into the cleaning and transformation stage using Power Query in Power BI.
The objective was not simply to make the dataset look cleaner. Each transformation needed to make the data more consistent and suitable for analysis.
Main Cleaning Steps
The cleaning process included:
- Promoting the correct row to column headers.
- Assigning appropriate data types to the columns.
- Cleaning text fields and removing unnecessary spaces.
- Handling blank, null, and placeholder values.
- Cleaning numerical fields that contained text or symbols.
- Standardizing monetary fields.
- Cleaning and standardizing discounts.
- Standardizing customer ratings.
- Cleaning and standardizing dates.
- Handling negative values according to the documented business rules.
- Creating the keys required for the analytical model.
- Preparing the fact and dimension tables.
- Validating the cleaned data before continuing to modelling.
Handling Missing Values
Some fields contained missing information. Instead of leaving these values untreated, I applied documented business rules where a reasonable default was appropriate.
Examples included:
- Missing discount →
0 - Missing units sold →
1 - Missing review count →
0 - Missing delivery fee →
0 - Missing logistics cost →
0 - Missing customer rating →
3 - Missing customer age →
35 - Missing vehicle year →
2022 - Missing order ID →
UNKNOWN
These assumptions were documented so that the analytical results could be interpreted in the context of how incomplete records had been treated.
Cleaning Negative Values
Negative values required additional consideration because their meaning depends on the business field.
Where a negative value represented a value that should logically be non-negative, the data was cleaned according to the defined business rule. I avoided making arbitrary changes where the original business meaning could not be established.
This helped maintain a balance between data correction and preservation of potentially meaningful records.
Validation
After the transformation steps, I checked the cleaned data to confirm that:
- Numerical columns contained appropriate numerical values.
- Dates were consistently formatted.
- Categories were standardized.
- Missing values had been handled according to the documented rules.
- The resulting data could be loaded into the analytical model.
The validation stage was important because cleaning data without checking the results can introduce new errors while attempting to fix old ones.
Currency Handling and Business Assumptions
One of the more challenging parts of preparing the dataset was handling monetary values.
The raw data contained different currency symbols, currency labels, and numerical representations. Some values also used suffixes such as K and M.
Because financial calculations depend heavily on consistent units, currency handling had to be addressed before building the final measures.
Currency Standardisation
The final reporting currency for the project is Kenyan Shillings (KES).
During Power Query transformation, currency symbols and textual indicators were cleaned so that monetary fields could be used consistently in calculations.
Values using K and M suffixes were interpreted according to their numerical meaning.
Where foreign-currency labels appeared, but there was not enough reliable information to establish and apply a defensible exchange rate, I did not introduce an unsupported conversion.
This was an important modelling decision because applying an assumed exchange rate without sufficient evidence could create a different type of data-quality problem.
Revenue Calculation
The project used the following business rule for revenue:
Revenue = Units Sold × Unit Selling Price × (1 − Discount) + Delivery Fee
This calculation was implemented in the analytical layer so that revenue could be consistently analysed across the report.
Other Assumptions
Several additional assumptions were required when dealing with incomplete records.
For example, missing discounts were treated as zero, while missing delivery and logistics fees were treated as zero. Missing ratings were assigned a neutral value of 3.
These decisions were documented rather than hidden inside the transformation process.
Why Documentation Matters
An important lesson from this stage was that data cleaning is not purely technical.
When the source data does not provide complete information, the analyst has to make decisions about how the data should be interpreted. Those decisions become part of the analytical methodology and therefore need to be transparent.
The assumptions used in this project are documented in the accompanying project documentation in the GitHub repository.
Building the Analytical Data Model
Once the data had been cleaned and validated, I moved from data preparation into data modelling.
The raw dataset was originally structured as a large flat table. Although a flat table can be useful for initial investigation, it is not the most suitable structure for a scalable analytical report.
I therefore created a star-schema data model consisting of a central fact table surrounded by descriptive dimension tables.
_Figure 1 illustrates how the dataset looked after cleaning. _
Fact Table
The central table is FactSales.
Figure 2.1 illustrates the FactSales Table
It contains the transaction-level information required for quantitative analysis, including measures related to sales, revenue, costs, discounts, delivery, logistics, ratings, and other transaction indicators.
Dimension Tables
I created the following dimensions:
Dim Vehicle
Contains descriptive vehicle attributes such as:
- Vehicle Key
- Car Make
- Car Model
- Vehicle Type
- Vehicle Year
- Fuel Type
- Transmission
- Colour
Dim Customer
Contains customer-related descriptive information used to analyse customer activity and performance.
Dim Location
Contains branch and location information used for geographical and branch-level analysis.
Dim Date
The Date dimension was created from the Order Date and contains:
- Date
- Date Key
- Month
- Year
Relationships
The dimension tables connect to FactSales using one-to-many relationships, with the dimension acting as the "one" side and FactSales as the "many" side.
This allows users to filter transactions by dimensions such as vehicle, customer, location, and date while keeping the model structured.
Why a Star Schema?
The star schema provided several advantages:
- Clear separation between descriptive attributes and transaction data
- Simpler DAX calculations
- Better filtering behaviour
- Easier report maintenance
- Improved analytical flexibility
- A structure that is easier to understand and extend
The modelling stage therefore transformed the cleaned dataset into a structure designed specifically for business analysis rather than simply reproducing the source layout.
Developing DAX Measures
With the data model in place, the next stage was developing the DAX measures required for analysis.
Instead of relying only on implicit aggregations, I created dedicated measures so that the business calculations could be reused consistently across different report pages and visuals.
Sales and Revenue Measures
The model includes measures for:
- Total Revenue
- Units Sold
- Total Orders
- Average Order Value
- Average Selling Price
These measures support the high-level sales analysis as well as more detailed comparisons by vehicle, branch, location, payment method, and time.
Profitability Measures
To analyse value generation, I developed measures covering:
- Total Cost
- Gross Profit
- Profit Margin
- Profit Per Vehicle
Looking at these measures together makes it possible to distinguish sales volume from actual financial contribution.
Customer and Operational Measures
Additional measures were developed for areas such as:
- Total Customers
- Customer activity
- Review counts
- Customer ratings
- Delivery fees
- Logistics costs
- Returns
- Cancellations
These measures extend the analysis beyond sales and help provide an operational and customer-experience perspective.
Discounts and Payment Methods
I also created measures to support discount and payment analysis, including average discount and revenue by payment method.
This allowed discounts and payment channels to be analysed alongside financial performance rather than in isolation.
Ranking and Time Analysis
Ranking measures were developed to compare vehicles and branches across relevant performance indicators.
Time-based measures were also used to analyse changes across years and months using the Date dimension.
Why Measures Instead of Only Calculated Columns?
A key part of the DAX development process was understanding when a measure was more appropriate than a calculated column.
Measures are evaluated according to the current filter context. This makes them particularly useful for dashboards where the same calculation needs to respond dynamically to slicers, page filters, and visual selections.
For example, Total Revenue can automatically recalculate when a user filters the report to a particular branch, vehicle type, year, or payment method.
This made the DAX layer an important part of creating an interactive analytical report rather than a collection of static calculations.
Designing the Power BI Dashboard and Report
After completing the data preparation, modelling, and DAX development, I moved to the report-development stage.
The main design objective was to avoid creating a collection of unrelated charts. Instead, I organized the report around different levels of business analysis, starting with an executive overview and moving into detailed investigation.
Executive Dashboard
The first page was designed as the management entry point.
It provides a high-level view of important KPIs and business performance indicators so that a user can quickly understand the overall position before moving into deeper analysis.
The dashboard focuses on measures such as:
- Revenue
- Profitability
- Units
- Orders
- Customers
- Operational indicators
Sales & Analysis
The second page provides more detailed sales analysis.
Users can explore performance across dimensions such as:
- Vehicle type
- Car make
- Car model
- Branch
- Location
- Payment method
- Time
This page moves from the overall picture into the factors contributing to sales performance.
Profitability & Branches
The profitability page focuses on financial performance and branch comparisons.
The intention was to allow management to examine revenue and profitability together rather than treating sales volume as the only indicator of performance.
Branch Detail
A dedicated Branch Detail page was created to provide deeper analysis of individual branches.
I also implemented drill-through functionality, allowing users to move from broader analysis into branch-specific details.
This reduced the need to place every possible detail on the main dashboard.
Operations & Customer Experience
This page examines the operational and customer side of the business.
It includes analysis related to:
- Delivery
- Logistics
- Returns
- Cancellations
- Ratings
- Reviews
- Customer activity
This provides a different perspective from purely financial reporting.
Investigations & Exceptions
The investigations page was designed to identify unusual records and business patterns that could require further attention.
The purpose was not to automatically label every unusual observation as an error, but to provide a structured way of identifying areas for investigation.
Management Insights & Recommendations
The final page translates the analysis into management-focused insights and recommendations.
This helps bridge the gap between technical Power BI analysis and practical business decision-making.
Interactivity and Navigation
The report includes slicers for dimensions such as:
- Year
- Month
- Branch
- Location
- Vehicle Type
- Car Make
- Car Model
- Payment Method
The visuals respond to selections through Power BI's filtering and cross-filtering behaviour.
The report also includes:
- Page navigation
- Drill-through
- A dedicated branch tooltip
- Interactive visual filtering
- Dynamic DAX measures
These features allow users to explore the data rather than simply read a static report.
Why the Report Was Structured This Way
The report was intentionally organised from summary to detail.
A management user can begin with the Executive Dashboard, identify an area that requires attention, move to a more detailed analytical page, and then use filtering or drill-through to investigate the underlying business area.
This structure helped keep the main dashboard focused while still providing access to detailed analysis.
Turning the Dashboard into Business Analysis
Once the report was complete, I used the model and dashboard to investigate business questions rather than simply observing the visuals.
The analysis covered several areas of the JCars Logistics business.
Sales Performance
I examined how revenue, units, and orders varied across:
- Time
- Branches
- Locations
- Vehicle types
- Makes and models
- Payment methods
This provided a way to move from overall sales performance into the specific business dimensions contributing to it.
Profitability
Revenue alone does not provide a complete picture of business performance.
I therefore compared revenue with costs, gross profit, profit margin, and profit per vehicle.
This helped identify areas where sales activity and financial contribution needed to be considered together.
Vehicle Performance
Vehicle-level analysis was used to investigate whether different vehicle types, makes, models, and vehicle characteristics showed different levels of sales and profitability.
This can support future decisions around inventory, sales focus, and vehicle-level performance monitoring.
Branch and Location Performance
Branch analysis was used to compare performance across locations.
Rather than relying on a single KPI, I considered multiple measures including revenue, profitability, units, and operational indicators.
This provides a more complete view of branch performance.
Customer and Operational Experience
Customer ratings, reviews, returns, cancellations, delivery, and logistics were analysed alongside financial measures.
This allowed operational performance to be considered as part of the overall business picture.
Discounts and Payment Methods
Discounts were investigated in relation to sales and profitability, while payment methods were analysed based on their contribution to revenue.
This helped answer questions about how different commercial activities relate to financial performance.
Investigating Exceptions
The report also included an investigation section for unusual records and patterns.
An important principle was to distinguish between:
“This record is unusual”
and:
“This record is definitely wrong.” **
An unusual observation may represent a genuine transaction, a data-quality issue, or a business situation requiring further investigation. The dashboard therefore supports investigation rather than making unsupported causal claims.
Analyst-Defined Questions
In addition to the required management questions, I developed additional questions to explore the dataset more deeply.
These included:
- Which vehicle categories contribute to sales activity?
- How does profitability vary across branches?
- How do discounts relate to profitability?
- How does revenue vary by payment method?
- Which vehicle-level patterns require further investigation?
- What operational patterns exist around returns and cancellations?
- How does customer activity relate to business performance?
- How does performance change over time?
- Which unusual records deserve additional investigation?
- Which combinations of business dimensions reveal useful performance differences?
These questions helped guide the choice of measures and visuals instead of creating visuals first and searching for a purpose afterwards.
Key Insights from the Analysis
The completed analysis produced several important observations about how the JCars Logistics business can be evaluated.
1. Revenue and Profitability Need to Be Viewed Together
One of the main analytical lessons was that revenue should not be treated as the only measure of performance.
A business area can generate substantial sales activity while having a different profitability profile after considering costs, discounts, delivery, and logistics.
For this reason, the report presents revenue alongside gross profit, profit margin, and profit per vehicle.
2. Branch Performance Requires Multiple KPIs
Branch performance cannot be fully understood from a single metric.
The report allows branch performance to be examined using financial, sales, customer, and operational indicators.
This makes it possible to identify areas where a branch may require further investigation without relying on one isolated KPI.
3. Vehicle-Level Analysis Adds Important Detail
Overall sales figures can hide differences between individual vehicle categories, makes, and models.
Vehicle-level analysis therefore provides additional context for understanding which parts of the vehicle portfolio are contributing to sales and profitability.
4. Discounts Can Affect Value Generation
Discounts are useful commercial tools, but their impact needs to be considered alongside profitability.
The report therefore allows discount levels to be analysed together with financial performance instead of evaluating discounts based only on transaction activity.
5. Operational Metrics Add Context to Financial Results
Delivery fees, logistics costs, returns, cancellations, ratings, and reviews provide additional information about business operations and customer experience.
These indicators can help management identify areas that deserve further investigation even when the financial results alone do not immediately reveal the issue.
6. Data Quality Is Part of the Analysis
The project demonstrated that data quality is not a separate activity that ends before analysis begins.
Currency inconsistencies, missing values, invalid dates, inconsistent formats, and unusual records can directly affect KPIs and business conclusions.
The cleaning and validation stages were therefore essential to the reliability of the final report.
7. Exceptions Should Be Investigated, Not Automatically Removed
Some records appeared unusual and required additional attention.
Rather than automatically deleting unusual observations, the analytical approach was to investigate them within the context of the available data.
This reduces the risk of removing legitimate business activity simply because it differs from the expected pattern.
Management Recommendations
The analysis provides several areas where management can use the Power BI solution to support ongoing decision-making.
1. Monitor Profitability Alongside Sales
Management should review revenue together with total cost, gross profit, profit margin, and profit per vehicle.
This provides a more complete picture of value generation than monitoring sales volume alone.
2. Review Branch Performance Regularly
Branch performance should be monitored using multiple KPIs rather than a single measure.
Regular review of revenue, profitability, sales activity, and operational indicators can help identify branches or locations that require additional investigation.
3. Evaluate the Effectiveness of Discounts
Discounts should be evaluated alongside profitability and sales performance.
This can help management understand whether discounting is supporting the desired business outcome while maintaining an appropriate level of value generation.
4. Investigate Operational Exceptions
Returns, cancellations, delivery-related measures, logistics costs, and unusual records should be monitored regularly.
Where unexpected patterns appear, management can investigate the underlying transactions or processes before deciding on corrective action.
5. Use Vehicle-Level Analysis for Planning
Vehicle make, model, type, year, and related performance indicators can provide useful information for sales and inventory planning.
The dashboard can be used to identify changing performance patterns and support future vehicle-related decisions.
6. Maintain Data-Quality Controls
The project showed how inconsistent source data can affect downstream analysis.
Future data collection processes should therefore include validation rules for:
- Currency
- Dates
- Numerical values
- Ratings
- Discounts
- Missing fields
- Categorical values
Improving data quality at the source can reduce the amount of corrective work required during later BI development.
Conclusion
The JCars Logistics project provided an end-to-end experience of developing a Business Intelligence solution from a raw dataset.
The project began with data-quality investigation and progressed through Power Query cleaning, currency handling, assumptions, data modelling, DAX development, dashboard design, analysis, and management recommendations.
The most important lesson was that successful BI development is not only about producing a dashboard. It is about creating a reliable chain from raw data to evidence-based business understanding.
The final Power BI solution combines an analytical star schema, reusable DAX measures, interactive report pages, drill-through functionality, tooltips, and investigation-focused analysis.
By combining technical data preparation with business-oriented analysis, the project demonstrates how Power BI can transform raw operational data into a practical decision-support tool.
https://github.com/4688-tori/Jcars-Logistics-PowerBI
