how to calculate percentile and values for rows in a column and arrange the as per percentile values

[srikant]

This is how I desire the data to look like, can anyone help me please 

[Greg_Deckler]

Where are the %'s coming from?

[srikant]

The above screenshot is of Excel sheet where I percentrank.exc function to calculate percentile and the values that I got were between 0.1to 1.0 then I converted these number to percentages 

[Greg_Deckler]

OK, so your source data is like you show below and you want to do what with it? I don't understand the issue I guess.

[srikant]

My source data is column a, just with the numbers and column b shows percentile values of each number in column a. I have been trying to do the same in powerbi but it is not happening  

[v-frfei-msft · Accepted solution]

Hi @srikant,

Did you use the formula like this in Excel?

=PERCENTRANK.EXC(H2:H13,H2)

We can create a calculated column as below to work on that in Power BI desktop. 

Column = VAR _ROW1 = CALCULATE(COUNTROWS(Table1),Table1[a]<=EARLIER(Table1[a]))
VAR _ROW2 = CALCULATE(COUNTROWS(Table1),ALL(Table1))
RETURN
_ROW1/_ROW2

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

according to your example I  want to made this percentile formula dynamic  as measure.  

from picture below i want to create a measure to show percentile of each branch_name base on principle they ve made.

Now i ve noidea what to do. thanks

[srikant]

Yeah, I used percentrank.exc formula

[v-frfei-msft]

Hi @srikant,

Did you try the way as I shared?

Regards,

Frank

[v-frfei-msft]

Hi @srikant ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank