What If? A common question we ask all the time. What if we increased our revenue 20% from last year? What if we increased our margins by 2%? We ask these questions frequently, but do we do anything to solidify next steps to figure these things out?
Power BI makes it easy for us to run these scenarios in a dynamic format with What If Parameters. However, I find most people avoid using them because they need to do some DAX to integrate them. Today, I want to demystify this and help you build out your own scenarios. Our scenario will allow us to create a revenue goal that we can adjust based upon the previous year.
Setting Up Our Revenue Measures
To get started, it is important to build a measure to calculate both our current year and previous year revenue. This will provide a base to perform our comparisons.
I start with a base calculation that will summarize our sales data for the current year:
CY Revenue =
SUM(SalesAggregated[Revenue])Which results in this column chart:
Next, we will leverage our current year revenue to calculate the past year’s revenue:
PY Revenue =
CALCULATE([CY Revenue], SAMEPERIODLASTYEAR(SalesAggregated[Month]))This results in the clustered column chart we created in my article last week:
The result of our measures allows us to see the previous year’s revenue for all of 2021, but only the current year revenue for Q1 of 2022. Our goal is to next add a What If Parameter so we can create our 2022 goals.
How Does a What If Parameter Work?
What If Parameters are built out in the Power BI Report. In fact, when you create a parameter, it does a lot of the hard work for you. Let’s start by creating a parameter and see what happens in the background.
Creating a What If Parameter
To get started, go to the modeling tab and select new parameter. A new window will appear which will require some input. First thing we will want to do is set our parameter name. I am calling mine “Revenue Increase %”.
Next, we need to configure the parameter. Because we are working with percentages, we need to change the data type to decimal number. Then, we need to set our minimum and maximum values. Because we do not want to lose revenue, our minimum will be zero. Our maximum will be 2 which will allow a 200% increase of revenue.
Lastly, we need to set the incremental value. We are going to specify .05 which will allow 5% increments. We will also set our default our value at zero. You will also notice that there is a box checked to add a slicer to the report canvas. We will leave that checked and click ok.
The most obvious item created is the slicer that appears on the canvas. But there is so much more to this simple process that happens in the background.
What Else Is Created With a What If Parameter?
To answer that question, let’s head over to the table view of the report. A new calculated table shows up on the left hand side that matches the name of our parameter. When we select it, you can see there is only one column which lists our percentages from 0 to 200 in decimal format.
But how do we leverage these numbers? The slicer will limit which value is being used, but how do I build a relationship to my revenue? While there is only one column on the table, there is a measure that was created as well:
Revenue Increase % Value =
SELECTEDVALUE('Revenue Increase %'[Revenue Increase %], 0)The SELECTEDVALUE() function looks for the value you have selected with the slicer and returns a single value. In this scenario, it is looking for a single percentage value in that column that was created. If it finds more than one value, it returns a zero instead.
Is There Any Other Setup Required Before We Use It?
At this point, everything is in place for a usable What If Parameter. However, I am always a fan of cosmetics and making things look a little cleaner. Everything is currently in a decimal format, but we are looking at everything in percentages. As a result, we will want to select both the column and the measure created and make sure they are formatted as a percentage with no values after the decimal place.
We can also consider adding a card to help highlight the percentage we want to use over last years numbers so it is easy to find. We can use that measure created to make it easier to see on the report canvas. This is helpful if you are syncing the slicer across multiple report pages.
It is important to understand what is being built in the background because it lays the groundwork for using the What If Parameter in our report.
Applying a What If Parameter
Now that our parameter is set, we need to apply it to our report. The first step is creating a new measure that will calculate our goal.
CY Goal =
[PY Revenue] * ([Revenue Increase % Value] + 1)If you remember, our goal was to calculate our current year goal as a percentage over our previous year revenue. To accomplish this, we used our previous year measure and multiplied it against the measure we created from our What If Parameter and added one. Why add one? Because we want to grow our revenue, so we add one to set the base as 100% and can grow it from there.
With our new measure created, we can apply it to our column chart:
Now that everything is in place, you can adjust the goal at the beginning of the year to set the goal. At the end of the year, you can easily adjust the goal to align with what happened over the entire year to determine what your goal is for next year.
Next Steps
We started with a single What If Parameter, but if you have multiple you want to adjust, you can create more of them. In the event that you have a complicated list of values that has a lot of rules, you can consider some other options as well. While we did create everything with a few clicks, you have the ability to write all of the DAX on your own. You can build out a table, create the slicer, and a measure to leverage it. The reality is you can create flexible parameters to meet any scenario!
How about you? Do you have any reports that could benefit from a little flexibility? Have you used What If Parameters before? If so, tell me in the comments below!
2 Pingbacks