Sales vs Target — Messy Excel to Power BI

Sales-against-quota report for a bike wholesaler with 17 reps in three branches. Power Query cleans each branch's messy Excel format into one sales table and logs every row it sets aside, with the reason.

Preview

Highlights

Canada (130%) and Northwest (126%) beat target while the UK (43%) is far below it
Built and validated as code; not yet opened in Power BI Desktop

Problem

Each branch sends sales in its own Excel layout and quotas arrive as a wide quarterly sheet. Management needed attainment per rep and region, and proof that the numbers could be trusted.

Approach

Power Query reads a folder of branch files, unpivots the quota sheet and routes bad rows (duplicate sheets, overlapping resends, spacer and total rows) to a data-quality log
Beginner to intermediate: KPI cards, running totals vs target, org hierarchy, fiscal calendar, Target Stretch what-if slider, field parameters
Advanced: drillthrough rep detail, report-page tooltip, calculation group with seven time views, saved DAX queries with PASS/FAIL tests
Expert: DAX user-defined functions, calendar time intelligence, visual calculations, object-level security, CI checks

Stack

Power BIDAXPower Query (M)TMDLPBIRExcel

Outcome

Attainment is 87% overall and no rep made quota over three years, but the current fiscal year runs at 97%
1,784 of 62,639 rows were set aside with a reason; the duplicate sheet alone would have added $3.6M of sales that never happened
Model: 27 measures, 6 functions; 7 report pages, 130 visuals; both validators pass