KPI and dashboard design, interactivity, performance and Power BI Service
Design reports people trust and use, add purposeful interactivity, keep them fast, and operate them in the Power BI Service — workspaces, refresh, gateways, permissions and maintenance.
This lesson builds on: DAX fundamentals — measures, CALCULATE, iterators and RELATED.
- Define KPIs with formula, target, owner and decision
- Lay out a dashboard with clear visual hierarchy and appropriate chart types
- Use drillthrough, bookmarks and tooltips purposefully
- Diagnose slow reports with Performance Analyzer
- Publish, schedule refresh, configure gateways and manage access in the Service
KPI design
A KPI is not "a number on a dashboard". A professional KPI definition has:
| Field | Example |
|---|---|
| Name | Net revenue vs plan % |
| Business question | Are stores delivering the agreed plan? |
| Formula | Completed-order net revenue ÷ plan revenue |
| Grain & filters | Store × month; completed orders; by order date |
| Target / thresholds | ≥ 100% green, 95–100% amber, < 95% red |
| Owner | Head of Retail |
| Decision it informs | Monthly store review; target adjustments |
| Source & refresh | Orion SQL views; daily 06:00 |
Good KPIs are few, actionable, comparable (vs plan, vs last year) and hard to game. Pair volume KPIs with quality KPIs (orders and return rate; leads and conversion).
Dashboard design principles
- One purpose and one audience per page. Executive overview ≠ analyst exploration.
- Visual hierarchy: most important information top-left; KPI cards first, trends next, details last. Users read in a Z/F pattern.
- Context for every number: compared with what? (plan, last year, target) — a number without comparison is decoration.
- Right chart for the question:
| Question | Chart |
|---|---|
| Trend over time | Line chart (time on x-axis) |
| Compare categories | Sorted horizontal bar chart |
| Part of whole (few parts) | Stacked bar or 100% bar; pie only for 2–3 parts |
| Actual vs target | Bullet chart, bar with target line, variance bars |
| Relationship | Scatter |
| Detail lookup | Table/matrix with conditional formatting |
- Reduce clutter: remove gridlines, borders, 3D, unnecessary legends; label directly; consistent number formats (thousands as "k", one decimal for %).
- Colour with meaning: neutral greys for context, one accent for the focus, red/green only for good/bad — and never colour alone (colour-blind users): add icons or labels.
- Fit on one screen without scrolling for executive pages; ≤ 6–8 visuals.
When every stakeholder's request becomes a visual, nobody can find anything. Start from the questions; move detail to drillthrough pages.
Interactivity with purpose
- Cross-filtering: clicking a bar filters other visuals (Format → Edit interactions to control which).
- Slicers: date range, store, category. Sync slicers across pages where appropriate.
- Drillthrough: right-click a store → "Store detail" page filtered to that store. Create a page, add
Stores[store_name]to the Drillthrough well, add a back button. - Drill down/up in hierarchies (Year → Quarter → Month).
- Tooltips: report-page tooltips show a mini chart on hover (Page information → Allow use as tooltip).
- Bookmarks: capture a view state (filters, visible visuals) for storytelling or toggles between "Chart" and "Table" views; combine with buttons and the Selection pane.
Performance optimisation
Use View → Performance Analyzer to record each visual's DAX query, visual display and "other" time.
| Cause | Fix |
|---|---|
| Large, wide model | Remove unused columns, reduce cardinality (split datetime into date and time), use integer keys |
| Bidirectional / many-to-many relationships | Redesign as a star |
| Expensive measures (FILTER over fact tables, nested iterators) | Column filters, variables, pre-aggregate in Power Query/SQL |
| Too many visuals per page | Fewer visuals; move detail to drillthrough |
| DirectQuery against slow sources | Import, aggregations, or optimise the source |
Power BI Service
Publishing and workspaces
Publish from Desktop to a workspace in app.powerbi.com. Publishing creates two items: the semantic model (formerly dataset: data + model + measures) and the report (visuals). Multiple reports can share one semantic model — the basis of a single source of truth.
Workspaces group content for a team. Separate Development → Test → Production workspaces (or Deployment Pipelines) so users never see half-built changes.
Refresh and gateways
- Scheduled refresh (semantic model → Settings): up to 8/day with Pro, 48/day with Premium/Fabric capacity.
- Cloud sources (SharePoint Online, Azure SQL) refresh directly with stored credentials.
- On-premises sources (a SQL Server in the office, files on a local share) need an on-premises data gateway installed on an always-on machine, with the data source configured in the gateway.
- Refresh failures notify the owner — monitor them; a dashboard silently showing last week's data is worse than none.
Permissions and sharing
| Mechanism | Use |
|---|---|
| Workspace roles (Admin, Member, Contributor, Viewer) | Team members building/maintaining content |
| Apps | Distribute a curated set of reports to large audiences — preferred for consumers |
| Direct share / links | Ad hoc, small audiences |
| Row-level security (RLS) | Roles with DAX filters (e.g. [region] = USERPRINCIPALNAME() mapping) so each manager sees only their data |
| Build permission | Lets users create their own reports on your semantic model |
Viewing shared content generally requires a Pro (or Premium Per User) licence unless the workspace is on Premium/Fabric capacity; licensing changes over time, so check current Microsoft documentation for your organisation.
They can see all data. Give consumers Viewer access or an app, and test RLS with View as role.
Maintenance
- Document measures (description field), data sources and refresh schedule.
- Endorse certified semantic models; avoid duplicate models per report.
- Monitor usage metrics; retire unused reports.
- Version PBIX/PBIP files in source control (PBIP format is text-based and Git-friendly).
- Plan for source-system changes (renamed columns break refresh).
Practice
Which chart best shows monthly revenue for the last 24 months?
A report's semantic model imports from a SQL Server inside the company network. What is needed for scheduled refresh in the Power BI Service?
Regional managers must see only their region. They are currently Members of the workspace. RLS roles are configured. What is wrong?
Which elements belong in a professional KPI definition? Select all that apply.
Independent assignment
Build the Orion Retail executive page: 4 KPI cards (Net Revenue, vs Plan %, YoY %, Avg Order Value), a monthly revenue vs plan line chart, a store bar chart sorted by % of plan with conditional colours, and a drillthrough page per store. Reconcile the revenue card with the SQL query in the DAX lesson.
Summary
- Define KPIs fully (formula, grain, target, owner, decision).
- Design for one audience: hierarchy, comparison, appropriate charts, restrained colour.
- Use drillthrough, tooltips and bookmarks to keep pages simple.
- Diagnose with Performance Analyzer; optimise the model first.
- In the Service: separate environments, schedule and monitor refresh, use gateways for on-premises sources, share via apps, secure with RLS, and maintain documentation.