Dense Rank

Version 1



    This is a ranking function where no rank numbers are skipped after ties.





    Example Calculation:



    IF ISNULL(SUM([Sales])) THEN



      IIF(SUM([Sales]) == LOOKUP(SUM([Sales]), -1), PREVIOUS_VALUE(1), PREVIOUS_VALUE(1) + 1, 1)





    Inputs and Setup:


    An aggregate measure such as "SUM([Measure])", or a calculation producing one.



    Partitioning and Addressing:


    Table (Down/Across)





    After a tie, the next row gets the following rank - it is "bumped up" from the rank it would have had if no tie had occurred.  Values of 5, 3, 3, 2 would produce ranks 1, 2, 2, 3.



    Related Functions:


    Standard Rank, Modified Competition Rank



    Further Reading/Examples:


    Adopted from James Baker: