Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- PERCENTRANK (Inclusive)

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

PERCENTRANK (Inclusive)

10-22-2016
11:21 AM

Excel has a PERCENTRANK function (which is different from PERCENTILE functions). I would like to find the same thing for DAX. Can anyone help me figure this out? Thank you.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-23-2016
05:08 AM

Matt - I believe the PERCENTILE functions in Excel and Power BI are similar in that they return the value sitting at a given percentile.

e.g. Excel's PERCENTILE.INC( <array>, k ) is equivalent to Power BI's PERCENTILE.INC( <column>, k ).

If k = 0.5 then they would return the 50th percentile.

PERCENTRANK in Excel is the inverse of PERCENTILE in that, for a given value (that may not appear in the array) it returns the rank expressed as a percentage.

I had a go at replicating PERCENTRANK.INC in DAX.

The DAX code looks like this (excessive use of variables )

Could be some bugs but works for sample data.

PercentRank.INC = VAR PercentRankArgument = [PercentRank Argument] RETURN IF ( // Only evaluate PercentRank for values between min/max of Number[Number] inclusive AND ( PercentRankArgument >= MIN ( Number[Number] ), PercentRankArgument <= MAX ( Number[Number] ) ), // Filter Number to values less than the PercentRankArgument VAR NumberLessThanArgument = FILTER ( Number, Number[Number] < PercentRankArgument ) VAR NumberGreaterThanOrEqualArgument = FILTER ( Number, Number[Number] >= PercentRankArgument ) // RankLower = the count of Numbers less than PercentRankArgument, and is used later for interpolation of ranks VAR RankLower = COUNTROWS ( NumberLessThanArgument ) // NumberLower = the largest Number < PercentRankArgument, used for interpolation VAR NumberLower = MAXX ( NumberLessThanArgument, Number[Number] ) // NumberUpper = the smallest Number >= PercentRankArgument, used for interpolation VAR NumberUpper = MINX ( NumberGreaterThanOrEqualArgument, Number[Number] ) // PercentRankArgumentRank = the rank of PercentRankArgument over the Number table, which is just RankLower + 1. // This is the same rank as NumberUpper in the Number table itself. VAR PercentRankArgumentRank = RankLower + 1 // InterpolationFraction = fraction that PercentRankArgument is from NumberLower to NumberUpper VAR InterpolationFraction = DIVIDE ( PercentRankArgument - NumberLower, NumberUpper - NumberLower ) // Calculate the interpolated rank VAR RankInterpolated = RankLower + InterpolationFraction * ( PercentRankArgumentRank - RankLower ) // Get the count of Numbers VAR NumberCount = COUNT ( Number[Number] ) // Final PercentRank is (RankInterpolated - 1)/(NumberCount - 1) VAR PercentRankOutput = DIVIDE ( RankInterpolated - 1, NumberCount - 1 ) RETURN PercentRankOutput

My Blog

Connect on Twitter

Connect on LinkedIn

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-27-2016
05:26 AM

I think you just want to calculate the PERCENTERANK for current row value. Based on the logic for PERCENTRANK, we can add a RANK column in table and achieve same logic based on this column. Please see my sample below:

Create a rank column.

Rank = RANKX(Table6,Table6[Value])

Then create the PctRank based on the rank column.

PctRank = (COUNTA(Table6[Name])-Table6[Rank])/(COUNTA(Table6[Name])-1)

Regards,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2020
09:21 AM

@v-sihou-msft Too bad PowerBI does not have the equivalent of the PercentRank function of Excel! I like your simple approach of using Rank and a computation of the Percentile. However, are you sure about your code for RankX?? It is not working when I try to duplicate your table. I get an error with your formula. Then when I try to modify it with a calculate and sum function, I only get '1' for each row!

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-30-2019
07:55 PM

I want to see a grouping by percentile

0-20%

20-40%

40-60%

60-80%

80-100%

based on PctRank so I can see which 'Name' fall within each percentile group.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2019
01:50 PM

I actually answered my own question with this formula 🙂 Switch = SWITCH(TRUE(), AND(Table6[PctRank]>0,Table6[PctRank]<=0.20),"0-20%", AND(Table6[PctRank]>0.20,Table6[PctRank]<=0.40),"20-40%", AND(Table6[PctRank]>0.40,Table6[PctRank]<=0.60),"40-60%", AND(Table6[PctRank]>0.60,Table6[PctRank]<=0.80),"60-80%", (Table6[PctRank]>0.80),"80-100%")

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2016
01:05 PM

As I understand, percentrank in Excel takes a score and returns its percentile. This seems to be exactly what percentile in DAX does. Percentile in Excel does something different however.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-23-2016
05:08 AM

Matt - I believe the PERCENTILE functions in Excel and Power BI are similar in that they return the value sitting at a given percentile.

e.g. Excel's PERCENTILE.INC( <array>, k ) is equivalent to Power BI's PERCENTILE.INC( <column>, k ).

If k = 0.5 then they would return the 50th percentile.

PERCENTRANK in Excel is the inverse of PERCENTILE in that, for a given value (that may not appear in the array) it returns the rank expressed as a percentage.

I had a go at replicating PERCENTRANK.INC in DAX.

The DAX code looks like this (excessive use of variables )

Could be some bugs but works for sample data.

PercentRank.INC = VAR PercentRankArgument = [PercentRank Argument] RETURN IF ( // Only evaluate PercentRank for values between min/max of Number[Number] inclusive AND ( PercentRankArgument >= MIN ( Number[Number] ), PercentRankArgument <= MAX ( Number[Number] ) ), // Filter Number to values less than the PercentRankArgument VAR NumberLessThanArgument = FILTER ( Number, Number[Number] < PercentRankArgument ) VAR NumberGreaterThanOrEqualArgument = FILTER ( Number, Number[Number] >= PercentRankArgument ) // RankLower = the count of Numbers less than PercentRankArgument, and is used later for interpolation of ranks VAR RankLower = COUNTROWS ( NumberLessThanArgument ) // NumberLower = the largest Number < PercentRankArgument, used for interpolation VAR NumberLower = MAXX ( NumberLessThanArgument, Number[Number] ) // NumberUpper = the smallest Number >= PercentRankArgument, used for interpolation VAR NumberUpper = MINX ( NumberGreaterThanOrEqualArgument, Number[Number] ) // PercentRankArgumentRank = the rank of PercentRankArgument over the Number table, which is just RankLower + 1. // This is the same rank as NumberUpper in the Number table itself. VAR PercentRankArgumentRank = RankLower + 1 // InterpolationFraction = fraction that PercentRankArgument is from NumberLower to NumberUpper VAR InterpolationFraction = DIVIDE ( PercentRankArgument - NumberLower, NumberUpper - NumberLower ) // Calculate the interpolated rank VAR RankInterpolated = RankLower + InterpolationFraction * ( PercentRankArgumentRank - RankLower ) // Get the count of Numbers VAR NumberCount = COUNT ( Number[Number] ) // Final PercentRank is (RankInterpolated - 1)/(NumberCount - 1) VAR PercentRankOutput = DIVIDE ( RankInterpolated - 1, NumberCount - 1 ) RETURN PercentRankOutput

My Blog

Connect on Twitter

Connect on LinkedIn

Featured Topics

Top Solution Authors

User | Count |
---|---|

306 | |

148 | |

51 | |

50 | |

49 |

Top Kudoed Authors

User | Count |
---|---|

289 | |

169 | |

65 | |

65 | |

45 |