https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/PERCENTILERANK/m-p/1082573#M514 <P><A title="" href="https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991" target="_self">In my recent quest to create or catalog as many DAX equivalents for Excel functions</A><SPAN>&nbsp;was able to figure out Excel's PERCENTILERANK.INC and PERCENTILERANK.EXC, the latter being documented incredibly poorly!</SPAN></P> <P>&nbsp;</P> <P><STRONG>PERCENTILERANK.INC</STRONG></P> <LI-CODE lang="markup">PERCENTILERANK.INC = VAR __Value = MAX('RanksInc'[Rank]) RETURN IF( __Value IN SELECTCOLUMNS('ValuesInc',"Values",[Value]), VAR __NumLower = COUNTROWS(FILTER('ValuesInc',[Value] &lt; __Value)) VAR __NumHigher = COUNTROWS(FILTER('ValuesInc',[Value] &gt; __Value)) VAR __Rank = __NumLower / (__NumLower + __NumHigher) RETURN IF(ISBLANK(__Rank),0,__Rank), VAR __Lower = MAXX(FILTER('ValuesInc',[Value] &lt; __Value),[Value]) VAR __Higher = MINX(FILTER('ValuesInc',[Value] &gt; __Value),[Value]) VAR __LowerNumLower = COUNTROWS(FILTER('ValuesInc',[Value] &lt; __Lower)) VAR __LowerNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] &gt; __Lower)) VAR __LowerRank = __LowerNumLower / (__LowerNumLower + __LowerNumHigher) VAR __HigherNumLower = COUNTROWS(FILTER('ValuesInc',[Value] &lt; __Higher)) VAR __HigherNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] &gt; __Higher)) VAR __HigherRank = __HigherNumLower / (__HigherNumLower + __HigherNumHigher) RETURN __LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank ) )</LI-CODE> <P><STRONG>PERCENTILERANK.EXC</STRONG></P> <LI-CODE lang="markup">PERCENTILERANK.EXC = VAR __Value = MAX('RanksExc'[Rank]) RETURN IF( __Value IN SELECTCOLUMNS('ValuesExc',"Values",[Value]), VAR __NumLower = COUNTROWS(FILTER('ValuesExc',[Value] &lt; __Value)) + 1 VAR __Count = COUNTROWS('ValuesExc') + 1 RETURN __NumLower / __Count, VAR __Lower = MAXX(FILTER('ValuesExc',[Value] &lt; __Value),[Value]) VAR __Count = COUNTROWS('ValuesExc') + 1 VAR __Higher = MINX(FILTER('ValuesExc',[Value] &gt; __Value),[Value]) VAR __LowerNumLower = COUNTROWS(FILTER('ValuesExc',[Value] &lt; __Lower)) + 1 VAR __LowerRank = __LowerNumLower / __Count VAR __HigherNumLower = COUNTROWS(FILTER('ValuesExc',[Value] &lt; __Higher)) + 1 VAR __HigherRank = __HigherNumLower / __Count RETURN __LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank ) )</LI-CODE> <P><SPAN class="reportid hidden">eyJrIjoiNDEwZjM1MWItNDIwZS00MGRlLWFjNWMtMDRmYWUyNzdkNzJkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9</SPAN></P> Sun, 10 May 2020 00:55:07 GMT Greg_Deckler 2020-05-10T00:55:07Z https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/PERCENTILERANK/m-p/1082573#M514 <P><A title="" href="https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991" target="_self">In my recent quest to create or catalog as many DAX equivalents for Excel functions</A><SPAN>&nbsp;was able to figure out Excel's PERCENTILERANK.INC and PERCENTILERANK.EXC, the latter being documented incredibly poorly!</SPAN></P> <P>&nbsp;</P> <P><STRONG>PERCENTILERANK.INC</STRONG></P> <LI-CODE lang="markup">PERCENTILERANK.INC = VAR __Value = MAX('RanksInc'[Rank]) RETURN IF( __Value IN SELECTCOLUMNS('ValuesInc',"Values",[Value]), VAR __NumLower = COUNTROWS(FILTER('ValuesInc',[Value] &lt; __Value)) VAR __NumHigher = COUNTROWS(FILTER('ValuesInc',[Value] &gt; __Value)) VAR __Rank = __NumLower / (__NumLower + __NumHigher) RETURN IF(ISBLANK(__Rank),0,__Rank), VAR __Lower = MAXX(FILTER('ValuesInc',[Value] &lt; __Value),[Value]) VAR __Higher = MINX(FILTER('ValuesInc',[Value] &gt; __Value),[Value]) VAR __LowerNumLower = COUNTROWS(FILTER('ValuesInc',[Value] &lt; __Lower)) VAR __LowerNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] &gt; __Lower)) VAR __LowerRank = __LowerNumLower / (__LowerNumLower + __LowerNumHigher) VAR __HigherNumLower = COUNTROWS(FILTER('ValuesInc',[Value] &lt; __Higher)) VAR __HigherNumHigher = COUNTROWS(FILTER('ValuesInc',[Value] &gt; __Higher)) VAR __HigherRank = __HigherNumLower / (__HigherNumLower + __HigherNumHigher) RETURN __LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank ) )</LI-CODE> <P><STRONG>PERCENTILERANK.EXC</STRONG></P> <LI-CODE lang="markup">PERCENTILERANK.EXC = VAR __Value = MAX('RanksExc'[Rank]) RETURN IF( __Value IN SELECTCOLUMNS('ValuesExc',"Values",[Value]), VAR __NumLower = COUNTROWS(FILTER('ValuesExc',[Value] &lt; __Value)) + 1 VAR __Count = COUNTROWS('ValuesExc') + 1 RETURN __NumLower / __Count, VAR __Lower = MAXX(FILTER('ValuesExc',[Value] &lt; __Value),[Value]) VAR __Count = COUNTROWS('ValuesExc') + 1 VAR __Higher = MINX(FILTER('ValuesExc',[Value] &gt; __Value),[Value]) VAR __LowerNumLower = COUNTROWS(FILTER('ValuesExc',[Value] &lt; __Lower)) + 1 VAR __LowerRank = __LowerNumLower / __Count VAR __HigherNumLower = COUNTROWS(FILTER('ValuesExc',[Value] &lt; __Higher)) + 1 VAR __HigherRank = __HigherNumLower / __Count RETURN __LowerRank + ( __Value - __Lower ) / (__Higher - __Lower ) * ( __HigherRank - __LowerRank ) )</LI-CODE> <P><SPAN class="reportid hidden">eyJrIjoiNDEwZjM1MWItNDIwZS00MGRlLWFjNWMtMDRmYWUyNzdkNzJkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9</SPAN></P> Sun, 10 May 2020 00:55:07 GMT https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/PERCENTILERANK/m-p/1082573#M514 Greg_Deckler 2020-05-10T00:55:07Z