Departmental BI in Power BI - How to Set It Up So It Lasts

Almost every Power BI rollout I've seen in an Australian organisation has a moment where it stops being one analyst's side project and becomes something a whole department relies on. The finance team's month-end pack. The operations team's daily dashboard. The HR team's headcount reporting that the executive looks at every Monday.

That moment is where things either settle into a good pattern or start quietly falling apart. Microsoft calls this the departmental BI usage scenario, and their planning guidance on departmental BI is worth reading. Below is my version, based on what we've actually seen work across finance, operations and sales teams.

What departmental BI actually means

The scenario is simple to describe. A small group of content creators, often two to five people, builds reports and semantic models for a much larger group of consumers within the same department or business unit. Maybe 30 people. Maybe 300.

That's different from personal BI (one person building for themselves) and team BI (a handful of people collaborating on equal footing). The defining feature here is the ratio: few creators, many consumers. Consumers don't edit anything. They view reports, maybe export to Excel, maybe set up alerts and subscriptions.

It's also different from enterprise BI, where a central team builds certified content for the whole organisation. Departmental BI is decentralised. The people building the content usually sit in the department and know the business intimately. That's its biggest strength and its biggest risk.

The typical setup

Here's the pattern that works for most departments we work with:

One workspace for creators. The content creators have Member or Contributor access. This is where semantic models, reports and dataflows live while they're being built and maintained. Consumers don't get workspace access at all.

A Power BI app for distribution. Consumers access content through an app published from that workspace. Apps let you control exactly what's visible, group reports into a sensible menu, and use audiences to show different content to different groups. The finance controller sees the full P&L pack, the cost centre managers see just their own section.

Shared semantic models. One semantic model feeding several reports, not one model per report. I'll come back to this because it's where most departments go wrong.

A data gateway, if your data is on-premises. Lots of Australian mid-market organisations still have an on-premises SQL Server or an ERP sitting in a local data centre. The gateway needs an owner, and that owner shouldn't be "whoever installed it two years ago and has since left".

Licensing is the first real decision

This one catches people out. In the departmental scenario, every consumer needs to be able to view content, and how that happens depends on your licensing.

If the workspace is on shared capacity, every viewer needs a Power BI Pro licence (or Premium Per User if the content is in a PPU workspace). For 30 people that's manageable. For 300 it adds up quickly.

If the workspace sits on a Fabric capacity of F64 or above, consumers with a free licence can view content. Creators still need Pro. Below F64, you're back to needing Pro licences for viewers.

The maths here matters. We did a review for a logistics company last year where the operations department had grown to around 250 report consumers, all on Pro licences, and they were also paying for a smaller Fabric capacity for some data engineering work. Moving the departmental workspace onto an F64 and dropping Pro for most viewers roughly broke even on cost, and gave them headroom for Fabric workloads they wanted to build anyway. That's not universal. For a department of 40 people, F64 is almost certainly overkill. Run the numbers for your situation rather than following a rule of thumb.

Shared semantic models - the thing to get right

If you only take one thing from this post, make it this one.

In a lot of departments, each report has its own semantic model. Someone builds the sales report with its own import of the sales table. Someone else builds the regional report with another import of the same table, plus slightly different measure definitions. Within a year you have six versions of "revenue", and the monthly meeting turns into an argument about whose number is right.

The fix is to separate the semantic model from the reports. One well-built model, with agreed measure definitions, gets published to the workspace. Reports connect to it with a live connection. Report authors can build new visuals and pages without touching the model, and everyone gets the same number for revenue.

This does introduce a dependency. Changes to the shared model can break downstream reports. That's a good reason to have one person (or a small group) own the model, and to use proper deployment practices once the department gets serious.

A couple of practical tips:

• Endorse the model. Mark it as Promoted, or Certified if your organisation has a certification process. It tells people "this is the one to use".
• Give report builders Build permission on the model without giving them full workspace access, if they're outside the core creator group. That lets power users in the department create their own reports off trusted data.
• Document measures in the model itself. Descriptions on measures show up in the field list. It's a small thing that saves a lot of "what does Adjusted Margin actually mean?" emails.

Who owns what

Departmental BI lives or dies on clear ownership, and this is where I see the most problems.

The typical failure mode goes like this. A talented analyst in finance builds an excellent set of reports. Everyone loves them. The analyst gets promoted, or leaves, and nobody else understands how the refresh works or why there's a calculated table called "Temp_Fix_DoNotDelete". Now the department has critical reporting that nobody can maintain.

What works better:

• At least two people who can maintain each semantic model. Not just "have access", but actually understand it.
• A named owner for the gateway and data source credentials. Ideally someone in IT, even if the department owns the content.
• A short README or wiki page per model. Data sources, refresh schedule, known quirks. It doesn't need to be beautiful.
• A connection to the central BI team or Centre of Excellence, if one exists. Departmental creators shouldn't have to solve every problem alone, and the central team benefits from knowing what's being built.

Where this scenario starts to strain

Departmental BI is a great pattern, but it has limits. A few signs you've outgrown it:

• Other departments start using your content. If sales and finance both depend on the same model, it's no longer departmental. It probably belongs in an enterprise or managed self-service setup with stronger governance.
• The model is getting huge. Once you're into very large import models, incremental refresh, composite models, or DirectQuery against a data warehouse, you need someone with real data modelling depth. That's often beyond what a department can staff.
• Data engineering is happening in Power Query. If your semantic model has 40 applied steps per table merging spreadsheets from three systems, that logic belongs upstream, in a dataflow, a Fabric lakehouse or a proper data warehouse. Power Query is great, but it's not an ETL platform for business-critical data.
• Audit and compliance requirements arrive. Financial reporting that feeds regulatory submissions needs change control, testing and lineage. A departmental workspace where three people can publish directly isn't going to satisfy an auditor.

When we see these signs, the conversation usually turns to Microsoft Fabric and moving shared data preparation into a lakehouse or warehouse, with Power BI sitting on top. The department keeps building reports, but the heavy lifting moves somewhere it can be managed properly.

What I'd do if I were setting this up tomorrow

If a department head asked me to set them up properly from scratch, here's roughly what I'd recommend:

1. One workspace per department (or per major subject area if the department is large), with creator access only.
2. Publish an app for consumers, using audiences if different groups need different content.
3. Build one or two shared semantic models with agreed measure definitions. Endorse them.
4. Sort out licensing based on actual consumer numbers, not assumptions.
5. Make sure at least two people can maintain each model.
6. Get the gateway owned by IT with proper monitoring.
7. Once the content is business critical, add a deployment pipeline with separate development and production stages.

None of this is complicated. It's mostly discipline. The departments that do it well end up with reporting that people trust and use. The ones that skip it end up with a sprawl of near-duplicate reports and a monthly argument about numbers.

If your department's Power BI setup has grown faster than its structure, our Power BI consultants can help you review it and put the right patterns in place, usually without rebuilding everything from scratch. And if you're thinking about where AI fits on top of your reporting, our work on AI for business intelligence builds on exactly this kind of clean semantic model foundation. Copilot in Power BI, for what it's worth, gives much better answers off a well-documented shared model than off a pile of one-off reports.