Creating a Power BI What-If Scenario Analysis

Microsoft Power BI has the capability to model “what-if” scenarios and sensitivity analyses through the use of input parameters. From a user’s perspective, this capability transforms the product from data retrieval, data aggregation, data transformation, and visualization reports to an interactive experience allowing users to evaluate how business decisions may impact future results.

Step 1 – Data Set Up

In this article, the demonstrated what-if analysis evaluates how gross revenue changes based on the number of units sold for different products with varying prices. We have a simple table listing each product and its associated unit price. The table is named TBL_PRODUCTS. I created this example table manually within Power BI, but if you need help retrieving data from Excel, please review the Power Query to Access Excel Data from Current Workbook, Local File, or SharePoint article. Specifically, the WIDGET and GADGET products are used in this analysis.

Power BI – Example Data Table
Power BI – Example Data Table

Step 2 – Create Modeling Parameters

From the Modeling ribbon, click New parameter and select Numeric range. This new parameter captures user input for the number of widgets sold and it is used to dynamically calculate the gross revenue for the widget product line.

Power BI – Modeling New Parameter
Power BI – Modeling New Parameter

The Parameters screen is displayed. This parameter represents a Numeric range of Whole numbers with a Minimum allowed value of 0 and a Maximum allowed value of 1000 that can Increment by values of 1 with an initial Default of 0. The Add slicer to this page checkbox is checked. The new parameter is named PROJECTED_UNITS_SOLD_WIDGETS.

Click the Create button to finish creating the parameter.

Repeat this step as necessary for each input parameter. I am adding a second parameter named PROJECTED_UNITS_SOLD_GADGETS to capture gadget unit sales.

Power BI – Parameter Configuration
Power BI – Parameter Configuration

Step 3 – Basic Slicer Formatting

Since the Add slicer to this page checkbox was checked, a new slicer is added to the Report view after the Parameter screen closes. The slicer title matches its corresponding parameter name which is PROJECTED_UNITS_SOLD_WIDGETS in this example. This is not very user-friendly so let’s change the slicer title.

Power BI – New Slicer
Power BI – New Slicer

Select the newly created slicer and open the Visualizations pane. Within the Visual section under Slicer header, change the Title text. In this case, the slicer is given the new title Projected Widget Sales.

Power BI – Rename Slicer Title
Power BI – Rename Slicer Title

The slicer header text now reflects the new title text. The slicer height and width are also adjusted to better fit the report.

Power BI – Renamed Slicer
Power BI – Renamed Slicer

Repeat this step as necessary for each input parameter.

Step 4 – Calculations Using Modeling Parameters

On the Data pane, we find our data table named TBL_PRODUCTS as well as the newly created modeling parameters PROJECTED_UNITS_SOLD_WIDGETS and PROJECTED_UNITS_SOLD_GADGETS.

Power BI – Data Pane
Power BI – Data Pane

On the Report view, we find our new slicers operating within the specified minimum and maximum thresholds.

Power BI – New Measure
Power BI – New Measure

To calculate gross revenue for each product line, we create two new measures. On the Data pane, select the PROJECTED_UNITS_SOLD_WIDGETS table.

From the Table tools ribbon, select New measure.

Create a new measure with the following DAX. In summary, this measure retrieves the current unit price for a widget from TBL_PRODUCTS and multiplies it by the current input value of the PROJECTED_UNITS_SOLD_WIDGETS slicer to arrive at the gross revenue for widgets. As the user adjusts the slicer, its underlying value changes and the value of this measure automatically updates with the new gross revenue calculation. The new measure is named MSR_PROJECTED_WIDGET_SALES.

MSR_PROJECTED_WIDGET_SALES = 
    VAR vWidget_Price =
        CALCULATE (
            MAX ( TBL_PRODUCTS[PRICE] ),
            FILTER ( ALLSELECTED ( TBL_PRODUCTS ), TBL_PRODUCTS[PRODUCT NAME] = "WIDGET" )
        )
    VAR vProjected_Units_Sold_Widgets = SELECTEDVALUE ( PROJECTED_UNITS_SOLD_WIDGETS[PROJECTED_UNITS_SOLD_WIDGETS] )
RETURN vProjected_Units_Sold_Widgets * vWidget_Price
Power BI – New Measure Created (Widget Sales)
Power BI – New Measure Created (Widget Sales)

Repeat this step as necessary for each input parameter. The following is a new measure for the gadget slicer and it is named MSR_PROJECTED_GADGET_SALES.

MSR_PROJECTED_GADGET_SALES = 
    VAR vGadget_Price =
        CALCULATE (
            MAX ( TBL_PRODUCTS[PRICE] ),
            FILTER ( ALLSELECTED ( TBL_PRODUCTS ), TBL_PRODUCTS[PRODUCT NAME] = "GADGET" )
        )
    VAR vProjected_Units_Sold_Gadgets = SELECTEDVALUE ( PROJECTED_UNITS_SOLD_GADGETS[PROJECTED_UNITS_SOLD_GADGETS] )
RETURN vProjected_Units_Sold_Gadgets * vGadget_Price
Power BI – New Measure Created (Gadget Sales)
Power BI – New Measure Created (Gadget Sales)

Step 5 – Visualizations

On the Report view, add a new Donut chart visualization from the Visualizations pane.

In the Values section of the Donut chart visualization, add the measures created in the prior step, e.g., MSR_PROJECTED_WIDGET_SALES and MSR_PROJECTED_GADGET_SALES.

Power BI – New Donut Chart Visualization
Power BI – New Donut Chart Visualization

We also want to display the total gross revenue of both widget and gadget sales combined in a Card visualization.

On the Data pane, select the TBL_PRODUCTS table.

From the Table tools ribbon, select New measure.

Create a new measure with the following DAX. This measure adds the values of the MSR_PROJECTED_WIDGET_SALES and MSR_PROJECTED_GADGET_SALES measures to arrive at the total gross revenue.

MSR_TOTAL_PROJECTED_SALES = [MSR_PROJECTED_WIDGET_SALES] + [MSR_PROJECTED_GADGET_SALES]
Power BI – New Measure Created (Total Sales)
Power BI – New Measure Created (Total Sales)

On the Report view, add a new Card visualization from the Visualizations pane.

In the Fields section of the Card visualization, add the total measure we just created, e.g., MSR_TOTAL_PROJECTED_SALES. For an in-depth guide to add a total within a donut chart visualization, please review the Display Total Inside Power BI Donut Chart guide.

Power BI – New Card Visualization
Power BI – New Card Visualization

The Report view now displays a Donut chart visualization detailed by widget and gadget sales. The Card visualization displays the total combined sales of widgets and gadgets as expected. As the user changes the slicer input values, the visualizations update to reflect the calculated revenue.

Power BI – Final Report
Power BI – Final Report

Result

As expected, the report responds to user input by dynamically recalculating the underlying measures and updating corresponding visualizations. By introducing what-if analysis through the use of modeling parameters, we transform the user experience from a report viewer to active engagement with the available data beyond simple filtering.

Power BI – What-If Analysis
Power BI – What-If Analysis

Further Reading