topic PERCENTRANK By Group or Filter? in Desktop
https://community.powerbi.com/t5/Desktop/PERCENTRANK-By-Group-or-Filter/m-p/514102#M240278
<P>So I am trying to build a PERCENTRANK column that is similar to Excel - <SPAN>for a given value (that may not appear in the array) it returns the rank expressed as a percentage.</SPAN></P><P> </P><P>I came across this post which is awesome and does what I'm looking for:</P><P> </P><P><A href="https://community.powerbi.com/t5/Desktop/PERCENTRANK-Inclusive/td-p/81208" target="_blank">https://community.powerbi.com/t5/Desktop/PERCENTRANK-Inclusive/td-p/81208</A></P><P> </P><PRE>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</PRE><PRE>PercentRank Argument =
IF (
HASONEVALUE( 'Test Number'[Test Number] ),
VALUES ( 'Test Number'[Test Number] )
)</PRE><P>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:</P><P> </P><PRE>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])</PRE>Wed, 12 Sep 2018 16:30:46 GMTAnonymous2018-09-12T16:30:46ZPERCENTRANK By Group or Filter?
https://community.powerbi.com/t5/Desktop/PERCENTRANK-By-Group-or-Filter/m-p/514102#M240278
<P>So I am trying to build a PERCENTRANK column that is similar to Excel - <SPAN>for a given value (that may not appear in the array) it returns the rank expressed as a percentage.</SPAN></P><P> </P><P>I came across this post which is awesome and does what I'm looking for:</P><P> </P><P><A href="https://community.powerbi.com/t5/Desktop/PERCENTRANK-Inclusive/td-p/81208" target="_blank">https://community.powerbi.com/t5/Desktop/PERCENTRANK-Inclusive/td-p/81208</A></P><P> </P><PRE>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</PRE><PRE>PercentRank Argument =
IF (
HASONEVALUE( 'Test Number'[Test Number] ),
VALUES ( 'Test Number'[Test Number] )
)</PRE><P>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:</P><P> </P><PRE>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])</PRE>Wed, 12 Sep 2018 16:30:46 GMThttps://community.powerbi.com/t5/Desktop/PERCENTRANK-By-Group-or-Filter/m-p/514102#M240278Anonymous2018-09-12T16:30:46ZRe: PERCENTRANK By Group or Filter?
https://community.powerbi.com/t5/Desktop/PERCENTRANK-By-Group-or-Filter/m-p/514836#M240603
<P>Hi <LI-USER uid="39540"></LI-USER>,</P>
<P>Could you please post me some sample file to have a test and post your desired result if possible?</P>
<P> </P>
<P>Regards,</P>
<P>Daniel He</P>Thu, 13 Sep 2018 09:40:58 GMThttps://community.powerbi.com/t5/Desktop/PERCENTRANK-By-Group-or-Filter/m-p/514836#M240603v-danhe-msft2018-09-13T09:40:58Z