Help with What If Parameter to Calculate Average

[rsbin]

Happy Friday Folks,

It has been a long week.  Trying to close it out by using a What If Parameter for the first time.

I have it set up the way I want...User can select a whole number between 1 and 20

TIY = GENERATESERIES(1, 20, 1)

I have the following table:

Flag	TIY
0	60
0	208
0	28
0	36
0	26
1
1
1
1

And a Measure that calculates Average. 

TIY_Avg = Average( MyTable[TIY] )

  My new What If scenario goes like this:  If Flag = 0 keep existing [TIY], but if Flag is 1, then TIY = SelectedValue from the What If Slicer.

Then recalculate the Average.  What would my new TIY_WhatIf_Avg Measure look like?

Appreciate any guidance late on a Friday afternoon.

Thanks and regards,

[v-henryk-mstf]

Hi @rsbin ,

Whether the advice given by @hnguy71  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[dhruvinushah]

Try to see if this works: 

WhatIfTIYMeasure = IF(ALLSELECTED(MyTable[Flag]) = 0, AVERAGE(MyTable[TIY]), AVERAGE(TIYWhatIF[TIYWhatIf]))

Hope this helps.

[rsbin]

@dhruvinushah ,

Appreciate you taking the time to reply, but unable to make sense of your solution.

Allow me to clarify.  In my WhatIf scenario, I want to take the average of all the values in the table, assuming all of the blanks get filled in with the parameter value.  I don't think your solution accomplishes this.

[hnguy71]

@rsbin ,

How about this:

TIY_Avg = 

VAR _FlagType = SELECTEDVALUE('Table'[Flag], 0)
VAR _WhatIfValue = SELECTEDVALUE(TIY[TIY])

VAR _BaseTbl = SUMMARIZE(ALL('Table'), 'Table'[Flag], 'Table'[TIY])
VAR _WhatIfTbl = ROW("Flag", 0, "TIY", _WhatIfValue)

VAR _AdjustTbl = UNION(_BaseTbl, _WhatIfTbl)

RETURN

SWITCH(_FlagType,
    0, AVERAGEX(_BaseTbl, [TIY]),
    1, AVERAGEX(_AdjustTbl, [TIY])
)

[rsbin · Accepted solution]

@hnguy71 ,

Thank you for taking the time to try to provide a solution.  I also thought about going down the path of creating a temp table, but came across the issue of having too many filter options for the User.

Finally realized, I could get around this by simply calculating my Average arithmetically - summing up my TIY and counting my rows.

Appreciate you taking the time to respond though.

Enjoy the rest of the weekend and Best Regards,