Energy Consumption Dashboard Using Power BI

Nikhil Sawhney
7 min readMar 5, 2024

--

Problem Statement:

Your company is responsible for supplying energy (water, electricity, and gas) to various buildings across different countries faces the challenge of effectively monitoring and managing energy consumption to ensure efficient operations and cost-effectiveness. However, without a centralized system to track and analyze energy usage data, the company struggles to identify trends, anomalies, and opportunities for improvement, leading to inefficiencies, increased costs, and potential service disruptions.

My Approach:

My approach to effectively monitoring and managing energy consumption involves leveraging Power BI to create dedicated pages for each energy type (water, electricity, gas). Each page will offer detailed insights into consumption patterns, anomalies, and cost-effectiveness metrics specific to that energy source. Additionally, I will have a centralized dashboard summarizing key metrics across all energy types, providing a comprehensive overview for strategic decision-making and operational efficiency improvements

Process Involved :

Use of Power Query to clean and transform raw data for analysis.
* Data Modeling to connect different tables using in power BI.
* Creating some measures using DAX for the dashboard.
*Exploratory Data analysis to find patterns and producing insights.
*Use of different visualizations to create a dashboard.

Step 1 : Reviewing the given Data in Power BI

We have given 3 tables for this analysis in file:
Table 1 - Building Master : showing that we have data of 11 different buildings.

Building Master Table

Table 2 - Energy Consumptions: showing units consumed by buildings with respective Year ( 2016–2019).

Energy Consumptions Table

Table 3 - Rates : showing per unit cost of energy unit as per the year with respective Energy Type (Water , Gas and Electricity ).

Rates Table

There were no inconsistencies found in any of three tables.

Step 2 : Data Transformation using Power Query

  1. Adding a unique “Id” column in both “Rates” Table and “Energy Consumptions” Table to make a relationship between them by merging Year and Energy Type columns so that we can make a relation between them.
Unique ID column

Now, required transformation is done and data is loaded.

Step 3 : Data Modeling

Relationships were created between Energy consumption table and Building Master Table using “Building” column and between Energy consumption table and Rates table by “ID” column we created previously.

Step 4: Creating Measures for Analysis

  1. First, I calculate Total cost incurred by the consumption of Energy Units.

2. Then, Total Cost as for all Energy types (Water, Gas & Electricity ) separately.

3. Total Units consumed

4. Unit consumed by Energy Type

5. Now, I have to calculate Percentage of “Energy Type” consumed

5. Similarly, I calculate Units that are left from total unit consumed each for energy type

Step 5 : Data Visualization and Dashboard making

Dashboard is divided into 4 sections showcasing — Overview , Water , Electricity and Gas consumption Insights

Overview

Overview Section is shows count of Buildings, Total Cost and Unit consumed with Energy Type breakdown.

Water Consumption Insights

Water consumption page contain the following elements:

  • Total Water Consumption: This section might display a headline figure representing the total amount of water used within a specific timeframe (day, week, month, etc.). The image shows a total consumption of 186,245,327 units.
  • Water Consumption by City: A chart or table could break down water consumption among different cities. The image shows a chart with New York, Chicago, Los Angeles, Phoenix, and Houston.
  • Water Consumption by Building: There could be a section that shows water consumption for each building managed by the organization. The image shows a graph with building IDs that don’t correspond to any names.
  • Water Cost by Year: A chart might illustrate the cost of water over time. The image shows a bar graph with water cost per year from 2016 to 2019.
  • Water Consumption by Year (Units): Another chart could show water usage over time. The image shows a bar graph with water consumption per year from 2016 to 2019.
  • Timeframe Selector: This would allow you to adjust the data view for different periods (e.g., daily, weekly, monthly, yearly).

Similarly, for Electricity Consumption, shown below:

Electricity Consumption Insights

and for Gas Consumption :

Insights :

1. Water is the dominant resource used, but the cost remains stable despite fluctuations in consumption:
Water consumption accounts for a significant portion (88.49%) of the total energy use. While water consumption fluctuates across years, with 2017 having the highest usage, the overall cost shows only a slight increase in 2019 compared to previous years. This suggests potential opportunities for conservation efforts to reduce water use without significantly impacting the budget.

2. Gas consumption and costs show a downward trend:
Gas consumption is the lowest compared to water and electricity but has significantly decreased from 2016 to 2019.
This decrease is reflected in the gas cost, which also shows a downward trend during the same period. This could be due to various factors, such as increased efficiency measures or a shift towards alternative energy sources for gas-powered activities.

3. Further analysis is needed to understand the factors behind these trends:
While the water consumption data includes a breakdown by building, additional context like building types or activities could offer deeper insights into usage patterns.
Similarly, understanding the reasons behind the decrease in gas consumption and cost would require further information, such as specific policy changes or infrastructure upgrades.

4. Additional insights can be gained by analyzing other aspects not shown in these images:
Comparing energy consumption and costs across different locations (e.g., cities, regions) could reveal valuable insights into potential efficiency improvements or resource allocation needs.
Analyzing the data by time of day or day of the week could help identify peak usage periods and potential areas for targeted conservation efforts.
By delving deeper into the data and exploring these additional aspects, a more comprehensive picture of the energy consumption patterns can be established, leading to more informed decision-making towards efficient resource management and cost optimization.

Recommendations:

1. Conservation Strategies: Implement targeted conservation measures based on insights from the dashboard to reduce water consumption and associated costs.
2. Efficiency Measures: Investigate factors behind gas consumption decreases and explore opportunities for further efficiency improvements.
3. Enhanced Data Analysis: Expand analysis to include factors like building types, locations, and time periods for a more comprehensive understanding of energy consumption patterns.
4. Continuous Monitoring: Implement a regular monitoring system using the dashboard to track energy trends, identify anomalies, and make data-driven decisions for efficient resource management and cost optimization.

By leveraging the power of data visualization and analysis through Power BI, our project aims to provide actionable insights that drive sustainable energy practices, cost savings, and operational efficiencies across our energy supply operations.

Dataset link :

LinkedIn :

https://www.linkedin.com/in/nikhilsawhney96/

--

--

Nikhil Sawhney
Nikhil Sawhney

Written by Nikhil Sawhney

Strong passion for Data and using it to tell stories and bring useful insights for decision making.