How can I create percentile rank terms for values?

[dragonus]

For example, I have the table below:

Date	Name	Value
1/1/2020	Example1	0.779717
1/2/2020	Example1	0.758218
1/3/2020	Example1	0.963904
1/4/2020	Example1	0.722535
1/5/2020	Example1	0.536913
1/6/2020	Example1	0.247032
1/7/2020	Example1	0.552012
1/8/2020	Example1	0.938482
1/9/2020	Example1	0.04752
1/10/2020	Example1	0.381461
1/11/2020	Example1	0.392838
1/12/2020	Example1	0.188267
1/13/2020	Example1	0.625824
1/14/2020	Example1	0.142416
1/15/2020	Example1	0.53994
1/16/2020	Example1	0.33163
1/17/2020	Example1	0.297304
1/18/2020	Example1	0.35762
1/19/2020	Example1	0.040757
1/20/2020	Example1	0.362639

How should I go about creating percentile ranks for them? Also, is there a bellcurve function in PowerBI that I can use to create a chart with these values?

[v-lionel-msft]

Hi @dragonus ,

If you want to sort the values, please refer to these two measures.

Current value = MAX(Sheet1[Value]) 

_Rank = 
RANKX(
    ALLSELECTED(Sheet1),
    [Current value],
    , ASC, Dense
)

In addition, if you want to create a bell curve, please refer to this article.

Create Bell Curve and Histogram with Power BI Desktop using DAX 

Best regards,
Lionel Chen

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

[parry2k]

@dragonus there is a DAX function https://docs.microsoft.com/en-us/dax/percentile-exc-function-dax which you can use to calculate percentile. For bell curve, watch this video, great resource, and explained everything in detail. This is a multiple part video.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

[dragonus]

Hi @parry2k

I did have a look at the DAX function. However, my table is long instead of wide and thus is unable to calculate it via that formula properly. Was wondering if there's a way to calculate percentile rank for tables w the Long format (eg having example1,2,3,4,5,6 in a single row)

[v-lionel-msft · Accepted solution]

Hi @dragonus ,

“However, my table is long instead of wide and thus is unable to calculate it via that formula properly. Was wondering if there's a way to calculate percentile rank for tables w the Long format (eg having example1,2,3,4,5,6 in a single row)”

Sorry, I misunderstood what you meant. There is no such function in DAX, you must first transform the table with M in 'Edit Query', and then use the PERCENTILE.EXC() function.

Best regards,
Lionel Chen

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