Forum Discussion
Percentile What If calculation
I have been asked to do this in Power BI. The following table shows a Representation of my data.
| Grouping Identifier | Value | What If Value |
| A | 10 | 5 |
| A | 15 | 7.5 |
| A | 20 | 10 |
| A | 10 | 5 |
| B | 9 | 2.86 |
| B | 18 | 5.71 |
| B | 36 | 11.4 |
| B | 20 | 6.35 |
| C | 12 | 12 |
| C | 5 | 5 |
| C | 2 | 2 |
| C | 3 | 3 |
| 25th Percentile | 8 |
I actually have hundreds of Grouping Identifiers with thousands of data points for each identifier.
The column "Value" represents the actual value. As you can see the 25th Percentile for these values is 8. Say we have a target of 5 for the 25th percentile.
The column "What If Value" represents what each record might look like if we managed to reduce the actual value by a ratio to get that Grouping Identifer to the target of 5 for the 25th Percentile. The Grouping Identifier of "C" was not modified because it already met the target 25th percentile.
These two columns have already been calculated in Power Query.
My task is to determine the overall 25th percentile if we managed to get the reach target with specific underperforming Grouping Identifiers.
Effectively
If "Grouping Identifer" selected, use "What If Value" in the Percentile.inc else use "Value" in the Percentile.inc
I've tried many approaches and can't get any to work. The selections of "Grouping Identifers" to use in the adjustment may be one or many.
Thanks in advance
1 Reply
- amitchandak
Super User
srhooey , Are you trying like this
PERCENTILE.INC(Table[value], [what if measure] )
or
measure =
var _max = maxx(allselected(whatif), whatif[value])
return
PERCENTILE.INC(Table[value],_max )
