Unified Dealer Performance Analytics Platform
How three separate product reports for a leading US automotive software company became one trusted Power BI platform covering 4.8M+ leads, 173K+ deals and three dealer products.
Executive Summary Dashboard
Screenshot coming soon01 Executive Summary
The client ran two separate Power BI reports for two of its dealer products, Deal Central and F&I Intelligence. Each had its own model, its own date logic and its own definitions of the same KPIs. Leadership could not see both products side by side, analysts duplicated work, and simple questions like "which dealers use both products?" had no answer.
I consolidated both into a single, governed semantic model with one shared calendar, one measure library of 145+ DAX measures and one 15-page report. During the project I also migrated the model to a new universal dealer source covering a third product (AMD Elite), caught and fixed a silent data regression, and delivered funnel and close-rate analysis that showed which tools drive dealer sales.
— Project at a Glance
02 The Challenge
- Two disconnected reports. Deal Central and F&I lived in separate files with separate data models, so no one could compare or combine them.
- Inconsistent KPIs. The same metric could be calculated differently in each report, which eroded trust in the numbers.
- No cross-product view. The business could not tell which dealers used one, two or all three products — key for upsell and retention.
- Large, granular data. Millions of lead-level rows needed a model that stayed fast and easy for analysts to use.
- A changing source system. Mid-project, the core dealer table was retired and replaced by a new universal view covering three product families.
- New questions from leadership. The client needed to know why some dealers' usage was declining, where deals were dropping out of the funnel, and whether specific tools actually improved close rates.
03 My Approach
Reviewed both legacy reports page by page, mapped every KPI to its source, and documented definitions and differences.
Designed a star-schema semantic model with shared dimensions (date, dealer) and separate fact tables per product.
Rebuilt all KPIs as explicit DAX measures in one hidden measures table, organized into display folders by product and topic.
Recreated every legacy page, then added new executive, trend, outreach and analysis pages plus a navigation hub.
Reconciled every number against the legacy reports and source data with DAX queries before release.
Ran client feedback rounds, delivered new analysis on request, and documented every change.
04 The Solution
One Unified Semantic Model
- Star schema with a shared Date dimension (2022–2027) driving every page, so all time filters behave the same everywhere.
- Separate fact tables for Deal Central leads (4.8M+ rows) and F&I deals (detailed, summary and daily grains), connected through shared dealer keys.
- Time Intelligence calculation group with 7 items (Current, MTD, QTD, YTD, Prior Year, YoY %, MoM %). One measure now works in every time view.
- Governed measure layer: implicit measures disabled model-wide so every number on every page comes from a reviewed, documented DAX measure.
- Row-level security, hierarchies and perspectives so each audience sees the right data at the right level of detail.
The 15-Page Interactive Report
| Page | What it answers |
|---|---|
| Home (navigation hub) | One-click access to every page with a short description of each. |
| Executive Summary | Headline funnel KPIs with YoY % change, colored indicators, and cross-product dealer overlap. |
| Deal Central Weekly | Weekly lead and sales performance with outcomes formatted for quick reading. |
| Dealer Performance | Dealer-level ranking and drill-down to spot leaders and laggards. |
| Usage Breakdown | Which Deal Central tools dealers actually use, and how often. |
| Recent Launch Ramp | How newly launched dealers ramp up by week and month since go-live. |
| Dealer KPI Calendar View | Day-by-day KPI heat view to spot patterns and gaps. |
| Trends & Benchmarking | Long-term trends and benchmarks across dealer groups. |
| PM Outreach – Declining Dealers | Dealers whose usage fell MoM, with where their funnel breaks. |
| F&I Intelligence Program | Program-level F&I adoption and activity. |
| Performance Summary | F&I deal outcomes and conversion KPIs. |
| Quality & Attach Analysis | ID verification, insurance verification and product attach rates. |
| F&I Non-Closer & Efficiency | Where F&I deals drop off and which steps correlate with closing. |
| DC Non-Closer & Efficiency | Where Deal Central leads drop off and which tools correlate with closing. |
| KPI Glossary & Report Links | Plain-English definition of every KPI and links to related reports. |
Deal Central Weekly
Screenshot coming soonF&I Intelligence Program
Screenshot coming soon05 Technical Highlights
Real projects are never clean. These are the problems that would have broken the report, and how I solved them:
| Problem | How I solved it |
|---|---|
| Core source table retired mid-project | Migrated the full model to the new universal dealer view: rebuilt 4 relationships, 22 measures, 5 calculated columns and 2 dependent tables, then validated exact matches. |
| Silent data regression | Found that "weeks / months since go-live" columns had gone blank for every row after migration. Rebuilt them with a dedicated go-live lookup. |
| No shared dealer ID across products | ~37% of AMD Elite dealers had no common ID. Built a unified dealer key that falls back across identifiers. |
| Custom Venn visual would not render | Diagnosed that the visual needs 0/1 indicator fields, not text categories, and built a binary indicator table. |
| Mixed number formats in one table | Used dynamic format strings so percentages show as "19.7%" and counts as "84.6" in the same table. |
| Missing step-level funnel data | Designed a reliable 4-stage funnel from available data and documented the gap instead of showing misleading numbers. |
06 Insights Delivered
| Finding | With Tool | Without Tool |
|---|---|---|
| Desking used | 49.0% close | 4.6% close |
| Manager View used | 36.9% close | 13.9% close |
| Sales View used | 35.2% close | 14.1% close |
| Insurance Verification (F&I) | 55.2% close | 31.5% close |
| ID Verification (F&I) | 50.2% close | 31.4% close |
| Protection product attached | 38.0% close | 29.8% close |
07 Business Impact
Product, sales and customer-success teams now read the same numbers from the same definitions.
Questions that needed two reports and manual merging are now answered on one page with a slicer.
The Performance Manager team gets a ready list of declining dealers and the exact funnel break point.
A three-product overlap view shows which dealers use one, two or all three products.
One model and one measure library instead of two, with a calculation group removing hundreds of duplicate measures.
The model already runs on the new universal dealer source and is structured to add new products quickly.
08 Skills Demonstrated
Have a Similar Challenge?
If your reports live in separate files, your KPIs don't match, or your Power BI model has grown hard to maintain, I can help you build one fast, trusted model your whole team relies on.