cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nobodyukno Member
Member

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 Super Contributor
Super Contributor

Re: PERCENTRANK By Group or Filter?

Hi @nobodyukno,

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 110 members 1,080 guests
Please welcome our newest community members: