Power BI What If Example Using Sales Data

If you have not tried to use the Power BI What If feature, this blog post is for you.  Change how your users perform analytics with simple sliders that let them change the values in their reports to see potential impacts of decisions.  By adding in the What If parameter you enable your analysts, managers or anyone else the ability to not just look at the data but to truly do more with their data.  Common use cases are forecast vs actual, sales quota changes, sales unit changes, call center volume changes, health care claim forecasts.  Once you see what you can do with this feature, I’m certain you will find applications for it in your organization.

Power BI What If Step by Step

In this example, I am going to show you how to use 2 What If parameters in the same Power BI Desktop report.  As a sales manager, I want to be able to increase my sales forecasts from 0-10% as well as see the impact on only a single region.  You can download the file here Sales Example File

Step 1 – Power BI Desktop What If Parameter

Open Power BI Desktop and click on the Modeling tab in the ribbon, then select the New Parameter (highlighted in yellow)

CDO Advisors Power BI What If Example

CDO Advisors Power BI What If Example

The What If Parameter window is displayed for you to enter your values.  Note, you have to use numerical values in Power BI What If scenarios.

CDO Advisors Power BI What If Default Window

CDO Advisors Power BI What If Default Window

Now to create values from 0-10% fill out the What If Parameter with the following values:

CDO Advisors Power BI What If Sales Impact Values

CDO Advisors Power BI What If Sales Impact Values

Format the SalesImpact field as a percentage and then you will see a slider bar with the values 0% to 10%:

SalesImpact What If

SalesImpact What If

Now create another What If Parameter following the same process name Region with values from 0-3, as shown below:

What if Region Values

What if Region Values

Step 2 – Building the Power BI What If Report

Now we want to add new measures to leverage the sliders that were just built.

First add a Measure named SalesImpact with the following DAX.

SalesImpactMeasure = SalesImpact[SalesImpact Value]*sum(Sales[Units])
This measure takes the total units sold and multiples it by the value on the SalesImpact What If Slider.
Now create a new measure named New Sales Unit Forecast.
New Sales Unit Forecast = Sales[SalesImpactMeasure]+sum(Sales[Units])
This measure adds the additional sales to the total units.
Now create a new Measure to only apply the sales increase to the selected region.
Forecast Impact by Region = SUMX(FILTER(Sales,Sales[RegionID]=Region[Region Value]),Sales[Units]+Sales[SalesImpactMeasure])
This measure uses the region filter and adds the impacted sales to the selected region.
Now we can build a report that uses all of these measures and What If parameters to change the values on the report dynamically.  In the visual below, you can see the impact of a 5% change on Region 2 (East).
Power BI What If Example

Power BI What If Example

Most businesses lack the time and expertise to build dashboards that get results.  At CDO Advisors, we’ll build you an amazing analytics dashboard at an affordable price.  So you can quickly make data driven decisions, spot issues and improve your business.

CDO Advisors Microsoft Power BI Solutions:

BI as a Service – Low Monthly Fees, Pay Per Month Per User

Executive Dashboards – Get Dashboards in 4 Weeks

Healthcare Dashboard Proof of Concept – SEE your data visualized

Microsoft Power BI Demo – Compare your current reporting to Power BI

Power BI Quick Start Solutions – Ready to start or enhance your Power BI?

Train Your Team – Remote Power BI Developer and End User Training

Use Power BI for Finance Teams – Learn how Power BI can improve operations

Virtual Chief Data Officer – Confused by all your data?  We can help

CDO Advisors YouTube – Subscribe and Watch Our YouTube Channel