PERCENTRANK (Inclusive)

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 :smileyhappy: )
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