6 Replies Latest reply on Dec 5, 2018 9:47 AM by Maciej Bedlin

    same rank for multiple records with same value

    Maciej Bedlin

      Hi All

      I nned to create a rank based on measure - distinct count of records in text.

      I would like to move the index (rank) to the end and duplicate ranks for the same value

      so let say rank 18 and 19 with value of 54 should have rank 18 and then skip 19 and 20 for rank 20

      so should be

      17

      18

      18

      20

       

      also how do I start with 1 not 0 with my rank please?

        • 1. Re: same rank for multiple records with same value
          Don Wise

          Hello Maciej,

          You'll want to look at the following for RANK, not INDEX in order to treat the numbers that are in competition with each other.  The RANK() function allows you to do what you're trying to do.  Here are examples from the Help site:

           

          RANK(expression, ['asc' | 'desc'])


          Returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is descending.

          With this function, the set of values (6, 9, 9, 14) would be ranked (4, 2, 2, 1).

          Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.

          For information on different ranking options, see Rank calculation.

          Example

          The following image shows the effect of the various ranking functions (RANK, RANK_DENSE, RANK_MODIFIED, RANK_PERCENTILE, and RANK_UNIQUE) on a set of values. The data set contains information on 14 students (StudentA through StudentN); the Age column shows the current age of each student (all students are between 17 and 20 years of age). The remaining columns show the effect of each rank function on the set of age values, always assuming the default order (ascending or descending) for the function.

          RANK_DENSE(expression, ['asc' | 'desc'])


          Returns the dense rank for the current row in the partition. Identical values are assigned an identical rank, but no gaps are inserted into the number sequence. Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is descending.

          With this function, the set of values (6, 9, 9, 14) would be ranked (3, 2, 2, 1).

          Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.

          For information on different ranking options, see Rank calculation.

          RANK_MODIFIED(expression, ['asc' | 'desc'])


          Returns the modified competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is descending.

          With this function, the set of values (6, 9, 9, 14) would be ranked (4, 3, 3, 1).

          Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.

          For information on different ranking options, see Rank calculation.

          RANK_PERCENTILE(expression, ['asc' | 'desc'])


          Returns the percentile rank for the current row in the partition. Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is ascending.

          With this function, the set of values (6, 9, 9, 14) would be ranked (0.25, 0.75, 0.75, 1.00).

          Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.

          For information on different ranking options, see Rank calculation.

          RANK_UNIQUE(expression, ['asc' | 'desc'])


          Returns the unique rank for the current row in the partition. Identical values are assigned different ranks. Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is descending.

          With this function, the set of values (6, 9, 9, 14) would be ranked (4, 2, 3, 1).

          Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.

          For information on different ranking options, see Rank calculation.

          1 of 1 people found this helpful
          • 2. Re: same rank for multiple records with same value
            Maciej Bedlin

            Don Wise

            thanks a million...

            now Based on this rank  is there a way to create somehow link on other sheet where I would be creating detailed graph to specific "site" ranked 1,2,3,4,5

            so I was thinking 5 sheets each with dynamic filter for rank one to 5 so if rank positions change the graphs would change too (graph for top 1 would show always the results for the 1st place?)

            • 3. Re: same rank for multiple records with same value
              Don Wise

              Hello Maciej,

              You could create a parameter and then a subsequent calculation to filter off the parameter. Put the parameter filter in the Filters. Then apply the parameter to all sheets/charts/views so that if one is changed, they all subsequently change as they're using the same parameter.  Thx, Don

              • 4. Re: same rank for multiple records with same value
                Maciej Bedlin

                could i somehow modify top N filter based on the same value rank is calculated?

                to retrieve only 2nd value

                 

                sorry I'm not up to speed with parameters yet still baby steps here and many thanks for your help so far.

                • 5. Re: same rank for multiple records with same value
                  Don Wise

                  Hello Maciej,

                  Difficult to say without data or a provided workbook.  It would be best, because this is a new question, to post a new question on the forums to help you further along with mocked up data or actual workbook.  Thx!  Don