Forum Discussion
Percentile by Row
Hello,
I have this column that i want to calculate using PERCENTILE.EXC
| Date | Product | X |
| 1/2/1990 | A | 0.01779078 |
| 1/3/1990 | A | -0.002563704 |
| 1/4/1990 | A | -0.008178207 |
| 1/5/1990 | A | -0.00973771 |
| 1/8/1990 | A | 0.004599524 |
| 1/9/1990 | A | -0.011788233 |
| 1/10/1990 | A | -0.006576845 |
| 1/11/1990 | A | 0.003511217 |
| 1/12/1990 | A | -0.024519231 |
| 1/11/1990 | B | 0.003511217 |
| 1/11/1990 | C | 0.003511217 |
Percentile 10% = PERCENTILE.EXC(A TABLE, 0.1)
Difference = (A-Percentile 10%)^2
This should be the end result (When I Filter Product A on a slicer).
*the one i got stuck is how to use Percentile 10% on each row.
| Date | Product | X | Pencertile 10% | Difference |
| 1/2/1990 | A | 0.017791 | -0.024519231 | 0.001790137 |
| 1/3/1990 | A | -0.00256 | -0.024519231 | 0.000482045 |
| 1/4/1990 | A | -0.00818 | -0.024519231 | 0.000267029 |
| 1/5/1990 | A | -0.00974 | -0.024519231 | 0.000218493 |
| 1/8/1990 | A | 0.0046 | -0.024519231 | 0.000847902 |
| 1/9/1990 | A | -0.01179 | -0.024519231 | 0.000162078 |
| 1/10/1990 | A | -0.00658 | -0.024519231 | 0.000321929 |
| 1/11/1990 | A | 0.003511 | -0.024519231 | 0.000785706 |
Hi Anonymous ,
According to your needs, you want to use the PERCENTILE function to get the result you want. First of all, I checked your DAX formula and found that there is something wrong with the setting of the second parameter in your PERCENTILE function. You can check the official document first, PERCENTILE.EXC (< column >, < k >), where if K is zero or blank, percentage range of 1 / (n + 1) returns the smallest value. If zero, it is out of range and an error is returned; if K is not a multiple of 1 / (n + 1), PERCENTILE.EXC Will interpolate to determine the value at the k-th percentile. So when you set the parameter to 0.1, you don't get -0.024519231. I did the following test, created two calculated columns, and got the result you want under slicer's filter.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous you can do it by adding the following columns:
Percentile 10% = PERCENTILE.EXC(Table[A], .01) Differnce = (Table[A] - Table[Percentile 10%]^2Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi Anonymous ,
According to your needs, you want to use the PERCENTILE function to get the result you want. First of all, I checked your DAX formula and found that there is something wrong with the setting of the second parameter in your PERCENTILE function. You can check the official document first, PERCENTILE.EXC (< column >, < k >), where if K is zero or blank, percentage range of 1 / (n + 1) returns the smallest value. If zero, it is out of range and an error is returned; if K is not a multiple of 1 / (n + 1), PERCENTILE.EXC Will interpolate to determine the value at the k-th percentile. So when you set the parameter to 0.1, you don't get -0.024519231. I did the following test, created two calculated columns, and got the result you want under slicer's filter.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
