PERCENTILERANK

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