Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In my recent quest to create or catalog as many DAX equivalents for Excel functions was able to figure out Excel's PERCENTILERANK.INC and PERCENTILERANK.EXC, the latter being documented incredibly poorly!
PERCENTILERANK.INC
PERCENTILERANK.INC =
VAR __Value = MAX('RanksInc'[Rank])
RETURN
IF(
__Value IN SELECTCOLUMNS('ValuesInc',"Values",[Value]),
VAR __NumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Value))
VAR __NumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Value))
VAR __Rank = __NumLower / (__NumLower + __NumHigher)
RETURN IF(ISBLANK(__Rank),0,__Rank),
VAR __Lower = MAXX(FILTER('ValuesInc',[Value] < __Value),[Value])
VAR __Higher = MINX(FILTER('ValuesInc',[Value] > __Value),[Value])
VAR __LowerNumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Lower))
VAR __LowerNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Lower))
VAR __LowerRank = __LowerNumLower / (__LowerNumLower + __LowerNumHigher)
VAR __HigherNumLower = COUNTROWS(FILTER('ValuesInc',[Value] < __Higher))
VAR __HigherNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] > __Higher))
VAR __HigherRank = __HigherNumLower / (__HigherNumLower + __HigherNumHigher)
RETURN
__LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank )
)
PERCENTILERANK.EXC
PERCENTILERANK.EXC =
VAR __Value = MAX('RanksExc'[Rank])
RETURN
IF(
__Value IN SELECTCOLUMNS('ValuesExc',"Values",[Value]),
VAR __NumLower = COUNTROWS(FILTER('ValuesExc',[Value] < __Value)) + 1
VAR __Count = COUNTROWS('ValuesExc') + 1
RETURN __NumLower / __Count,
VAR __Lower = MAXX(FILTER('ValuesExc',[Value] < __Value),[Value])
VAR __Count = COUNTROWS('ValuesExc') + 1
VAR __Higher = MINX(FILTER('ValuesExc',[Value] > __Value),[Value])
VAR __LowerNumLower = COUNTROWS(FILTER('ValuesExc',[Value] < __Lower)) + 1
VAR __LowerRank = __LowerNumLower / __Count
VAR __HigherNumLower = COUNTROWS(FILTER('ValuesExc',[Value] < __Higher)) + 1
VAR __HigherRank = __HigherNumLower / __Count
RETURN
__LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank )
)
eyJrIjoiNDEwZjM1MWItNDIwZS00MGRlLWFjNWMtMDRmYWUyNzdkNzJkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9