Summary
An energy consumption report built in Power BI for an energy management company.
The solution features two calendars to support complex benchmark comparisons across a number of dimensions.
It uses statistical process control methods to identify signals in the data and it handles parent-child relationships between meters. The overall solution also includes an Excel spreadsheet to manage the large amount of reference data required to make sense of the raw measurement data.
Requirement
The client, an energy management company installs electricity metering equipment in offices, factories and the like to accurately measure hourly consumption. They then analyse and advise on ways of reducing energy bills, implement energy saving solutions and track the results over time.
Although they had excellent software to collect and analyse the data, the reports it produced were not very user-friendly and were difficult to share with their customers. They needed Power BI report they could use with their customers that provided high level information plus the ability to drill down into detail.
They particularly wanted to be able to compare energy consumption between two time periods, answering the questions like “how does our consumption this month compare with last month?”.
Challenges
The measurement system collected data every 30 minutes for multiple meters and, in some cases across multiple sites. The data was exported to CSV and filed in a folder structure mirroring each client and their sites. This resulted in a very large volume of data having to be loaded from a lot of files with unknown filenames.
Customer-specific information – the reference data – had to be provided separately and in the case of meter names, had to exactly match the CSV data. The spreadsheet template made extensive use of conditional formatting and formulas in named ranges to ensure data quality.
Comparisons between time periods meant offsetting the dates by a number of weeks to ensure like-for-like comparisons.
Solution
The solution had 7 queries creating 5 tables plus 2 calendar tables, 7 selector tables (2 connected, 5 disconnected) and 3 measure tables with 128 DAX measures.
A reference spreadsheet was created to hold the information for each customer required to structure and populate the report: tariffs, meter hierarchy, public holidays etc as well as the location of the CSV files.
The queries used the folder names to determine the customer and site names, and loaded all CSV data from the folders into a single table.
A process was created to help the client to copy meter information from the Power BI report to the Reference spreadsheet, thereby reducing the chance of meter names not exactly matching those in the CSV data.
The report consisted of 5 pages covering summary information, high and peak utilisation, benchmark comparisons and daily profiles.
Two calendar tables were used to allow usage to be compared to a baseline that could be changed by the user.
Disconnected selector tables were used to allow different measures to be swapped in and out on the visuals and tables.
Comparisons between time periods was reported using line charts but also area charts that were colour coded to illustrate good and bad comparisons (i.e. under or over utilisation) both day by day and cumulatively over the period.
Design Highlights
This project takes a large amount of data and produces a compelling report that is easy to understand whilst still allowing complex comparisons and detail drill-down.
It illustrates the use of additional reference data that is essential for meaningful reports, and how to ensure it is in synch with the main dataset.
It features a particularly good use of area charts to illustrate the cumulative effect of energy use reduction over time.