Let's say you have a sales data model. You can use a parameter to experiment with different discount rates to see the effect on the gross profit margin.
Taking the movies data model from my previous posts as an example, let's see how to work with a What If parameter that you use to calculate adjusted revenue. Usually, a What If parameter would apply when you have something that you would want to experiment with. Maybe you're doing budgeting or forecasting, and you have sales amounts or expense amounts, and you want to experiment with different values to multiply against maybe last year's values to come up with some proposed current-year values. You will want to see how the results roll up to grand totals, be able to filter, and so forth.
But the movies data model doesn't lend itself well to 'What If' analysis, so you're going to use your imagination here a little bit and say, what if you have a numeric value and you need to apply some computation to it? On the Modeling tab of the ribbon, click New parameter, and let's call this Multiplier.
After adding this parameter, what happens is you get this slicer that has a slider in it.
A Multiplier table was created too, and it has a Multiplier and a Multiplier Value column. Multiplier is a calculated column, and the values go from 0 to 20. That's the range that you specified when you set up the parameter. There is the expression 'GENERATESERIES(0, 20, 1)'. That's the range of possible values with a minimum of zero, a maximum of 20, and then the increment by 1 that produces this list of possible values.
The other item in the table is Multiplier Value, and it looks at the Multiplier column and uses the 'SELECTEDVALUE' function, which wants a column name and an alternate result. Whatever you change in the Multiplier box will be the value returned by the expression and Multiplier Value, but if there is no selection, then you have the default, which is set as 1. So that all got created automatically when you created the new What If parameter.
To use it, you need a new measure. The below example is a snippet that can be used to calculate Adjusted Revenue.
Adjusted Revenue = [Total Revenue] * Multiplier[Multiplier Value]It takes the Total Revenue and multiplies it by the Multiplier Value.
Create a new measure for this calculation, paste in the code for the Adjusted Revenue measure, change the formatting to Whole number, set the comma, and then add in this new Adjusted Revenue measure.
And now, you can experiment with the multiplier slicer to try different values and then watch the Adjusted Revenue value change.
