Forum Discussion
Percentile in a specific range
Do you need to display multiple formulas on a single chart? In order to help you better, could you please provide some sample data and the expected results based on these sample data? That would be very helpful.
My suggestions so far are as follows for your reference:
First, calculate the percentile value corresponding to FW=1, FW<=2, and FW<=3, and then use the following formula:
Measure =
SWITCH(TRUE(),
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW=1"), [FW=1 formula],
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW<=2"), [FW<=2 formula],
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW<=3"), [FW<=3 formula])
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply
below an example
FWTime
| Column A [FW] | Column B [Time] | ||||
| 1 | 437.90 | Percentile 0.5 FW1 | 289.0885127 | Percentile when FW<=1 | |
| 1 | 320.53 | Percentile 0.5 FW2 | 232.5888831 | Percentile when FW<=2 | |
| 1 | 257.65 | Percentile 0.5 FW3 | 143.3801389 | Percentile when FW<=3 | |
| 1 | 249.50 | Percentile 0.5 FW4 | 139.7399248 | Percentile when FW<=4 | |
| 2 | 215.68 | ||||
| 2 | 143.48 | ||||
| 2 | 143.28 | ||||
| 2 | 139.95 | ||||
| 3 | 139.53 | ||||
| 3 | 130.59 | ||||
| 3 | 125.82 | ||||
| 3 | 122.80 | ||||
| 4 | 117.86 | ||||
| 4 | 111.55 | ||||
| 4 | 110.81 | ||||
| 4 | 110.77 |
For calculate of the percentile I used the below formula
Percentile 0.5 FW1 =PERCENTILE.INC(B2:B5;0.5)
Percentile 0.5 FW2 =PERCENTILE.INC(B2:B9;0.5)
Percentile 0.5 FW3 =PERCENTILE.INC(B2:B13;0.5)
Percentile 0.5 FW4 =PERCENTILE.INC(B2:B17;0.5)
I used in PowerBI the formula