iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To turn dealership records into useful management insight with Power BI, first decide which decisions the report must support, then clean the source data, model it at a clearly defined grain, and validate each measure against the underlying records. The dashboard design below is a practical pattern—not a report built or tested against a specific Kenyan dealership’s data.
Start with the decisions, not the charts
Agree with managers on the questions the report should answer before choosing visuals. For example:
- Which vehicles, categories, branches, or salespeople account for sales?
- Where is gross margin changing, and can the available records explain why?
- How much stock is on hand, and how long has it been held?
- Are there stock-outs or unusually long holding periods?
- How do results vary by month or compare with targets?
These are candidate questions, not guaranteed KPIs. Confirm that the source records contain the necessary fields and that the business agrees on definitions before building measures.
Identify and prepare the source data
Potential inputs include sales invoices, vehicle stock and movement records, purchase costs, expenses, customer and salesperson records, and targets. These are suggested source categories; no particular dealership’s data or system has been established.
#1 Best Overall
Use Power Query to inspect and shape exports before loading them into the model. Microsoft notes that source exports are often denormalized and may need transformation. Microsoft’s star-schema guidance describes how Power Query can help prepare those tables.
Check column types, missing identifiers, duplicate invoice lines, date formats, currency values, inconsistent vehicle descriptions, and nulls. Preserve the original values where practical, document transformations, and reconcile cleaned row counts and totals with the source system. Resolve issues such as inconsistent vehicle names before relying on model or category comparisons.
Set a consistent grain for each fact table
A semantic model separates facts—events or observations to summarize—from dimensions used to filter and group those facts. Each fact table needs a declared, consistent grain: precisely what one row represents. Microsoft’s star-schema guidance explains this distinction and why grain matters.
Rank #2
Sales
A candidate sales fact table could contain one row per vehicle sale line, if that matches the structure of the invoices. Define how cancellations, returns, discounts, and taxes are represented before totaling sales or calculating profit.
Inventory
Inventory data needs its own grain. A stock-movement table might record one row per movement; an inventory snapshot might record one row per vehicle at a specified point in time. Do not combine sales and inventory measures in a single table unless their row-level meaning and relationships are understood. A current-stock measure from snapshots must respect the snapshot’s as-of date.
Dimensions and relationships
Depending on available fields, useful dimensions may include Date, Vehicle, Customer, Salesperson, Branch, and Supplier. A Vehicle table might include make, model, year, fuel type, body type, or acquisition channel only when those attributes are present and sufficiently clean. Use unique keys on dimension sides and one-to-many relationships where appropriate. Avoid creating a dimension from unreliable descriptions without first standardizing them.
Define measures in business terms
Candidate measures include units sold, sales value, cost of goods sold, gross profit, gross margin percentage, current stock count, stock value, average days in stock, and sales versus target. Each depends on source fields and agreed definitions. Specify the currency, tax treatment, discount handling, transaction status, and date basis for every relevant measure.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
- Units sold: Count eligible sale lines or vehicles according to the agreed transaction rules; exclude canceled sales and account for returns consistently.
- Sales value: Decide whether the figure is before or after discounts and taxes, and state the currency.
- Gross profit and margin: Calculate only if reliable cost fields exist. Agree which costs belong in the calculation; do not infer margin from sales value alone.
- Stock count and value: Use an inventory source with a clear as-of date and define which statuses count as available stock.
- Days in stock: Requires a suitable date basis, such as the date a vehicle entered stock, and a defined end date for the calculation.
- Sales versus target: Requires target records at a compatible time period and organizational level.
Power BI explicit DAX measures are evaluated when a report query requests them, so their results respond to the report’s filter context. See Microsoft’s guidance on measures. Keep measure names and labels clear enough that users can tell what is included.
Build views around management use
A management overview can bring together sales value, units, gross margin, current stock, and aging, with trends by month and comparisons by vehicle model or branch where the model supports them. Supporting report pages can provide vehicle-level inventory and transaction detail.
Rank #4
- Label each KPI and state its time period and definition, either in the report or in a tooltip.
- Use date, branch, and vehicle filters only when the underlying fields and relationships support them.
- Make the inventory snapshot date visible when showing stock totals.
- Keep transaction-level detail available for investigation without crowding the overview.
Report performance depends on the data and visuals. Microsoft’s Power BI optimization guidance notes that visuals with more data to display can load more slowly and recommends restrictive filters in suitable cases.
Choose how the report gets fresh data
Import and DirectQuery have different freshness and operational behavior. The right choice depends on how quickly users need updates, where the source is hosted, data volume, query behavior, connectivity, and capacity.
| Mode | How source changes appear | Operational consideration |
|---|---|---|
| Import | The semantic model contains a point-in-time copy; changes appear after a successful refresh. | Set an appropriate refresh schedule and monitor refresh history. |
| DirectQuery | Report queries are sent to the underlying source as users interact with the report. | Source accessibility and query performance affect the experience. |
For an on-premises source that Power BI cannot reach directly, a gateway is needed for refresh. Microsoft’s refresh guidance covers refresh behavior and service limits. At the time described in that documentation, shared-capacity semantic models can be scheduled to refresh up to eight times daily; models on Premium, PPU, or Fabric capacity can be scheduled up to 48 times daily. These are service limits, not a recommendation that every dealership needs that frequency; verify current limits and licensing when implementing.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Validate the report and protect access
Before managers rely on the results, compare the model with the source system and test how users will filter it.
- Reconcile row counts and money totals against source records.
- Test date, vehicle, and branch filters, and confirm they return expected records.
- Check canceled and returned transactions against the agreed rules.
- Verify that stock counts use a consistent as-of date and status definition.
- Review refresh history and investigate failed or incomplete refreshes.
- Limit customer-level information to authorized users; assess whether row-level security is needed for reports shared across roles.
Use Kenyan registration figures only as market context
The Kenya National Bureau of Statistics’ 2026 Economic Survey reports 395,235 newly registered road motor vehicles and motorcycles in 2025, marked provisional. This is a nationwide registration count across vehicle types, not a measure of a particular dealership’s sales, used-car transactions, inventory, market share, or demand for a specific model.
KNBS says the increase was mainly driven by NTSA enforcement measures that cleared registration backlogs and improved access to motor-vehicle financing. The figure should therefore not be presented as unambiguous evidence of stronger underlying consumer demand. The 2026 Facts and Figures publication lists categories including station wagons, saloon cars, panel vans and pickups, lorries and trucks, buses and coaches, minibuses and matatu, trailers, wheeled tractors, motorcycles, three-wheelers, and other vehicles. National totals can add context to internal reporting, but cannot replace dealership transaction and stock records.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
