What a Power BI diagnostic actually looks like
Most consultancies describe their assessment. This is the whole thing — the same document a client gets at the end of day five, with nothing removed except the client's name, because there isn't one. We ran it on Harborline Components, a fictional industrial distributor, and we published the dataset so you can check every number yourself.
Fictional company. Synthetic data. Harborline Components does not exist. The dataset was generated by us for demonstration and is free to download (CC BY 4.0). Every figure in this report reconciles against those CSV files — that's the point of publishing them. Real client work sits under NDA; this is the part we can show in public.
Scope of the review
What we were handed: one Power BI workspace, six reports built over two years by three different people, feeding a Monday morning commercial meeting.
| Item | What we found |
|---|---|
| Tables in the model | 11, all flat exports from the ERP and the CRM |
| Measures | 34, of which 3 are called some variation of "Revenue" |
| Reports on top of it | 6, plus 4 spreadsheets maintained outside Power BI |
| Data volume | 13,073 order lines · 5,212 orders · 240 customers · 120 products |
| Period covered | January 2025 – August 2026 (20 closed months) |
| Refresh | Nightly, scheduled; fails roughly twice a month without alerting anyone |
The score
The weights are fixed and published so the number means something: metric definitions 25%, model structure 20%, dates 15%, grain 15%, performance 15%, governance 10%. A score under 40 means the reports cannot be trusted for decisions until the model is fixed — which is different from saying the work was bad. Every model in this range was built by someone solving a real problem quickly, and it worked until it didn't.
The honest headline: this model is repairable. Nothing here requires starting over, and we say so in writing before anyone has signed anything.
Executive summary
- Three numbers, all called revenue. For January–August 2026 the same model can produce $13.02M, $12.96M or $12.56M. All three are arithmetically correct. Nobody wrote down which one is the company's official number, so each report picked one.
- Order counts are overstated by 2.5×. "Orders" is built by counting rows, but a row is an order line. The Monday deck says 5,344 orders; the business had 2,153.
- Margin fell and nobody reported it. Gross margin went from 27.0% in 2025 to 25.7% year to date, while revenue grew 4.9%. No report compares margin to last year, so the decline never appeared in a meeting.
- One region is failing behind a healthy average. Company on-time delivery reads 93.0%. Mexico is at 75.6%. A single headline KPI hid an entire region for at least eight months.
The ten findings, ranked by impact
1. Three measures mean "revenue"Critical
The model contains Total Sales (summed on order date), Revenue (summed on invoice date) and Net Rev. (invoice minus credits). For Jan–Aug 2026 they return $13.02M, $12.96M and $12.56M. The spread is $460K — bigger than the year-over-year growth everyone is celebrating.
- Where
- Three measures in the same model, three reports each using a different one
- Effect
- The Monday meeting argues about which number is right instead of what to do
- Fix
- One official definition (net of credits); the other two renamed Booked Orders and Invoiced before credits — 2 days
2. The fact table joins the calendar twice, and no one marked which join is officialCritical
Both order_date and invoice_date link to the date table. The median gap between them is 8 days, with a maximum of 24. Any order placed near month end lands in one month or the other depending on which report you open.
- Where
- Relationships on the sales fact table
- Effect
- Month-end revenue moves between periods with no one changing anything
- Fix
- Invoice date becomes the single active relationship; order date stays inactive and is used explicitly for the backlog measure — half a day
3. "Orders" counts rows, and a row is an order lineHigh
Orders average 2.5 lines. Counting rows returns 5,344 orders year to date; counting orders returns 2,153. Average order value is therefore understated by the same factor, which is the number the sales team is compensated against.
- Where
- The
Ordersmeasure and every visual using it - Effect
- Order volume and average order value are both wrong, in opposite directions
- Fix
- Distinct count on the order key, and a written note in the model about what the fact table's grain is — half a day
4. Eleven flat exports wired to each other instead of a star schemaCritical
Every table came out of a source system as a wide export and was then joined to whichever other table shared a column. Filters travel in directions nobody designed, and some paths are ambiguous, which is why two visuals on the same page can disagree.
- Where
- The whole model
- Effect
- Slow refresh, slow visuals, and results that can't be reasoned about
- Fix
- One fact table at order-line grain plus three dimensions (customer, product, date) — 3 days, and it is the change everything else depends on
5. The date table is not marked as a date tableHigh
It exists and it covers the period (648 days), but Power BI was never told it is the calendar. Time intelligence — year to date, same period last year — returns results that look plausible and are quietly wrong at period boundaries.
- Where
- Model settings
- Effect
- Every year-over-year figure in every report is suspect
- Fix
- Mark as date table — 15 minutes, and the single cheapest fix on this list
6. $0.30M of booked business disappears from every reportHigh
144 order lines have no invoice date yet — ordered, not yet shipped and billed. Because every report filters on invoice date, that work is invisible. It is not lost data; it is real backlog that no report shows.
- Where
- Rows with an empty invoice date
- Effect
- Operations plans against a demand picture that excludes the order book
- Fix
- An explicit backlog measure through the inactive order-date relationship, plus one page for it — 1 day
7. Credits and returns are subtracted in some reports and not othersHigh
Credits total $0.40M year to date, 3.1% of everything invoiced. Finance nets them out; the commercial reports do not. That difference alone is the gap between two of the three revenue numbers in finding 1.
- Where
- The credit amount column, used inconsistently
- Effect
- Sales and finance reconcile by hand every month
- Fix
- Folded into finding 1 — no extra time
8. Margin is falling and no report would show itHigh
Gross margin: 27.0% in 2025, 25.7% year to date. Revenue is up 4.9% over the same period last year, so the top line looks like a good year. On this revenue base, those 1.3 points are roughly $163K of gross profit that growth is hiding.
- Where
- No margin-versus-prior-year visual exists in any of the six reports
- Effect
- A real commercial problem is invisible to the meeting that would act on it
- Fix
- Margin measures with a prior-year comparison, on the page people already open — 1 day
9. A single on-time KPI hides a failing regionHigh
On-time delivery reads 93.0% company-wide. By region: Canada 96.6%, Southeast 94.3%, Northeast 94.2%, Midwest 93.2%, Southwest 93.1%, West 91.5% — and Mexico 75.6%. Mexico is small enough (275 lines) that it never moves the average.
- Where
- The headline KPI tile, with no breakdown behind it
- Effect
- One in four Mexican deliveries is late and no report says so
- Fix
- Region breakdown plus a threshold alert — half a day
10. Nothing in the model answers "which customers are we losing?"Medium
236 of 240 customers bought this year. The top 10 are 25.4% of revenue and the top 20 are 38.0% — concentrated enough that losing three or four quietly would matter, and there is no report that would notice.
- Where
- Customer dimension exists but is used only as a filter, never as a subject
- Effect
- Churn is discovered when a salesperson mentions it, not when it happens
- Fix
- A customer view with prior-period comparison — 1 day, and the first thing worth building after the model is sound
What we would not touch
This section matters more than the findings, because it is where an audit stops being a sales pitch.
- The refresh schedule and the gateway. They work. The nightly failures are caused by the model, not by the infrastructure — fix the model and the failures go with it.
- The report designs. The pages people open every Monday are laid out sensibly and are familiar. We would rebuild what feeds them, not what they look like.
- The licence tier. Nothing here is solved by paying Microsoft more. At this data volume — 13,073 rows — no capacity upgrade is justified, and anyone recommending one before fixing the model is selling you something.
- The source systems. The ERP and CRM exports are fine. The problem starts after the data arrives.
The repair plan
| Stage | What happens | Time |
|---|---|---|
| 1. Stop the bleeding | Mark the date table, fix the order count, pick the official revenue definition and rename the other two | Day 1–3 |
| 2. Rebuild the model | Star schema: one fact at order-line grain, three dimensions, one active date relationship | Week 1–2 |
| 3. Reconnect the reports | Point the six existing reports at the new model; the layouts stay | Week 3 |
| 4. Add what was missing | Backlog, margin versus prior year, on-time by region, customer view | Week 4 |
| 5. Hand over | Written model documentation, a metric dictionary, and a working session with your team | Week 4 |
That is the shape of a Power BI Rescue: four to six weeks, scope and price agreed before it starts. The diagnostic exists so that the number in that quote is real instead of a guess with a change order waiting behind it.
Check it yourself
Every figure above comes from a dataset we published. Download the kit, load the four CSV files into Power BI Desktop, paste the measures, and you will land on the same numbers — $12.56M net revenue, 2,153 orders, 25.7% margin, Mexico at 75.6%. If something doesn't reconcile, we'd want to know.
Open the live dashboard to see the same company after the repair, including the three-numbers problem side by side.
Want this run on your model?
Five business days, fixed scope, fixed price. You get the score, the findings, the repair plan and an hour with the engineer who wrote it — yours to act on with us or without us.
Ask about the diagnostic