Day: January 22, 2020

I wanted to have a look at What if Parameters in Power BI. This had passed me by so using Adventureworks I wanted to get a handle on it.

I have a simple problem. I have a sales goal Id like to achieve over the months and Id 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 each 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 was created the 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 its exactly the same as our sales but lets 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.