Scenario Analysis on Specific Attributes - What If Parameters Setup, Power BI


#1

Hi there,

I want to create a scenario analysis where I only change the values with a specific attribute.

Basically, I want to select a specific city and a population scenario. See below:

Select City
Toronto
Montreal
Vancourver

Select Population Increase
20k
50k
100k

Table (nothing selected)
Country City Population
Canada Toronto 3M
Canada Vancouver 2M
Canada Montreal 2M

Table (Toronto and 100k selected)
Country City Population
Canada Toronto 3.1M
Canada Vancouver 2M
Canada Montreal 2M

How do I go about doing this?


#2

Can I see where you have got to so far?

What does your model look like?

I don’t think this is very difficult, but need more context to understand what is actually required.

You will likely need to use similar techniques to what is included in this workshop


#3

Have you also gone through this course. Everything you’ll want to learn about scenario analysis is packaged into this course module


#4

08

Something like this? Used What if scenario Sam put up

33

https://www.youtube.com/watch?v=7IEYHMvqZHY

Instead of multiplying your measure against your total population measure (which you’ll have to create just a sum) you can use addition.

Also If you’ve got only 3 cities you could what if parameter against each city, but that would give you 3 separate slicers not sure if you’d want that or if you have more than 3 cities in your data set.

You’ve got options either way :slight_smile:


#5

Hi Sam,

Thanks for the video! It helps, but I’m having trouble doing this based on my model. Here’s a sample of how my model is setup. See attached screenshot.

Basically I want to be able to increase the “Actuals” according to the work type, similar to how you did it with your products.


#6

So you want to select a specific Work type and ONLY increase that?

Or do you want to isolate the same work type everytime and increase that?

Exactly how you want this to operate is very important as they would be totally different solutions

Can you show formulas as well, as I’m still trying to guess what you actually need


#7

Hi Sam, it’s the first case. I want to select Project and increase only that.


#8

I still need more information.

What formulas do you currently have

It seems like you need exactly what is described in the workshop but I can’t be sure.

What’s wrong with just doing something as similar as the below

Need to describe exactly what you need to me or I’ll be wasting time guessing


#9

Hi Sam, let me email you my file, but basically what I want is if I select “Project” and select 10k, then the table will show only 10k increase for “Project”. I know if I select “Project”, then it filters out everything else, but I want to be able to see all my work types and see only projects increased.


#10

Ok please make sure to set your model up well to make it intuitive. Like so (simple to do)

The solution here needs to be slightly different to the workshop due to the context of the calculation that you want.

You first want to create a brand new table for your slicer

image

Then just input logic like this

If say you wanted to selected two work type this formula wouldn’t work but if it’s only one like you described it would work.

Let me know if you get this going and if you need something for multiple selections


#11

Thanks Sam! I was able to get this working. I really appreciate your help in this! Your site and forum is the best Power BI resource I’ve seen.

Cheers,
John


#12

Hi Sam,

One thing I noticed is that’s different is that my grand totals are the same, despite different values. Do you know why that is?


#13

Sorry I just realize my image was incorrect due to the filters I had on the visuals. AND yes the total doesn’t work.

My bad on this one. Technique is right though just need to adjust formula a bit.

Ok this is what I have come up with

First change the Scenario Values to this formula

Scenario Value = 
VAR SelectionCheck = SELECTEDVALUE( 'Project Table'[Work Type] ) = [Selected Type]

RETURN
IF( OR( NOT( HASONEVALUE( 'Project Table'[Work Type] ) ), SelectionCheck = TRUE() ),
      SELECTEDVALUE('Work Actuals Increase'[Work Actuals Increase] ),
            BLANK() )

Then scenario totals only need to be this

Scenario Totals = [Total Actuals] + [Scenario Value]