Overview
A mid-size retail company with 6 regional sales teams was running entirely on manually assembled Excel reports. Managers spent 3–4 hours every Monday pulling figures from a shared SQL Server database, reconciling discrepancies, and sending static screenshots to leadership. By the time decisions were made, the data was already a week old.
I was brought in to replace this process with a fully automated, interactive Power BI solution. The engagement covered everything from connecting to the source database, designing the semantic model in Power BI Desktop, writing the DAX measures, applying row-level security by region, and deploying to Power BI Service with scheduled refresh.
Key Deliverables
- Executive summary page with MTD/QTD/YTD revenue, margin, and units sold vs. target
- Regional breakdown with drill-through to individual rep performance and deal-level detail
- Sales pipeline tracker showing open opportunities by stage, age, and forecasted close value
- Forecast accuracy scorecard comparing sales predictions to actuals over rolling 12 months
- Row-level security (RLS) ensuring each regional manager sees only their own territory
- Automated daily refresh via Power BI Service gateway connected to on-premises SQL Server
Challenges & Solutions
The source database had three separate transaction tables with inconsistent date column formats and no foreign key relationships enforced. I normalised the data in Power Query using a series of custom M functions, then built a date table in DAX to serve as the universal time dimension. The result was a clean, consistent model that handled cross-table time intelligence without any ambiguity.
Implementing RLS across 6 regions while also supporting an "All Regions" view for senior leadership required a dynamic security pattern using USERPRINCIPALNAME() inside a calculated table — a common pattern that is easy to break if the model isn't properly structured from the start.