What-if Analysis in Power BI

Leveraging parameters in Power BI can take your scenario planning to the next level. In the example below, we are using mock model output of a linear regression model to perform a what-if analysis to see how changes in price will impact the demand at Duncan Farms for different vegetables.

In this case, the equation to calculate demand for Duncan farms is:

Demand = (Price * Price Coefficient) + Slope

The screenshot below shows the input data. Because we are conducting the what-if analysis based on a regression model, there needs to be columns for the coefficients and slopes that are specific for each product. You can use multiple variables in the what-if analysis, but for simplicity we are only going to use price.

Step 1: Create the Parameter

Next, name the parameter, select the corresponding data type, and set the maximum and minimum values. Because each product has different price ranges, I decided to focus the what-if analysis on a price percentage increase or decrease. To give the end user precise control over the what-if analysis, I decided to set the increment as 1% and allowed the range to be +/= 200%. Note: if you are doing a decimal increment, don’t forget to add the leading zero (i.e. 0.01 instead of .01). Once you have your parameter set, click “OK.”

Step 2: Create a Measure that Calculates the New Demand with the Parameter

In your table that contains your data, create a new measure that incorporates the parameter to calculate the demand. Because this value needs to be calculated at the row level, make sure to use the SUMX function below.

New_Forecast = SUMX(Sheet1, IF( Sheet1[Slope] + ( (1 + 'Price Parameter'[Price Parameter Value]) * Sheet1[Price] * Sheet1[Price Coefficient]) > 0, Sheet1[Slope] + ( (1 + 'Price Parameter'[Price Parameter Value]) * Sheet1[Price] * Sheet1[Price Coefficient]), 0) )

Step 3: Build Graphs to Visualize What-If Analysis

Below, I created four graphs that illustrate the current forecast as well as the what-if forecast. The top graph shows the aggregated forecast while the graphs at the bottom show the forecast by product. On the right is a slicer that allows the user to choose how much they want to increase or decrease the price to see the demand impact. The graph below shows a 0% change.

When we increase the price by 20% across the board, we see the overall demand decrease. It looks like the apple demand dropped the most, which would make sense because it had the largest coefficient.

When we increase the price by 150%, we see the demand drop even more. For pumpkins and apples, we see there is a demand of 0 for the first two weeks. This is a result from flooring the demand to 0 when the output is negative from that what-if analysis.

Now let’s see what happens when we decrease the price by 20%.

As price decreases, demand is increasing, suggesting that customers would buy more at a lower price.

When we drop the price by 150%, we can see the impact even more. While the demand significantly increase for apples and pumpkins, squash only increased by a little. This makes sense because the price coefficient for squash is the smallest of the three products.

Using what-if analyses in Power BI can give business users a way to interact with model output and aid with scenario planning. It’s extremely easy to do and customize based on what you are trying to achieve.

Thank you so much for reading. If you have any questions, comments, or feedback, send me a message at elizabeth.dinevski@tessellationconsulting.com

What-if Analysis in Power BI