5 Replies Latest reply on Jun 19, 2018 7:25 AM by Junhao Li

    Average rank

    Junhao Li

      The goal is calculating the average rank across row and down the column.

       

      For example, I want a column on the right showing the average rank of (the rank of Profit) and (the rank of Sales). Thus, the first row should have a third cell showing the average rank is 5.5 for Auburn.

       

      I also want to show the average rank of (rank of Profit). It will be an additional row at the bottom shows that.

       

      Thanks in advance!

        • 1. Re: Average rank
          Ken Flerlage

          This seems like a strange thing to do, but it's definitely possible. Instead of using quick table calculations, create the following calculated fields:

           

          Rank Profit

          RANK_UNIQUE(SUM([Profit]))

           

          Rank Sales

          RANK_UNIQUE(SUM([Sales]))

           

          Rank Avg

          ([Rank Profit]+[Rank Sales])/2

           

          I'm using RANK_UNIQUE because that's the ranking method you were using in your table calculations, but there are other options. For more details, see the following: Table Calculation Functions

           

          See attached workbook.

           

          If this addresses your question, please be sure to mark this answer as correct. Thanks!

          • 2. Re: Average rank
            Junhao Li

            I know how to do average rank across row, just like what you did.

             

            However, what I am really looking for is average rank down the column. Do you have any idea of how to do it?

            • 3. Re: Average rank
              Ken Flerlage

              You'll want to do something line WINDOW_AVG([Rank Sales]). This will create a table calculation and you'll have to set up that table calculation to work the way you need it to.

               

              Can you give an example of how this should work using the data in your workbook?

              • 4. Re: Average rank
                Junhao Li

                Basically, I want to have a row in the very bottom (after  Wyoming) saying the average rank of Sale and average rank of Profit in the workbook I provided.

                • 5. Re: Average rank
                  Junhao Li

                  If you confused about the original workbook, I have posted another one to better demonstrate what I need to do.

                   

                  In this workbook, I want to calculate a average rank since 2000 for each region. It should look like "grand total", however, grand total does not calculate the right number and the "average" in grand total does not work either.

                   

                  I am thinking about just calculate an average rank number in a separated worksheet and combine later in a dashboard.

                   

                  Any help will be appreciated!