cancel
Showing results for
Did you mean:
nobodyukno 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])
v-danhe-msft 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. 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:
• Mynda • YunC • Ian-SCS • tstraker • tazmon95 • SamCarlson • LeanneShapro • Ian-SCS • todd_carl • bosh9 • paulboolos • locka • fcanney • cocoychenbi 