Forum Discussion
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
PercentRankOutputPercentRank 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
- v-danhe-msft
Microsoft Employee
Hi Anonymous,
Could you please post me some sample file to have a test and post your desired result if possible?
Regards,
Daniel He