What if parameters on calculated Column

[jasemilly]

Hi 

I have some a source table that is used for some margin comparisons pulling from an SQL source, it contains a hardcoded percentage increase and a minimum cost value that work fine.

The users would like to be able to change the minimum cost value and the percentage increase value for different customers. 

I have been looking at the "what if" scenrio and parameters

The minimum cost value that has to be applied to each row before aggregating.  I am trying to use a calculated column and parameters but I don't believe this will work, I havent had any success the moment.   

If I can't pass parameters into a calculated column could I use power apps to pass back to the source SQL view and then refresh the power bi report?

If I had the percantage at 10% and the minimum cost at £35, the underlying table would look like this.

id   cost       margin increase   new price

1    30         33                         35

2    40         44                         44

3   20         22                          35

thanks for all help.

[v-jayw-msft]

Hi @jasemilly ,

What if parameter can be only used in measures not in columns.

Could you consider using measure to achieve this?

Best Regards,

Jay

[jasemilly · Accepted solution]

I have decided to create a lookup table that holds values and create a view in SQL to handle.

[Greg_Deckler]

@jasemilly You definitely can't use measures to dynamically change column values. Column values are only calculated at the time of data load/refresh. You would use a What If parameter which creates a disconnected table that you would then use in a Measure to perform some calculation, but I am not clear on the calculation you wish to perform.

[jasemilly]

I would like the user to be able specify a minimum value using the whatif then checking the value of each row in the underlying table.

if  new cost is less than the minimum it becomes the new value.

two parameters 

new minimum cost is      20

percentage increase       10%

underlying table

orignal cost    new cost after percentage increse   new cost isn't greater than minimum, so becomes minimum

10                   11                                                     20

                                                                                new cost is greayer than minimum so is ok as is

30                   33                                                    33

[amitchandak]

@jasemilly , have tried what if parameter : https://docs.microsoft.com/en-us/power-bi/desktop-what-if