Forum Discussion
PERCENTRANK (Inclusive)
Excel has a PERCENTRANK function (which is different from PERCENTILE functions). I would like to find the same thing for DAX. Can anyone help me figure this out? Thank you.
Matt - I believe the PERCENTILE functions in Excel and Power BI are similar in that they return the value sitting at a given percentile.
e.g. Excel's PERCENTILE.INC( <array>, k ) is equivalent to Power BI's PERCENTILE.INC( <column>, k ).
If k = 0.5 then they would return the 50th percentile.
PERCENTRANK in Excel is the inverse of PERCENTILE in that, for a given value (that may not appear in the array) it returns the rank expressed as a percentage.
I had a go at replicating PERCENTRANK.INC in DAX.
The DAX code looks like this (excessive use of variables :smileyhappy: )
Could be some bugs but works for sample data.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
8 Replies
I think you just want to calculate the PERCENTERANK for current row value. Based on the logic for PERCENTRANK, we can add a RANK column in table and achieve same logic based on this column. Please see my sample below:
Create a rank column.
Rank = RANKX(Table6,Table6[Value])
Then create the PctRank based on the rank column.
PctRank = (COUNTA(Table6[Name])-Table6[Rank])/(COUNTA(Table6[Name])-1)
Regards,
- I actually answered my own question with this formula :) Switch = SWITCH(TRUE(), AND(Table6[PctRank]>0,Table6[PctRank]<=0.20),"0-20%", AND(Table6[PctRank]>0.20,Table6[PctRank]<=0.40),"20-40%", AND(Table6[PctRank]>0.40,Table6[PctRank]<=0.60),"40-60%", AND(Table6[PctRank]>0.60,Table6[PctRank]<=0.80),"60-80%", (Table6[PctRank]>0.80),"80-100%")
v-sihou-msft Too bad PowerBI does not have the equivalent of the PercentRank function of Excel! I like your simple approach of using Rank and a computation of the Percentile. However, are you sure about your code for RankX?? It is not working when I try to duplicate your table. I get an error with your formula. Then when I try to modify it with a calculate and sum function, I only get '1' for each row!
Rank = RANKX('Table',CALCULATE(SUM('Table'[Value])))
As I understand, percentrank in Excel takes a score and returns its percentile. This seems to be exactly what percentile in DAX does. Percentile in Excel does something different however.
Matt - I believe the PERCENTILE functions in Excel and Power BI are similar in that they return the value sitting at a given percentile.
e.g. Excel's PERCENTILE.INC( <array>, k ) is equivalent to Power BI's PERCENTILE.INC( <column>, k ).
If k = 0.5 then they would return the 50th percentile.
PERCENTRANK in Excel is the inverse of PERCENTILE in that, for a given value (that may not appear in the array) it returns the rank expressed as a percentage.
I had a go at replicating PERCENTRANK.INC in DAX.
The DAX code looks like this (excessive use of variables :smileyhappy: )
Could be some bugs but works for sample data.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 PercentRankOutputI recreated and corrected the formula and it appears to work well like this below:
PercentRank.INC.Value =
VAR PercentRankArgument = [Value]
RETURN
IF (
PercentRankArgument >= MIN('Sheet1'[Value]) && PercentRankArgument <= MAX('Sheet1'[Value]),
VAR NumberLessThanArgument = FILTER('Sheet1', 'Sheet1'[Value] < PercentRankArgument)
VAR NumberGreaterThanOrEqualArgument = FILTER('Sheet1', 'Sheet1'[Value] >= PercentRankArgument)
VAR RankLower = COUNTROWS(NumberLessThanArgument)
VAR NumberLower = MAXX(NumberLessThanArgument, 'Sheet1'[Value])
VAR NumberUpper = MINX(NumberGreaterThanOrEqualArgument, 'Sheet1'[Value])
VAR PercentRankArgumentRank = RankLower + 1
VAR InterpolationFraction = DIVIDE(PercentRankArgument - NumberLower, NumberUpper - NumberLower)
VAR RankInterpolated = RankLower + InterpolationFraction * (PercentRankArgumentRank - RankLower)
VAR NumberCount = COUNTROWS('Sheet1')
VAR PercentRankOutput = DIVIDE(RankInterpolated - 1, NumberCount - 1)
RETURN if(PercentRankOutput<0,0,PercentRankOutput),
BLANK()
)
