Sonoma Partners Microsoft CRM and Salesforce Blog

What if Power BI could help you forecast better?

Today's blog post was written by Brendan Landers, VP of Consulting at Sonoma Partners.

A new feature in the August release of Power BI is the What If parameter. This allows you to define values for a slicer that can be used in DAX formulas allowing you to look at data differently, based on what if scenarios. From my perspective, this is applicable when looking at opportunities.

Traditionally, we look at weighted revenue in forecasting opportunities with varying levels of success. Probability is typically self-reported by the sales representative or based on an opportunity status that is self-reported by the representative. While we are working on predicting opportunity outcomes using Azure Machine Learning, I still believe past performance is the best indicator of future results. I know with certainty what percentage of opportunity revenue we’ve closed in the past, so we can apply that to future opportunities to obtain forecast.

Below I hope to highlight how you can use the What If parameter in Power BI to look at a forecast.

The example will be rather basic to illustrate the capability of the feature, but you could get quite complex with this type of analysis.

First, in the Power BI Desktop I’ll start with a simple data model that includes Customers, Opportunities, and Users. I’ll create two charts. First, using a Clustered Bar Chart I’ll show the Expected Revenue by Sales Rep. Next, using a Clustered Column Chart we’ll show the Expected Revenue by Year and Quarter.

Blanders 1
Click the image to expand.

Next I am going to add in a What If Parameter.  To do so, under Modeling in the What If section, select New Parameter. 

Blanders 2
Click the image to expand.


The What-if parameter dialog is launched.  Here I will Name my parameter What If Percentage.  I will leave the Data Type as a whole Number, and set the Minimum to 25 and Maximum to 125.  This will allow me to see what if scenarios between 25% and 125% of the current pipeline, which I will show in a minute.  Leave the Add slicer to this page box checked and click OK

You will now see a new table on the right-hand side for What If Percentage.  Under the table, you will see two options.  The top option is the slicer values.  To add this to the page, create a new slicer chart and select the field.

Blanders 3
Click the image to expand.

We can now toggle the slicer between the values of 25 and 125, but we need to apply this to the expected revenue field to see the data in motion.  To do so, I’ll create a new measure in the Opportunity table called What If Revenue.  The formula is as follows:

What If Revenue = sum(Opportunities[Expected Revenue]) * ('What If Percentage'[Parameter Value]/100)

You can see we are multiplying the Opportunity Expected Revenue with the What If Parameter (after converting it to a percentage by dividing by 100).  We now have a dynamic value in our Opportunity table.  Next, we will add the new measure as values in our bar and column charts to view the Expected Revenue side by side with the What If Revenue.

Blanders 4
Click the image to expand.

So, now you can see with an Achieve Percent of 100, the values are equal, but as I slide the slicer I see the what if value change in real-time.  For example, if I slide the slicer to 60, I can see what the numbers would look like if we hit 60% of the expected revenue.

Blanders 5
Click the image to expand.

I hope this illustrates how you can apply the new what if parameter to your Dynamics 365 for Sales data to help forecast your pipeline. Let us know if you have any questions by commenting below.

Topics: Analytics Microsoft Dynamics 365