Power BI on Dataverse, secured for the sales floor
Sales leadership at a UK/IE MSP had no live view of profit, rep performance, or pipeline health. Reporting was Excel-based, stale, and inconsistent. I designed and shipped a Power BI reporting suite, built directly on a Dataverse semantic model, with 10 KPI cards, profit trend visuals, rep-level breakdowns, and a slicer-driven exploration layer.
The model is secured with Row-Level Security backed by mail-enabled Entra security groups, so reps see their numbers, managers see their teams, and leadership sees the full estate — all from a single report. The result replaced the weekly Excel ritual with live data the sales floor uses daily for quote-to-renewal decisions.
The sales team operated on D365 Customer Engagement for opportunity, account, and product data, but reporting hadn't kept pace. Weekly performance packs were assembled by hand from CSV exports, with figures that drifted between sources and meetings. By the time a number reached a manager, it was already out of date and frequently disputed.
The brief was to deliver a single source of truth that leadership trusted, that reps would actually open during the day, and that didn't depend on a person remembering to refresh a spreadsheet.
The goals were: build a Power BI semantic model on Dataverse with reusable measures; surface 10 headline KPIs covering revenue, margin, conversion, and pipeline; provide rep-level and manager-level views without duplicating reports; secure the data with Row-Level Security so visibility matches role; embed a chromeless view for the sales floor wallboard; and deliver something performant enough to use interactively.
The dashboard sits on top of a Dataverse semantic model that pulls opportunity, account, and product data from D365 Customer Engagement. Power Query handles transformation, DAX measures define the headline metrics once, and Row-Level Security roles map onto mail-enabled Entra security groups so membership management lives where IT already manages it.
The result is a single artefact serving rep, manager, and leadership audiences from one model, with security applied at the data layer rather than duplicated across reports.
Headline metrics covering revenue, margin, conversion, average deal size, and pipeline health, refreshed live against the Dataverse model.
Time-series visuals showing profit and margin movement, with drill-through into product, account, and rep level detail.
Per-rep performance against target, with manager rollups, ranked tables, and slicer-driven comparisons across teams and periods.
Rep and Manager roles enforced at the model layer via mail-enabled Entra security groups — visibility follows organisational structure.
Period, product, region, and rep slicers let users self-serve answers without bespoke report builds for every question.
Chromeless embedded view suitable for a sales floor wallboard, surfacing the live numbers the team rallies around each day.
The dashboard replaced the weekly Excel ritual; sales conversations now start from live data rather than yesterday's snapshot. The same model is reused for monthly leadership reporting, removing a parallel build that previously diverged from the sales-facing numbers.
Because security was implemented at the model layer through Entra groups, onboarding a new rep is now a group membership change handled by IT, not a report rebuild. The semantic model has become the foundation for further reporting work without revisiting the security posture each time.
Define the measures once, in the model, and never again. Every measure that lived inside a report rather than the semantic model became a maintenance liability the moment a second report needed it. Pushing logic down to the model paid back immediately.
Tie Row-Level Security to identity infrastructure that already exists. Building RLS roles directly against Entra security groups means joiner-mover-leaver flows already cover access changes — no separate process for the report. This is the same principle that underpins access reviews in an ISMS.
Build for the audience that will use it daily, not the one that will sign it off. The sales floor's needs — speed, clarity, headline numbers — drove the design. Leadership reporting fell out of the same model almost for free, but designing for leadership first would have produced a report nobody opened.