Turning a raw dataset into a useful business intelligence solution is not simply a matter of creating charts. It begins with understanding the data, identifying problems, designing an appropriate model, and creating calculations that answer real business questions.
The article documents my journey building the JCars Logistics Power BI project, from raw data through modelling, DAX, dashboard development and analysis.
1. Starting with the Raw Dataset
The first stage was understanding the dataset rather than immediately building visuals.
The PBIX contains the original Jcars_data dataset alongside a Jcars_data Clean version. Keeping the raw and cleaned files separate was important because it preserved the original source while providing a better dataset for analytical work.
Understanding the meaning of each field.
Checking data types, ie dates and numerical fields
Looking for blanks and invalid values.
Looking for duplicates
The Procedure was clean first, analyse second.
2. Data Cleaning and Preparation
The Jcars_data became the foundation for cleaning and preparation.
The preparation stage involved standardising field types and values, dealing with missing or invalid records where appropriate, and ensuring that dates could support time-based analysis.
Cleaning Process
Cleaning Steps
I then separated descriptive information from transactional information. This was an important modelling decision because repeating customer, vehicle, location and sales-representative attributes inside every transaction makes analysis harder to maintain.
The result was a model consisting of a main fact table surrounded by dimension tables.
3. Building the Data Model
I used a star-schema , with FactSales acting as the fact table.
The model contains dimensions including:
- Dim_Date
- Dim_Vehicle
- Dim_Customer
- Dim_Location
- Dim_SalesRep
- Dim_PaymentMethod
This approach separates measures from descriptive attributes.
For example, FactSales contains the transactional measures, while Dim_Vehicle provides vehicle context and Dim_Location provides geographic context.
4. DAX Development
Rather than placing calculations directly inside individual visuals, I created reusable measures.
Important measures in the project include:
- Total Cars Sold
- Total Sales Revenue
- Total Gross Profit
- Total Transactions
- Average Revenue per Car
- Average Revenue per Order
- Average Customer Rating
- Returned Transactions
- Total Logistics Cost
Measures such as average revenue per car are particularly useful as they transform into key business performance indicators:
Average revenue per car = DIVIDE([Total Sales Revenue], [Total Cars Sold], 0)
Similarly, average revenue per order can be calculated using revenue divided by the number of transactions.
Average revenue per order = DIVIDE([Total Sales Revenue], [Total Transactions], 0)
The benefit is that same business definition can be reused across cards and charts.
5. Dashboard Development
The main dashboard was designed around the questions a logistics manager might ask:
How much are we selling? Where are we selling? Which vehicles perform well? Which representatives and branches contribute most? How are revenue and profit changing?
The first page therefore combines KPI cards with comparative and trend visuals.
It includes:
- Cars sold
- Sales revenue
- Gross profit
- Average revenue per car
- Average revenue per order
- Vehicle performance
- Branch performance
- Sales representative performance
- Payment status
- Revenue and profit over time
- Revenue versus logistics cost
- Geographic performance
- Interactive branch and sales-representative filters
The design intentionally moves from well defined KPIs to explanations of performance. A manager can first see the overall position and then investigate what is driving it
6. Detailed Report Development
The solution goes beyond one dashboard. The PBIX contains six report pages, each serving a different analytical purpose.
The additional pages provide deeper analysis of branches, time trends, sales channels, vehicles, customers and sales representatives.
For example, the vehicle analysis compares cars sold by model, average revenue per model and the relationship between sales volume and customer rating.
7. Analysis and Business Insights
The report was designed to move beyond descriptive reporting.
The combination of revenue, gross profit and logistics cost allows management to investigate whether high-revenue areas are also generating healthy returns.
Branch-level analysis can identify locations that contribute strongly to revenue or vehicle volume, while the sales-representative analysis provides another dimension for evaluating commercial performance.
Vehicle analysis can reveal models with strong sales volume but weaker revenue per car, or models that generate higher value despite lower volume.
Time-series analysis also provides a way to identify changes in revenue and profitability rather than relying only on cumulative totals.
Conclusion
The JCars Logistics project demonstrates effective Power BI reporting starts well before dashboard design.
The workflow moved from raw data investigation → cleaning → preparation → dimensional modelling → DAX → dashboard design → detailed analysis.
The most important lesson is that the dashboard is only the final layer of the solution. The quality of the decisions it supports depends on the quality of the data preparation, modelling assumptions and calculations underneath it.
Top comments (0)