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

# Average rank

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.

• ###### 1. Re: Average rank

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.

• ###### 2. Re: Average rank

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

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

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

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!