What if... my denominator changes...

[Parosel]

Hi, All

I've spent the last day with our favourite search engine and looking through the forum here for an answer and can't find it. 

My situation is this I have a neumerator and denominator Activty A and Activity B with the result of 0.19 being the target for our clients, our clients have control primarily over Activity A. I have last years data for all months and want to be able to show our clients so I want to be able to show them what Activity A they will need to do each month if Activity B increase by 1%, 2%.. N% etc.

I can't help think this is a simple problem to solve and I'm just over thinking it... I've already tried to impliment a what if parameter but can't seem to get it to work how I want.

Any help would be very much appreciated in this.  

[Parosel]

@johnt75 thanks so much for this - though it didn't work. It did however give me the footing I needed to work out the logic. thanks again

[Anonymous]

Hi @Parosel ,

According to your statement, I think you want to calcualte Activity A based on dynamic Activity B. 

Activity A / Activity B = 0.19.

Here I create a sample to have a test. 

I think what if parameter should help, or you can create a calculated table directly.

Parameter = GENERATESERIES(0, 1, 0.01)

Measure:

Activity A = 
VAR _Target = 0.19
VAR _B = CALCULATE(SUM('Table'[Activity B])) * (1 + SELECTEDVALUE(Parameter[Parameter]))
RETURN
_Target * _B

 Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Parosel]

@Anonymous Thanks for this Rico - I found a different solution in the end (and ironically the business case changed and the end user then no longer needed this functionality) but this looks like an elegant solution too. 

[johnt75 · Accepted solution]

If you already have measures for Activity A and Activity B you can create a what if parameter with values ranging from 0 to 0.1, or whatever upper and lower bounds you need. You just need to remember that the percentage needs to be in decimal format, so 1% would be 0.01.

You can then use the what if parameter measure which gets created automatically for you in a new measure like

Value using what if =
var a = [Activity A]
var b = [Activity B] * ( 1 + [What if parameter measure])
return DIVIDE( a, b )