cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robarivas
Helper V
Helper V

PERCENTRANK (Inclusive)

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.

1 ACCEPTED SOLUTION

@MattAllington @robarivas

 

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.

Sample PBIX here.

 

The DAX code looks like this (excessive use of variables Smiley Happy )
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
    

 

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

6 REPLIES 6
v-sihou-msft
Microsoft
Microsoft

@robarivas

 

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:

 

11.PNG

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)

23232.PNG

 

Regards,

@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])))

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.

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%")

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. 

 

https://msdn.microsoft.com/en-us/library/dn802531.aspx



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@MattAllington @robarivas

 

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.

Sample PBIX here.

 

The DAX code looks like this (excessive use of variables Smiley Happy )
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
    

 

 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.