conceptual question

[Anonymous]

Hello Community!

So one of our VP's has a request to do something in a report, and not sure how to go about it.  

Here is a small section of her Excel report.    The "Risk #"  indicates, essentially, a probablity, and it applies to the values to the right  ($1,382,049 for example).    In this case, Risk 2 is equal to 75%....meaning that the value in the cell to the right ($1,382,049) is 75% of it's original value.  

I pull this same CRM data into Power Bi and it comprises to compile some CRM reports.    Our CRM is very basic so there is no chance of doing any manipulation in that system. 

My task is to somehow be able to apply this same type of Risk matrix  (1, 2, 3 each of varying probabilities), to a specific record, like she has done here.   I don't want to apply the Risk value to a whole series (a whole row or column).    Just specific records, like you see above.  

I love the Paramater what if capability in power bi and thought perhaps that might come into play.   Or creating a separate Risk # table with something like Risk 1 = 90%   Risk 2 = 75%  etc, and then somehow connecting that.    But just not sure...?

Any ideas or suggestions?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Could you please check if the below screen shot is what you want? 

If yes, you can get it by the below 2 methods:

1. Create a calculated column or measure directly with SWITCH function

COriginal value = DIVIDE('Table'[Sales],SWITCH('Table'[Risk #],1,0.9,2,0.75)) //Calculated column

MOriginal value = SUMX(VALUES('Table'[Risk #]), DIVIDE(MAX('Table'[Sales]),SWITCH(MAX('Table'[Risk #]),1,0.9,2,0.75))) //Measure

2. Create a risk table and a measure to get it just like below screen shot

Original value = DIVIDE(MAX('Table'[Sales]),CALCULATE(MAX('Risk'[Rate]),FILTER(ALL('Risk'[Risk #]),'Risk'[Risk #]=MAX('Table'[Risk #]))))

I created a sample pbix file with the above methods, you can download it from this link.

Best Regards

Rena

[Anonymous]

That is exactly it!   Thanks Ruth!!