cancel
Showing results for
Did you mean:
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])```
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.

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 110 members 1,080 guests
Recent signins: