Modified Competition Rank

Version 4



    This is a ranking function with ties. Tied items share the lower rank. 


    Example Calculation:



    IF ISNULL(SUM([Measure])) THEN



      IIF(SUM([Measure]) == LOOKUP(SUM([Measure]), -1),


          WINDOW_COUNT(SUM([Measure])) - RUNNING_COUNT(SUM([Measure])) + 1,






    Inputs and Setup:


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



    Partitioning and Addressing:


    [Dimension or Set being ranked] using an Advanced... sort that is in the opposite direction of the sort that is used for the [Dimension or set being ranked] in the view. For example, a dimension sorted in Descending order in the view would then have this calculation sorted in Ascending order.




    After a tie occurs and two rows share the same rank, the next row's rank is unaffected - it is the same rank that it would have had if there had been now tie above it. Values of 5, 3, 3, 2 would produce ranks 1, 3, 3, 4.



    Related Functions/Formulas:


    Standard Rank:


    Further Reading/Examples:


    Adopted from James Baker:


    EDIT: attached a Version 8 twbx