Most firms already have the data for matter profitability and lock-up. What they lack is one model that reconciles it. How we build that from the PMS.
The number partners argue about
There is a meeting that happens in most law firms. Finance presents lock-up by department, a head of department says the figure cannot be right because three large matters billed last week, and the conversation moves on without a decision. The data was not wrong. It was pulled from the practice management system on a different day, with a different definition of a billed matter, than the figure the head of department had in their head. Nobody trusts the number, so nobody acts on it.
That is the problem Power BI solves for a law firm, and it is worth being precise about it, because it is not a visualisation problem. Firms do not lack charts. They lack one model, refreshed on a schedule everyone knows, where matter profitability, work in progress, lock-up and utilisation are calculated once from the same data and reconcile to the ledger. Our Power BI for legal page describes the engagement; this post is about what is inside it.
Where the data is
The good news is that the data exists. Practice management systems hold time recorded by fee earner and matter, disbursements, bills raised, cash received, and the matter's client, department and office. The finance system holds the ledger. HR holds the fee earner's grade, target hours and cost. That is everything matter profitability and lock-up need.
The bad news is that it exists in three places with three sets of identifiers, and most firms bridge them with a monthly spreadsheet that one person understands. The first piece of work is therefore not a dashboard. It is bringing time, billing, cash and fee earner data into one governed model with the matching rules written down. For most firms that is a modest Fabric lakehouse or Azure SQL layer fed from the practice management system's database or API, and it is a weeks not months job because the data is well structured. Our data platform and warehouse work is that layer.
The four reports, and what each one needs
Matter profitability. Revenue billed on the matter, less the cost of time recorded at each fee earner's cost rate, less disbursements written off. The hard part is the cost rate: it has to come from HR or finance, be dated so a promotion changes the rate from the right month, and be agreed by the finance director before anyone sees a margin. We have seen firms spend a year arguing about profitability because the cost rates were somebody's estimate.
WIP. Time recorded and not yet billed, valued at charge rates, aged. The definition to settle is what counts as recorded: time entered, or time approved. Pick one and label it.
Lock-up. WIP plus debtors, expressed in days of revenue, by department and fee earner. Lock-up is the number the partnership cares about because it is cash, and it is the number most often wrong because it needs billing and cash receipts to reconcile to the ledger. If the model's debtor figure does not match finance's, the report is dead on arrival, so the reconciliation is built into the refresh and shown on the page.
Utilisation and realisation. Hours recorded against target, and the proportion of recorded time that ends up billed and collected. Realisation is where the uncomfortable truths live, and it is the report fee earners will check most carefully, so the time data has to be right to the hour.
Why it stops being trusted, and how to prevent it
Reports in law firms lose trust in three ways. The definition drifts, because someone changes a measure in one report and not another. The refresh fails silently and the figures go stale. Or the firm restructures, a department splits, and historic matters are suddenly in the wrong place. All three are governance problems, and the answers are the ones we apply everywhere: one semantic model with the measures defined once, monitoring that tells someone when a refresh has not run, and a dated department mapping so history stays where it was. Data governance that people actually follow is the short version of the approach.
There is also a people point. The first version of a matter profitability report will show some partners' matters making less than they believed. If the report arrives as a surprise at a partners' meeting, it will be attacked and the attack will be about the data. If each head of department has seen their own figures first, with the definitions explained, it will be accepted. We build that walkthrough into the delivery.
Sharing it beyond the firm
Some firms want to show clients their own matter spend, or give a referrer a view of the work they send. Doing that with a Power BI licence per external user is expensive and awkward. Our Power BI Portal approach lets a firm share governed reports externally without licensing each reader, which is also how multi-office firms give each office its own view of one model.
Where to start
Start with lock-up by department, reconciled to the ledger, refreshed nightly. It is one report, it is the one the partnership already argues about, and getting it trusted earns the right to build the rest. From there, matter profitability with agreed cost rates, then utilisation and realisation. The whole set is a short programme, not a year, and it runs on data you already have. If you want a view of how far off that your firm is, our free audit is the place to start.
Related reading
Design the report before you build it
Your pipeline succeeded. That doesn't mean the data is right
Data governance that people actually follow, not a policy nobody reads
Choosing a Power BI consultancy
Simon Devine
Managing Director
Part of the Hopton Analytics team, delivering governed analytics programmes for UK mid-market organisations.
