Dynamically changing assumptionss from Report view

[MVaughan1]

Hello, I am trying to find a way of displaying assumptions that can be changed in the Report view.

I cannot share my data for confidentiality, but here is a representative sample of the data

	A	B	C	D	E
	Product  Name	Actual Revenue	Forecast Revenue	Last Year Revenue	Is Actual Revenue(B) within 10% (Tolerance) of Forecast Revenue (C) and Last Year Revenue (D)
1	X	900	1000	800	No
2	Y	700	750	700	Yes
	Tolerance	10%

My ideal scenario is to have a Tolerance level filter displayed on the report which can then be altered dynamically by the end user.  So in my table above, the ability to select for example 15% which would then alter column E results )

Any suggestions very gratefully received.

Thanks,  Mark

[v-yuta-msft]

Hi MVaughan1,

"Is Actual Revenue(B) within 10% (Tolerance) of Forecast Revenue (C) and Last Year Revenue (D)"

<--- Could you clarify more details about your logic and expected result?

Regards,

Jimmy Tao

[kohlivinayak]

Hi @MVaughan1

I am hoping you already have the formula for the calculation and you are struck with the dynamic tolerance.

Create a new table (in excel or in power bi) having one column with values from 1 to 100 (or values you want your user to filter the tolerance on)

Use this coumn on the filter visual on the report (single selection only enabled).

create a measure as below

Tolerance value = selectedvalue(tolerancetable[column])

And in the formula use this measure in place of tolerance percentage.

Thanks

Vinayak

[MVaughan1]

HI @kohlivinayak

First of all thank you very much for this - I really appreciate this guidance.   

I think I'm nearly there, but I appear to have a glitch which I wonder if you could help me on.

I have followed your instructions which were great, but I can't seem to get the Tolerance Value to change my column.   I have tested my formula and it works (so I can replace the Tolerance Value with eg "0.1" and it works).    The Tolerance Value appears to work because my table refreshes every time I change the Rev Tolerance (Filter)Value, but the results are always the same ie it shows a 1 for any negative figure in the "vs PY" column. 

Do you have any further ideas on this please ?

Mark

With the selection I would expect a "1" to appear in each of the rows in column "Rev vs PY (Risk)" apart from row 4 and 5

[kohlivinayak]

Hi @MVaughan1

Please share the formula for us to have a look.

Thanks

[MVaughan1]

Hi again @kohlivinayak

Rev vs PY (Risk) = IF('Finance data (2)'[vs PY]<[Tolerance Value],1,0)

If I swap [Tolerance Value] for eg "0.1", the values change. 

This is my Tolerance Measure

Tolerance Value = SELECTEDVALUE(Table1[Rev Tolerance])

And below I have shown a picture of the Rev Tolerance table.

[kohlivinayak]

The value you are puting in rev table is text.

Please change it to 0.1, 0.2, 0.5 as such decimal values. (check data type before exiting the edit query, it should be decimal)

In the modeling tab you have an option to display it as % where it will multiply the number with 100 and add a % in end of it by default.

Select the column name on the right side, go to modeling tab and below format just select % option.

[MVaughan1 · Accepted solution]

Hi @kohlivinayak,

I have got this to work, not because of the decimal but because my formula was in a calculated column rather than a measure.  

The Selected Value and IF HAS ONE VALUE functions both seem to rely on measures.   Not sure if this is basic Power BI chapter 1 that I skipped over but certainly a key learning point for me.       What is a bit of a pain is that it seems as though I will have to convert all my existing columns into measures.  I got this to work by changing my vs PY to a "SUM vs PY" measure..     I don't really understand why this is needed, but I shouldn't take any more of your time on this.

Thanks once again,  really appreciate your help.

Mark