PERCENTILERANK 05-09-2020 17:55 PM

[Greg_Deckler]

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