topic Re: What if... my denominator changes... in DAX Commands and Tips

What if... my denominator changes...

Parosel — Wed, 27 Jul 2022 07:47:36 GMT

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.

Re: What if... my denominator changes...

johnt75 — Wed, 27 Jul 2022 10:26:29 GMT

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 )

Re: What if... my denominator changes...

Parosel — Wed, 27 Jul 2022 11:25:24 GMT

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

Re: What if... my denominator changes...

Anonymous — Mon, 01 Aug 2022 06:16:31 GMT

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.

Re: What if... my denominator changes...

Parosel — Mon, 01 Aug 2022 10:47:35 GMT

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.