Forum Discussion
Advocate IIPercentile on Consolidated data
I want to calculate the percentile 95 from a table which has grouped data.
In the following example, I have a table which has 2 columns: Leadtime and Total (assume the **bleep** % does not exist, I just added it here for illustrative purposes).
I want to retrieve the closest possible to the Percentile 95 (in this case will be 47.5 = percentile 95.4).
| Leadtime | Total | cum_pct % |
| 0 | 455 | 1.4% |
| 0.5 | 469 | 2.9% |
| 1 | 483 | 4.5% |
| 1.5 | 224 | 5.2% |
| ... | ... | ... |
| ... | ... | ... |
| 46 | 412 | 91.4% |
| 46.5 | 382 | 92.6% |
| 47 | 405 | 93.9% |
| 47.5 | 458 | 95.4% |
| 48 | 207 | 96.0% |
| 48.5 | 257 | 96.8% |
| 49 | 442 | 98.2% |
| 49.5 | 187 | 98.8% |
| 50 | 368 | 100.0% |
Any idea for this? Appreciate it!
3 Replies
jthomsonSolution Sage
Seems similar to a question that got answered yesterday:
https://community.powerbi.com/t5/Desktop/Search-for-closest-value/m-p/348935#M156699
Not sure how the grouping of your data may affect the specifics of the approach but it should give you an idea
- PowerAnalytics
Hi!
I think the case is very different, as in this case, we do not have the values we are searching for a close approximation already calculated in a table.
It is also not a good approach to add a calculated column, as there are many filters that we want the user to be able to select which will alter the context (example: country, period, other dimensions)
- PowerAnalytics
Any other ideas? This is a though one.
