How to get percentile ranking

[Tarpan]

Hi all,

I am having a hard time figuring out a good way to do this so I am looking for some advice.

I have a measure for ranking values.  Lets say 1-100.  The total number of rows is variable based on the data, of course.

I would like to figure out where a row eixsts within a percentile... So, I want to create a new measure that calculates that row 9 is 9/100 = 9% (rank divided by highest value rank).  Ultimately the point is to sort the ranks into 10 based percentile groups.  Easy enough once I have the percent.

My approach has been to try to divide the current value by the maximum value of that measure, but I have tried a wide of different approaches but so far I have been unable to get that maximum value to divide by.

1, 1%

2, 2%

3, 3%

100, 100%

Any ideas?

[rajivraina]

Hi,

I am also trying to get percentile ranks in a similar fashion and was wondering if you had ever solved or figured out this issue?

Please let me know.

Thanks.

[v-ljerr-msft]

Hi @Tarpan,

---

I have a measure for ranking values.  Lets say 1-100.  The total number of rows is variable based on the data, of course.

---

Could you post the measure you're using for ranking values? It's better share a sample pbix file which can reproduce your issue in this case.

In addition, I would suggest you to create a calculate column(not a measure) to get rank values for each row in this scenario. Then the solution provided by Thiyags should work.

Regards

[Tarpan]

Thanks for the feedback.  I will see if I can put together a sample. 

I tried producing a column instead of a measure but apparently I don't have enough memory to do that.

"The operation has been cancelled because there is not enough memory available for the application"

Total Rank1 = RANKX(ALL(companies[companies]),[Total Order Count])

Where Total Order Count is a measure that is a sum of orders.

This works without a problem as a measure, but I get the memory issue as a column. 

[v-ljerr-msft]

Hi @Tarpan,

Have you tried using RANK.EQ Function (DAX) to create the calculate column. The formula should like below:

Total Rank1 = RANK.EQ ( companies[orders], companies[orders] )

Regards

[Tarpan]

This ultimately returned simply '1' for every row...clearly I need to dig a bit deeper and learn a bit more about these functions and come back to this I think.  It seems like what I am trying to do is not as simplistic as I had assumed.

[Anonymous]

Are the (new?) PERCENTILEX.INC and related DAX functions of use - https://msdn.microsoft.com/en-us/library/dn802531.aspx

[Tarpan]

They didn't seem to, but it could be that it is my understanding of these functions.

[Thiyags]

Lets assume above is my dataset

For this we need to create 2 measures.

First we want to find the max Rank.

MaxRank = CALCULATE(MAX(Percentile[Rank]),ALL(Percentile[Value]))

Then 2nd measure to divide the rank by that measure

PercentileRank = DIVIDE(SUM(Percentile[Rank]),[MaxRank])

[Tarpan]

So I certainly was trying this in an all-in-one calculation...but tried to seperate it out as a measure and ultimately hit the same issue.

MaxRank = CALCULATE(MAX(Percentile[Rank]),ALL(Percentile[Value]))

The error I am seeing back is: "Calculation error in measure 'Percentile[MaxRank]: A table of multiple values was supplied where a single value was expected."

Perhaps the issue I am hitting is that there are multiple rows at that maximum value?