Results from a What-If Parameter

[Anonymous]

I have a What-If Parameter that let's you input a certain investment amount ($). Based on the amount entered, it calculates your total savings. Here is an example. Recommended Incremental (Per Hour) is the investment amount ($) and incremental savings is the savings.

We run this multiple times for different scenarios to understand what the investment amount is that yields the highest savings. Is there a way we can automatically get the investment amounts for each instance where savings is maximized?

[AlexisOlson]

There's not a solver or goal-seek equivalent in Power BI like there is in Excel, nor can you write recursive loops in DAX.

However, there does remain the brute-force method of iterating through each parameter value, calculating the savings, and taking the max. Such a calculation might look like this:

MaximalSavingsAmount =
VAR AllScenarios = ADDCOLUMNS ( ALL ( Parameter[Amount] ), "@Savings", [Incremental Savings] )
VAR MaxSavings = MAXX ( AllScenarios, [@Savings] )
RETURN
    MINX ( FILTER ( AllScenarios, [@Savings] = MaxSavings ), Parameter[Amount] )