Power BI What if Parameters in Power BI
- 30th January 2020
- Debbie Edwards
In this blog I take a look at What if Parameters in Power BI, using Adventureworks to get a handle on it.
I have a simple problem. I have a sales goal the company would like to achieve over the months and I'd like to see how adding percentages onto sales would help me hit my goal.
For example, If I add 1% onto sales, would that help hit the targets?
Management have decided that we need to be selling £170M per month as their big sales target after analysing current sales.
The first thing we do is add this into the report using a constant line.
We want to know how to set Sales Quotas, what do we need to do to push towards the targets. From Modeling, select the What if New Parameter.
This is all fairly standard stuff. We want to increase by percentages for the sales forecast so we use a Fixed Decimal number. Then we want to be able to see changes from 1% to 150%.
We want to go up in increments of half a percent.
Now we have a slicer which we can move to choose percentage increases and a new table called % Sales Forecast.
Within the table are the following:
% Sales Forecast = GENERATESERIES(CURRENCY(1), CURRENCY(1.5), CURRENCY(0.05))
This will return a single column table containing values. We give it a start and and end and an increment
GENERATESERIES(<startValue>, <endValue>[, <incrementValue>])
If you go into data view you can look at the table created and see that this has created our calculated column of data for the forecast (Single Column table).
% Sales Forecast Value = SELECTEDVALUE('% Sales Forecast'[% Sales Forecast], 1)
this is the measure that has been created in the table so it doesn't exist in the table structure. It returns the value, in this case when % Sales Forecast calculated column has been filtered down to one distinct value.
Create a measure in new % sales Forecast table to take sales and multiply by the measure
Total Sales Forecast = [Total Sales]* '% Sales Forecast'[% Sales Forecast Value]
Total sales were created by creating this measure.
Total Sales = SUM(FactResellerSalesXL_CCI[Sales Amount])
I decided to add this measure here because it makes sense to hold this in the same area as all the other forecast information.
When you add this new forecast measure you get the following.
Because we are only * by 1 it's exactly the same as our sales but let's change the forecast.
Great. If we get 1.15% more sales then March should exceed our target. So if we can hit this next March, we have achieved our targets.
To create the same functionality without grouping your buttons first, it's much more complex. This is a great update and will make things so much easier in future. Click Here for the original 'how-to' solution.
If this guide was useful, and you feel you would benefit from further training, our Power BI Training Course can help you develop your practical knowledge and skillset.
If your organisation would like more information on Power BI and how to get started download our quickstart PDF below, designed to provide you with relevant actionable dashboards and the internal skillset to use them, or call us on 01246389000 or email us on email@example.com to discuss the opportunities Power BI and Azure can create for your business.