Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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,
Try to see if this works:
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.
@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])
)
@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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |