cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

PERCENTRANK By Group or Filter?

So I am trying to build a PERCENTRANK column that is similar to Excel - for a given value (that may not appear in the array) it returns the rank expressed as a percentage.

 

I came across this post which is awesome and does what I'm looking for:

 

https://community.powerbi.com/t5/Desktop/PERCENTRANK-Inclusive/td-p/81208

 

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
PercentRank Argument = 
IF (
	HASONEVALUE( 'Test Number'[Test Number] ),
	VALUES ( 'Test Number'[Test Number] )
)

However, I have one caveat that I would like to add. I want to be able to do this calculation by row. So my table has stock tickers. I want to be able to do the above calculation by stock ticker. I attempted to alter the first part but got an error saying something along the lines of no values were returned:

 

PercentRank.INC = 
VAR PercentRankArgument = [PercentRank Argument]
RETURN
    IF (
        // Only evaluate PercentRank for values between min/max of Number[Number] inclusive
        AND (
            PercentRankArgument >= CALCULATE(MIN ( Number[Number] ),FILTER(Number[Ticker] = EARLIER Number[Ticker]),
            PercentRankArgument <= CALCULATE(MAX ( Number[Number] ),FILTER(Number[Ticker] = EARLIER Number[Ticker])
1 REPLY 1
v-danhe-msft
Microsoft
Microsoft

Hi @Anonymous,

Could you please post me some sample file to have a test and post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!