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

Implementation in Microsoft Power BIWhat is What-if Analysis?  

What-if analysis is a method in data analytics to manipulate and transform the 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. In this blog, we will briefly discuss the most common use cases of what-if analysis for businesses. We will also look at the simple implementation of what-if in Power BI using an electricity demand and consumption dataset. 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.

Common Use Cases of What-if Analysis in Business Analytics 

Since different businesses can 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 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 actions accordingly. 

2. Scenario Analysis for Uncertainty Management 

Another common use case of what-if analysis 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 can also assist businesses and organizations in managing and achieving goals. It helps businesses in finding various factors which can help them reach their set goals. For example, a retail chain can analyze the impact of promotions and discounts on achieving a certain sales target.

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 this 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: 

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

Next, we follow these steps: 

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

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

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

1. Select the “Modelling” tab and click “New Parameter.”  
2. Then, provide the range of values ‘What-if parameter’ can take, the increment, and the default value, as shown below: 

 

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

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: 

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: 

And then, we can assign a specific color to both these cases, as shown below: 

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: 

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

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.  

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

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

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

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

Conclusion 

As demonstrated above, What-if analysis is a useful tool with many use cases for businesses. Additionally, its implementation in Power BI is quite simple. Once used with Power BI’s DAX, What-if analysis provides a plethora of analytical possibilities to model and analyze complex business scenarios.  

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

Leave a comment below and let us know how using What-if analysis can change your business’s bottom line.