Percentile by Row

[Anonymous]

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

[v-henryk-mstf · Accepted solution]

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.

Here is the sample pbix file.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous you can do it by adding the following columns:

Percentile 10% = PERCENTILE.EXC(Table[A], .01)

Differnce = (Table[A] - Table[Percentile 10%]^2

Check 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.⚡