cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jasemilly
Helper I
Helper I

What if parameters on calculated Column

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.

1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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
Super User
Super User

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



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors