Does PBI have an equivalent to Excel's Scenario Manager?

[RBear]

And, if not, what's the easiest way to duplicate it?

[MFelix]

Hi @RBear,

Check the what if to do what you need. Check the two lonks below one is the documentation and other one post with examples.

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

https://community.powerbi.com/t5/Community-Blog/What-If-Analysis-Techniques-For-Power-BI/ba-p/460414

Regards
MFelix

[RBear]

Thanks, MFelix - I'm aware of the What If tool in PBI, but that's not nearly equivalent to Scenario Manager, even if multiple What Ifs  are used simultensously.

I was thinking about coming up with a manual version, but that's too complicated. it's probably better to do it in Excel and export the outcome to PBI, but there's got to be another way...

[MFelix]

Hi @RBear,

What are you exactly trying to achieve? You probably need to make the what if analisys and then use the slicer or bookmarks to go from scenario to scenario.

Can you explain or share some sample data and what you want to achieve?

Regards,

MFelix

[RBear]

---

You probably need to make the what if analisys and then use the slicer or bookmarks to go from scenario to scenario. [Underline added].

---

That's exactly the problem with the What If slicer - it allows you to go from scenario to scenario, but it doesn't work if what you really need is a bird's eye view of all scenarios at once in order to get a sense of your options.

---

Can you explain or share some sample data and what you want to achieve?

---

Gladly. Let's take a situation involving a loan and an interest rate. To make it simple, assume it's a one year loan. After one year, the Loan Balance would simply be the Principal Amount * (1+Interest Rate). In that case, I would like to automatically generate a report that will display the Loan Balance assuming the Interest Rate is 5%, 7% or 10%. Or, for a slightly more complex report, the same but with the Loan Amount being $1,000, $5,000 or $10,000.

Obvioulsy, you can get much fancier than this, but I believe it's a good example for what a Scenario Manager can do (vs. a What If slicer).

[MFelix · Accepted solution]

Hi @RBear,

Not really sure if I got your need but I created a simple file with two tables no what if parameters needed but the principal is the same.

Loan Amount

1000
5000
10000

Rate

5,00%
7,00%
10,00%

Then I added this 3 measures:

Rate selection = SELECTEDVALUE(Rates[Rate])

Loan Amount  select = SELECTEDVALUE('Loan Amounts'[Loan Amount])

Loan Balance = 'Loan Amounts'[Loan Amount  select] * (1+ Rates[Rate selection])

Then I added a matrix visual with:

• Rows: Loan Amount (Column)
• Columns: Rate (Column)
• Values: Loan Balance (measure)

The result is below:

This would be your comparision between scenarios so all values agains all rates. Then using the slicers and bookmarks you can make it more interactive or more closed in terms of options.

Of course you can have different levels of complexity.

Check the attach PBIX file. 

regards

MFelix

[RBear]

You did it!!! That's exactly what I was looking for: homemade PBI Scenario Manager! In one important sense, it's even better than the one in Excel, because it's much simpler to provide tables with a list of the relevant variables (Loan Amount and Rate, in this case), than the laborius method used in Excel for the same purpose.

And the two sliders are just the icing on the cake....

Your solution should be used as a template for future PBI versions.

Hats off and thank you very much, MFelix!

[MFelix]

Hi @RBear

I used athe normal table but you can also use a what if created tables that is used with a generate series formula.

As I said you can add complexety to this model with dates and rates calculations etc. And the use of slicers and bookmarks can give you the additional interactivity you may desire.

Regards,
MFelix

[RBear]

---

 you can also use a what if created tables that is used with a generate series formula.

---

Can you elaborate on this, please?

Also, as an intellectual exercise - can you extend your solution to a 3D table? Meaning - add a Number of Years (during which the loan is outstanding) variable to the mix? The formula for annualy compound interest is:

Loan Balance = Loan Amount (1 + Rate) ^(Number of Years)

So if, in our example, we had a  Years  table with, for example, 2, 3 or 4 (years), how would you present it in the report? Again, just curious.

[MFelix]

Hi @RBear,

This brings me back 20 years, when I was making my degree in Managment and add to make an Excel with interest rates calculations 😄 :D.

See attach PBIX file where I have added the following:

Table: Years

Years

1
2
3
4
5

Table Type of Calculation

Type of CalculationID

Simple Interest	1
Coumpound Interest	2

Add the following Measures:

Select Type of Calculation = SELECTEDVALUE('Type of calculation'[ID])

Year Selection = SELECTEDVALUE(Years[Years])

Loan Balance Simple = [Loan Amount  select] * (1+ [Rate selection] * [Year selection])

Loan Balance Simple =
[Loan Amount  select]
    * ( 1
    + [Rate selection] * [Year selection] )

Loan Balance Compound =[Loan Amount  select]
    * ( 1 + [Rate selection] )
    ^ [Year selection]


Loan Balance =
IF (
    CONTAINS ( 'Type of calculation'; 'Type of calculation'[ID]; 1 );
    [Loan Balance Simple];
    IF (
        CONTAINS ( 'Type of calculation'; 'Type of calculation'[ID]; 2 );
        [Loan Balance Compound];
        BLANK ()
    )
)


Total interest = [Loan Balance] - [Loan Amount  select]

This will allow to have several calculations including the comparision between type of calculations simple and compound and also the total interst to pay and compared them over time:

I then just place it in a matrix or a column / line chart.

Be aware that if you change the order of the hierarchy in the matrix visual you need to drill down again to the lowest level so that the calculations are correct.

Regarding the What If tables the only difference you have is that the What If uses a table that generates values within a given interval and a given growth, that will simplify the creation of the table nothing and add options to your model since for example in exchange rates you will have 5 , 6, 7, 8, 9 and 10 instead of 5 7 and 10.  See attach also the PBIX with What if tables.

Please tell me if this is helpfull and compreenshive and I will turn this into a tutorial for the community.

Regards,

MFelix

[RBear]

---

This brings me back 20 years, when I was making my degree in Managment and add to make an Excel with interest rates calculations 😄 :D.

---

I'm glad it makes you feel young again

Great job, MFelix!

You should definitely use this for a tutorial - it's sorely need (speaking as a new user)! Please make sure it's clear and explict (me, I'm still chewing on what you did, to really understand how this whole thing works) - how SELECTEDVALUE is used to grab the exact item in a specific table, what is the function of CONTAINS (and why it's different from FILTER), etc.

On the financial side, if you ever decide to do another tuturial, this time specificly on how to use PBI as a financial tool (I believe the financial community is a great untapped pool of PBI users), I would just also make explict two unstated assumptions in this model - that the compounding period is the same as the Rate period (as in "Interest at 6%, compounded annually" vs. "Interest at 6%, compounded semiannually/quarterly/monthly" - you need to use a somewhat different function there)

and that no payments are made on the outstanding loan at any time during the Years (the formula there is even more complicated). That's a more advanced topic, obvioulsy, but it may not be a bad idea to have it covered with a tutorial.

Again, thanks for all this. I really learned something new...