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.
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.
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.
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.
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.
The slicer header text now reflects the new title text. The slicer height and width are also adjusted to better fit the report.
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.
On the Report view, we find our new slicers operating within the specified minimum and maximum thresholds.
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
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
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.
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]
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.
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.
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.
