Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])))
Solved! Go to Solution.
Try this DAX:
Percentile = RANKX ( ALL ( TableName ), CALCULATE ( SUM ( TableName[Value] ) ), , ASC, SKIP ) / COUNTX ( ALL ( TableName ), TableName[Value] )
@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 )
)
Try this DAX:
Percentile = RANKX ( ALL ( TableName ), CALCULATE ( SUM ( TableName[Value] ) ), , ASC, SKIP ) / COUNTX ( ALL ( TableName ), TableName[Value] )
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |