What-if Analysis: Use Cases for Businesses & Implementation in Microsoft Power BI

Introduction

What-if analysis is a method in data analytics that manipulates and transforms data to deduce various outcomes. It is a powerful inferential mechanism to explore different scenarios based on the actual dataset and a set of variables whose values can be selected by the user. In this way, the user can examine and address the uncertainties in the data. This blog will briefly discuss the most common use cases of what-if analysis for businesses. Using an electricity demand and consumption dataset, we will also examine the simple implementation of What-if Analysis in Power BI. We will see that setting up what-if parameters in Power BI and using them in Data Analysis Expressions (DAX) measures enables us to implement and analyze all what-if scenarios from the most simple to the most complex ones.

Learn more about Business Intelligence

Common Use Cases of What-if Analysis in Power BI

Since different businesses have different processes and needs, what-if analysis can take various forms, from simple case analysis to complex forecasting. However, the following are the most common use cases of what-if analysis:

1. Predictive Analytics and Advanced Business Modeling:

The most common application of what-if analysis in Power BI is to generate future data based on existing data. In this way, businesses can model future possibilities. For example, a construction-based company may need to predict the closing cost of its open projects based on changing labor costs, materials costs, weather patterns, etc. By generating data for the future, businesses can make predictions and take action accordingly.

2. Scenario Analysis for Uncertainty Management:

Another common use case of what-if analysis in Power BI is scenario management, which involves exploring various possibilities based on variable data. For example, in the case of COVID-19, one may seek to analyze the impact of vaccination, social distancing, virus mutation, etc., on the positivity rate.

3. Goal Seeking Analysis:

Finally, what-if analysis in Power BI can also assist businesses and organizations in managing and achieving goals. It helps businesses find various factors that can help them reach their goals. For example, a retail chain can analyze the impact of promotions and discounts on achieving a certain sales target.

Read more: Features of Power BI Paginated Reports for CIOs

What-if Analysis in Power BI for Electricity Demand and Consumption Dataset

Implementing what-if analysis in Power BI is very convenient and can be broadly achieved in two steps: 

  1. Setting up What-if Parameters 
  2. Using those parameters inside Data Analysis Expressions (DAX) measures to transform the data.

For demonstration purposes, let us assume a scenario where we have data for daily average demand and supply of electricity. Through this data, we can analyze the fluctuations in demand and supply to predict days where the demand is expected to be greater than the supply for that day. For this, let us consider the following fictitious data:

this image shows Power BI for Electricity Demand - What-if Analysis in Power BI

After loading the data into Power BI, first, we write two measures in DAX to calculate daily demand and supply, as shown below:

this image shows measures in DAX to calculate daily demand and supply

Next, we follow these steps: 

  1. Select “line and stacked column chart” from the visualization pane. 
  2. Next, plot “Date” on the shared axis.  
  3. Then, add the above-created measures in the “Column Values” and “Line Values” fields.

Read more: Integrating Power BI: A Guide to Merging with Your Current Infrastructure

this image shows Column Values during What-if Analysis in Power BI

Empower your Decision-Making with AlphaBOLD's Expertise in Power BI

With AlphaBOLD, you'll leverage Power BI's advanced analytics to understand your data, predict trends, and make strategic decisions.

Request a Demo

After plotting the data, we can customize the visual per our preferences.  

The next step is to create a ‘What-if parameter’ to model fluctuations in the electricity demand. For this, follow these steps: 

  1. Select the “Modelling” tab and click “New Parameter.”  
  2. Then, provide the range of values the ‘What-if parameter’ can take, the increment, and the default value, as shown below: 
this image shows What-if parameter -Power BI’s Data Analysis Expressions

After setting up the What-if parameter, the next step is to incorporate this parameter into the “What-if daily demand” measure we created earlier. In this way, we can simulate the fluctuation in power demand through the What-if parameter. The edited measure is shown below:

this image shows What-if parameter

Now, we can change the value of the What-if parameter, which will change the daily demand data. Further, we can also place the What-if parameter inside a “Card” to confirm its value, as shown below: 

this image shows What-if parameter inside a “Card” to confirm its value

Now, we can use conditional formatting to highlight the days in the visualization where the electricity demand exceeds the electricity supply. We can do this by first writing a DAX measure that checks  days where the demand is  greater than the supply: 

this image shows electricity demand exceeds the electricity supply

Then, we can assign a specific color to both of these cases, as shown below:

this image shows specific color

Next, while we have the “line and stacked column chart” visual selected, we click the “fx” sign underneath the “default color,” then select “Field value,” and finally, select the above-created measure from the drop-down menu, as shown below:

this image shows default color - What-If analysis in Power BI

As we can see below, for days where the demand exceeds the supply, the bar will be crimson (#DC143C):

this image shows demand exceeds the supply - Power BI’s Data Analysis Expressions

In the same way, we can also incorporate variability in the electricity supply. Let us assume we also have some additional power coming in from solar panels. We can make another What-if parameter, “Solar Power,” and incorporate this into the “What-if daily supply” parameter.

Read more: Advanced AI Analytics in Power BI for CTOs: Transforming Data Strategy

This image shows What if parameter

In this case, we need to add solar power to the regular power, as shown below:

this image shows regular power

Additionally, we can calculate the number of days when the demand is more than the supply by using the following DAX:

this image shows demand is more than the supply

After placing the above-mentioned measure in a card and doing some formatting, we get the following dashboard: 

this image shows measure in a card and doing some formatting - Power BI’s Data Analysis Expressions

Elevate your Data Strategy with AlphaBOLD's Power BI Solutions

AlphaBOLD is eager to help you elevate your data strategy to new heights. Together, we can harness the power of advanced AI analytics to make informed, data-driven decisions.

Request a Demo

As we can see below, we can simulate changes in both demand and supply to predict the dates when the demand might exceed the supply: 

this image shows simulate changes in both demand and supply Power BI’s Data Analysis Expressions

Conclusion

As demonstrated above, what-if analysis in Power BI is useful for many business use cases. Additionally, its implementation in Power BI is quite simple. Once used with Power BI’s Data Analysis Expressions(DAX), what-if analysis provides many analytical possibilities for modeling and analyzing complex business scenarios.

Explore the Insights of Power BI

We hope this blog helped you understand the different dynamics of What-if analysis and how it can be used.

Explore Recent Blog Posts

Infographics show the 2021 MSUS Partner Award winner

Related Posts

Receive Updates on Youtube