Power BI Dashboards for Insurance Agencies

Power BI dashboards for insurance agencies are built on data your agency already generates: policies, premiums, renewal dates and activity logs in Applied Epic, EZLynx, HawkSoft or a Vertafore system, commission statements from a dozen carriers, and whatever pipeline lives in your CRM or a producer's spreadsheet. The agency owns all of it. What it usually lacks is one view that puts those sources side by side, refreshes itself, and answers an owner's question without somebody exporting to Excel first.

This page is about the agency version of that work — the metrics principals actually run on, the reason a canned AMS report stops short of them, and how the systems get connected. If you want the general engagement, with no vertical attached, that is Power BI dashboard development. If the question is broader than a dashboard — governance, licensing, a reporting estate that has grown sideways — start at Power BI consulting services.

What an agency principal wants on one screen

Most agency owners want the same handful of answers, refreshed without being asked: how each producer is tracking against goal, what is renewing in the next ninety days and how much of it is at risk, what the book is worth and how it is shifting, and where the new business is coming from. Everything else is detail underneath those four.

The value is rarely a number nobody had before. It is having the numbers in one place, defined the same way every month, so a producer meeting starts from an agreed picture instead of an argument about whose spreadsheet is right. The dashboards below are the ones that earn their keep in a 20-to-200-person P&C or benefits agency.

Note what is deliberately not on this list: underwriting, loss ratio modelling and actuarial work. Those are carrier problems. An agency's analytics question is about distribution — who sells, who stays, and what the book is worth.

• Producer performance — written premium and commission against goal, new versus renewal, by line of business
• Retention and renewal risk — what is up for renewal, what lapsed, and which accounts show the warning signs
• Book of business — premium and revenue by client, producer, line and carrier, with concentration visible
• New business pipeline — quotes out, bound ratio, and where the referrals originate
• Carrier mix — premium by carrier, contingency and profit-sharing position, appointments that are going unused
• Service load and E&O exposure — open activities, aging service items, certificates and policies without documented follow-up

Why the reports built into your management system stop short

Every agency management system ships with reporting, and for single-system questions it is usually fine. Ask it what premium a producer wrote last month and you will get an answer. The limits show up the moment a question crosses a system boundary, and almost every question an owner cares about does.

Commissions are the clearest example. The AMS knows what was written; the carrier statement knows what was actually paid, when, and net of what adjustments. Reconciling the two is where producer compensation stops being an estimate — and no AMS report reaches into a carrier statement to do it. The same gap opens between the AMS and the CRM: the system of record knows bound business, the CRM knows what was quoted and lost, and only together do they make a funnel.

There is also a definitional problem that reporting tools cannot solve for you. Retention measured by policy count, by premium and by client household produces three different numbers, all defensible, and an agency that has not written its definition down will quietly report whichever one the current report happens to use. Settling those definitions once, in a shared model, is most of the work of making reporting trustworthy.

• Cross-system questions — AMS plus commissions plus CRM — fall outside any single tool's reporting
• History is thin: many reports show current state, not what the book looked like twelve months ago
• Exports land in Excel, get edited by hand, and the edited copy becomes the version people circulate
• Definitions drift between reports, so two "retention" numbers disagree and both are cited
• Nothing refreshes on its own, so the report is only as current as the last person who ran it

Getting agency data out of the AMS, the carriers and the CRM

How data leaves an agency management system varies by platform, by version and by contract. Some vendors offer a reporting database, an API or a scheduled export; others leave you with manual exports and a conversation with your account representative. That conversation is the first thing we have, because the answer shapes the rest of the build — and it is worth knowing that data access is frequently negotiable even when the published documentation suggests otherwise.

Carrier data is the messier half. Policy download arriving through IVANS keeps the AMS current, but commission statements still show up as spreadsheets, PDFs and portal downloads in a different format from each carrier, often on different cycles. The workable pattern is a defined drop location — a SharePoint or OneDrive folder the accounting team already uses — with the parsing and normalization handled on the way in, so nobody is retyping a statement to make a dashboard work.

Then the pieces are assembled into one model rather than six connected reports. A shared client and producer dimension is what lets a single filter move every visual on the page, and a star schema is the structure that keeps it fast as the book grows. Row-level security is the other piece agencies ask for almost immediately: producers see their own book, managers see their team, the principal sees everything — one dashboard, one set of definitions, different access.

• Applied Epic, EZLynx, HawkSoft, AMS360 and QQCatalyst all expose data differently — confirm the route before designing around it
• Commission statements need a normalization layer; parsing them by hand does not survive contact with month-end
• CRM data (HubSpot, Salesforce, Dynamics or an in-house tracker) supplies the quoted-and-lost half of the pipeline
• One shared model beats six standalone reports — clients, producers and carriers resolve to the same list everywhere
• Row-level security scopes each producer to their own book without building a separate dashboard per person

Retention and book value: define them before you visualize them

Two numbers carry more weight in an agency than any others, and both are definitional before they are technical. Retention can be measured by policy count, by premium, by client or by revenue, and the four move independently — an agency can lose small accounts and keep premium flat, which looks like health on one chart and attrition on another. Book value follows the same pattern: commission revenue, written premium and revenue net of producer splits are three different pictures of the same book.

So the model carries the definitions explicitly. Retention is calculated more than one way and each version is labelled, rather than one being chosen silently and reported as the truth. The same goes for new-versus-renewal splits, where a rewritten policy at a different carrier is either new business or retained business depending on a rule your agency should own rather than inherit from a report.

This matters beyond the weekly meeting. An agency that intends to acquire, be acquired, or borrow against its book eventually has to present those figures to somebody who will test them. Numbers that are already consistent, already reconciled to carrier statements and already reproducible are worth more at that table than a well-designed chart built on an undocumented rule.

Tracking service load and E&O exposure

Errors-and-omissions risk in an agency is mostly a paper-trail problem, and a paper trail is data. Activity and task records in the AMS already show what was promised, what was documented and what has been sitting untouched — the reporting rarely surfaces it while there is still time to act.

A service dashboard puts the aging in front of the people who can clear it: open activities past their due date, renewals approaching with no documented outreach, certificates and endorsements requested but not issued, coverage declinations without a signed record. None of that is a compliance product and it does not replace an agency's E&O procedures. It removes the excuse that nobody knew, which is where a surprising share of claims begin.

It is also the view that makes staffing arguments concrete. When service load is visible by account manager and by producer, the conversation about whether a team is under water stops being anecdotal.

How the engagement runs, and what it costs

The first conversation is about access and definitions, not visuals. What does your AMS actually let you extract, where do commission statements land today, and how does your agency define retention — those three answers determine the scope. From there the pattern is a working prototype against your real data, reviewed with the people who will use it, then extended: one dashboard that earns trust beats six that nobody opens.

Every engagement is run by Edward Pinela, MBA, a principal consultant with more than twenty years in business intelligence and enterprise data work. The person who scopes it is the person who builds it, which is why the scoping call is worth having before you have a specification written.

Dashboard projects are quoted per agency, because the AMS, the carrier mix and the state of the commission data move the number too much for a list price to be honest. If you have in-house capability and need senior Power BI hands in blocks instead of a project, Power BI Flex Support starts at $2,500 for 20 hours at $100–$125 per hour, in prepaid blocks whose hours do not expire. If the underlying data work is the bigger problem — sources that do not reconcile, no warehouse, reporting spread across tools — data analytics consulting is the engagement that comes first.

Further reading: Microsoft Learn — row-level security (RLS) with Power BI.