How an energy retailer turns meter data into margin with Power BI

The data an energy retailer can’t see in time

An energy retailer is a data business underneath the brand. Every connection produces interval meter reads. Every plan carries a network tariff. Every day brings a new wholesale price in the National Electricity Market. Every billing cycle throws off signals about who is struggling to pay and who is about to leave. The raw material for good pricing and retention decisions is all there. The problem is that it rarely reaches the people making those decisions while it still matters.

In most retailers the numbers arrive late and in pieces. Margin is reported monthly, in a spreadsheet, weeks after the period it covers. Cost-to-serve is a blended average that hides which plans and regions actually lose money. Churn is noticed at month-end, once the customer has already gone. The obligations do not wait either. The Default Market Offer caps standing prices, the Australian Energy Regulator and state bodies such as Victoria’s Essential Services Commission set the rules, and AEMO’s market timetable keeps its own clock. Deciding on last month’s data, in a market that moves by the half-hour, is a structural disadvantage.

The usual response is another hand-built dashboard bolted onto a single export. It looks like progress for a quarter, then breaks. The export changes, the numbers stop reconciling with finance, and two teams end up arguing about whose spreadsheet is right. The chart was never the problem. The missing piece is a consistent, governed layer underneath it.

Why Power BI, and what sits beneath it

The aim is one trusted view of consumption and margin that the whole business reads from. We headline these builds on Power BI for three practical reasons. A semantic model defines each measure once, so every report agrees. Row-level security means each team sees only its slice. And a refresh and distribution model gets the numbers in front of people daily without anyone rebuilding a spreadsheet.

Power BI is only as good as the data beneath it, so the architecture shapes that data first. A governed data layer, built on Microsoft Fabric or Snowflake depending on the retailer’s existing stack, ingests interval meter reads, billing data and market and network costs, then models them into a clean star schema. Power BI reports from that single source rather than from ad-hoc extracts. One semantic model defines margin, cost-to-serve and consumption once, so finance and pricing open a report and see the same number calculated the same way. Query mode is chosen per use case, with pre-aggregated imports for the fast executive summary and DirectQuery where analysts need to drill to the interval level.

We separated the data layer from Power BI on purpose. Interval data at this scale punishes shortcuts, and an independent modelled layer can feed forecasting and regulatory reporting later, not just today’s dashboard.

Building it, and where it got hard

The friction in energy analytics is rarely the dashboard. It is the data, and one issue is almost guaranteed to bite. Time.

Half-hourly interval data assumes 48 intervals in a day, except on the two days a year that daylight saving starts and ends, when a day has 46 or 50. Early in the build the consumption model mishandled those two days. Totals for the changeover dates came out wrong, and because it was only two days a year, the error was easy to miss and hard to trust once found. In a business where consumption totals feed settlement and margin, “mostly right except twice a year” does not pass.

The fix was a proper date-and-time dimension that modelled intervals explicitly, handled the daylight-saving gap and fold, and reconciled daily totals back to the source so the changeover days balanced. While we were there we dealt with the other usual suspects. Reads were matched correctly to each connection’s NMI. Estimated and actual reads were kept apart so they were not double-counted. Refresh windows and the gateway were sized so the daily load finished well before the team logged on. None of this is glamorous. All of it is the difference between a dashboard people trust and one they quietly stop opening.

What changed

In a representative build the core margin and consumption reports moved from a monthly spreadsheet cycle to a daily automated refresh, so the business started deciding on current data rather than last month’s. Splitting cost into its network, wholesale and retail components by tariff and segment surfaced specific plan-and-region combinations that were losing money, which a blended average had hidden. Consumption and billing patterns flagged at-risk accounts roughly three weeks earlier than the old month-end view, giving the retention team a real window to act before a customer left.

These figures are illustrative. They describe the pattern rather than a published result for a named retailer. The shift is the point. The data that was always there starts reaching the people who price plans, manage cost-to-serve and fight churn while it is still current. That is what turns meter data into margin.

Where this fits

Consumption analytics is one application of our Data Insights and Analysis service, built on Power BI, for the utilities sector. It is a contained, high-return starting point, because the data already exists and the value comes from modelling it properly and getting it in front of the right people daily. If your margin and churn numbers arrive weeks late, the place to start is to map your meter, billing and market data and decide the handful of views that would change your decisions.