Calculating Percentiles in PowerBI

[Sid10]

In Excel, we can use percentrank.inc function to calculate the percentile scores for a list of values. How do I accomplish the same in powerbi? So, for illustration, say, I have a set of 7 values - For each value I want to know what is its percentile score. I hope to then put each value in different quartiles.

I was able to calculate the 'rank', if that helps.  

Rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Value])))

[Greg_Deckler]

@Sid10 According to Excel to DAX Translation: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

https://community.powerbi.com/t5/Community-Blog/P-Q-Excel-to-DAX-Translation/ba-p/1061107

PERCENTRANK Equivalent is here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/PERCENTILERANK/td-p/1082573

and kind of looks like this:

PERCENTILERANK.INC = 
    VAR __Value = MAX('RanksInc'[Rank])
RETURN
    IF(
        __Value IN SELECTCOLUMNS('ValuesInc',"Values",[Value]),
                VAR __NumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Value))
                VAR __NumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Value))
                VAR __Rank = __NumLower / (__NumLower + __NumHigher)
            RETURN IF(ISBLANK(__Rank),0,__Rank),
                VAR __Lower = MAXX(FILTER('ValuesInc',[Value] < __Value),[Value])
                VAR __Higher = MINX(FILTER('ValuesInc',[Value] > __Value),[Value])
                VAR __LowerNumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Lower))
                VAR __LowerNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Lower))
                VAR __LowerRank = __LowerNumLower / (__LowerNumLower + __LowerNumHigher)
                VAR __HigherNumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Higher))
                VAR __HigherNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Higher))
                VAR __HigherRank = __HigherNumLower / (__HigherNumLower + __HigherNumHigher)
            RETURN
                __LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank )
    )

[Tahreem24 · Accepted solution]

Try this DAX:

Percentile = RANKX ( ALL ( TableName ), CALCULATE ( SUM ( TableName[Value] ) ), , ASC, SKIP ) / COUNTX ( ALL ( TableName ), TableName[Value] )

[Sid10]

@Tahreem24 Thank you for that elegant solution to get to percentiles using a simple Rank and Count function!

There is a small error in your formula though. Percentile = R/(n+1) [In other words you need to add 1 to the denominator]. Please edit your answer so others are not misguided on this. 

[v-lili6-msft]

hi  @Sid10 

Just adjust the rank measure as below:

Rank 1 = RANKX(ALL('Table'[Name]),CALCULATE(SUM('Table'[Value])))

or

Rank 2 = RANKX(ALLSELECTED('Table'[Name]),CALCULATE(SUM('Table'[Value])))

Result:

Regards,

Lin