2 Replies Latest reply on Sep 12, 2019 5:05 PM by Bryce Larsen

    Sorting/Ranking by two measures - case of a tie

    Uzair Rafique

      Hi all,

       

      I want to get a ranking of top 10 suppliers but need to create the ranking based on the score (desc) and total number of deliveries (desc).

       

      Meaning that in case the scores are the same, the number of deliveries are checked to decide the rank (breaking the tie).

       

      Any advice?

       

      Best,

       

      Uzair

        • 1. Re: Sorting/Ranking by two measures - case of a tie
          kevin flerlage

          Uzair,

           

          I created a sample data source and brought that into a workbook (both are attached).  The data looks like the following:

           

           

          DimensionValue 1Value 2
          A458734
          B740509
          C424506
          D314294
          E42438
          F630833

          etc.

           

          I created two rank calculations, one to rank Value 1 and another to rank Value 2:

           

          @Rank of Value 1

           

              RANK(avg([Value 1]), 'desc')

           

           

          @Rank of Value 2

             RANK(avg([Value 2]), 'desc')

           

          I then created a third calculation called Combined Rank.  The combined rank takes the string of the first rank and combines it with the string of the second rank (and I separated them with a dash). 

           

          @Combined Rank

               STR([@Rank of Value 1]) + ' - ' + str([@Rank of Value 2])

           

          This yields values like 1 - 11, 2 - 3, 3 - 19.  The first number is the rank of value 1 and the second is the rank of value 2. 

           

          Next, place them all on your view.  I started with Dimension on Row, Measure Names on Columns, and Measure Values on Text to build a text table like what I've shown below:

           

          1.PNG

           

          Now take your Combined Rank field and place it on Rows in front of Dimension.  Then click on the down arrow of the pill then set it to Compute Using "Dimension".  This will sort it by that Combined Rank field. 

           

          2.PNG

           

          You'll see that there is a tie at 19 for C & E.  The tie is broken by the numbers in Value 2 which is reflected in the Combined Rank.  19-15 is sorted before 19-26. 

           

          I hope this helps...and if it does, please mark it as answered.  Thanks!

           

          Kevin Flerlage

          1 of 1 people found this helpful
          • 2. Re: Sorting/Ranking by two measures - case of a tie
            Bryce Larsen

            Someone else had a very similar question earlier in the week.

             

            Began using this function:

            RANK(-RANK(SUM([Value 1]))-1/SUM([Value 2]))

             

            Have a look at this thread to review: if names have same value of VSAT then use Survey received as deciding factor

             

            Best of luck!

             

            Bryce

            1 of 1 people found this helpful