Data without structure is just noise. In this project, I am building a complete, end-to-end Business Intelligence solution for JCars Logistics, a company that imports, sells, and delivers vehicles across Kenya. The business needs a way to move away from raw transaction logs and instead use data to understand sales performance, profitability, logistics costs, and customer behavior.
I am transforming a messy, flat dataset into a structured, interactive Executive Dashboard and detailed analytical reports. In this article, I am walking you through my full journey, from data quality auditing and Power Query cleaning to DAX modeling, dashboard design, and executive insights.
1. Auditing Data Quality and Identifying Assumptions
Missing Value Imputation: Handled null entries contextually, applying statistical imputation or default placeholders for text columns, and nulls for numerical fields.
Currency Standardization: Reconciled multi-currency client transactions by applying exchange rates to standardize all monetary metrics into Kenya Shillings (KES).
Date Parsing and Validation: Removed malformed, unparseable date records that violated standard calendar formats to preserve timeline integrity.
Semantic Normalization: Standardized ambiguous categorical entries, such as consolidating "False", "No", and "Not Returned" into a single unified boolean state ("Not Returned").
Discount Normalization and Conversion Assumptions: Applied current market since the payment dates are not explicit. Normalized discounts by treating decimals as direct factors and converting unitless whole numbers into standard percentages.
2. Data Modelling
To avoid relying on a flat-file structure, the data model transitions from a single flat table into a relational Star Schema.
Repeating attributes, including branch locations, vehicle specs, and sales representative details, were separated into dedicated dimension tables (Dim Branches, Dim Cars, Dim Sales Reps). Surrogate keys were generated for each entity and mapped back to the central Facts table using Left Outer joins.
Additionally, customer attributes were extracted into a standalone Dim Customers dimension table. Although the transactional grain did not strictly demand this separation for basic reporting, normalizing customer entities preserves historical data integrity, prevents redundancy, and prepares the schema for future scalability, such as tracking repeat buyers.
3. DAX Functions
I used both implicit Power BI aggregations and explicit DAX measures. Here are a few key measures I am implementing:
Total Profit: I am subtracting both the inventory base costs and logistics expenses directly from recorded revenue.
Total Profit = SUM('Facts'[Recorded Revenue]) - (SUMX('Facts', 'Facts'[Units Sold] * 'Facts'[Cost Per Unit]) + SUM('Facts'[Logistics Cost (Ksh)]))
Average Delivery Time (Days): Used AVERAGEX to iterate row-by-row through the dataset, evaluating the exact time difference between the order date and delivery date.
Average Delivery Time (Days) = AVERAGEX('Facts', DATEDIFF('Facts'[Order Date], 'Facts'[Delivery Date], DAY))
4. Structuring the Dashboard and User Flow
I strudctured the Power BI file with a deliberate analytical flow, guiding executive users from high-level summaries down to granular root-cause investigation.
Page 1: Executive Dashboard
I designed the first page as a single-page Executive Dashboard focused on monitoring. It prominently displays top-level KPIs (Total Revenue, Gross Profit, Total Units Sold) alongside revenue trends and top performers. To enable quick operational filtering, I added dedicated slicers for Region and Sales Rep, allowing leadership to dynamically isolate regional performance or individual rep metrics without cluttering the main visual layout. This page is purposely uncluttered so executives can assess overall company performance at a single glance.
5. Extracting Key Management Insights
Through these visual reports, several critical operational insights are emerging from the data:
Social Media Acquisition Dominance: Digital channels (Instagram and Facebook) are generating a significantly higher volume of closed deals compared to walk-ins or WhatsApp inquiries.
Payment Liquidity Patterns: High-volume liquid methods (M-PESA, Cash, Bank Transfer) account for the majority of completed transactions, while Asset Financing represents a secondary segment.
Logistics Margin Erosion: Specific delivery routes are incurring disproportionately high logistics costs relative to the delivery fees charged to customers, shrinking profit margins
7. Recommendations
Based on the data, JCars Logistics leadership should take the following actions:
Reallocate Marketing Investments: Increase ad spend on Facebook and Instagram, as the data proves these channels yield the highest conversion rates.
Adjust Regional Delivery Pricing: Restructure customer delivery fees in high-cost regions to ensure logistics expenses are fully covered, protecting gross margins.
Automate Payment Collection Workflows: Implement automated reminders and stricter timelines for pending and partially paid orders to improve cash flow and reduce collection delays.
Top comments (0)