10 Replies Latest reply on Nov 29, 2017 2:05 PM by Jim Dehner

# Need advice on creating a total rank that can be used in a sort

Hi,

I have a couple issues I'm trying to resolve and have been unsuccessful in implementing the ideas I've already found on the forum.  I have created a small sample workbook with the data in the same format as the original. In each box, the top number is the value and the bottom number is the rank.

First, I need to create an overall average rank across the metrics for each person. For example, William would have a rank of 11.3 (avg of 5, 12, 17). I run into issue with aggregating an already aggregated number; I've tried using a LOD calculation and ran into issues there.

Second, once I have this overall rank, I need to display it in the same format at the other columns and add it into the existing sort calculation.

I appreciate any help with resolving this request. Thank you!

• ###### 1. Re: Need advice on creating a total rank that can be used in a sort

Hi Melissa

This is really an interesting problem and I am not certain I have a good answer but here goes -

see the attached

Part of the issue here is that Rank is a table calculation and in the Order of Operation Table calculations are the next to last - so all the filtering, LOD Calculations, totals and regular calculations preceed table calcs - I tried nesting table calculations but did not get something that worked out

so I went a different way (and you will probably have to modify it to make sense)

I went back to the base data and did an LOD show below which looks at the combination of person, role, and date and does your numerator /denominator calculation

That gives you a new set of values that are aggregated above the base level (lowest) in your data

Now table calculations can be applied to the results for the LOD

So I had another calculation :

for a Rank on the LOD values

(I used sum in the calc - you may want Avg

Well it returns a combined metric ranking like this for your worksheet

It may give you a start

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Need advice on creating a total rank that can be used in a sort

Hello Melissa Corbin

I played a round with your workbook for a bit and was able to sort based on the average total rank. I simplified some of your calculations since Tableau has built in table calculations that can be used vs manually creating the calculation. Want to say this is right based on what you wanted to do but feel free to double check my work.

What I did was under "Analysis" and selected Show totals -> Show row grand totals then selected show grand totals by average. Then from there you can sort based on who has the highest overall rank. The only downside was that I am unable to hide the value grand total as I myself can not figure out the calculation to use at the moment I whipped this up. If you want to hide this extra grand total I think the information in this post Want to display Grand Total for only 1 column, rather than all columns  may be of some help.

• ###### 3. Re: Need advice on creating a total rank that can be used in a sort

Jim,

Thank you for taking a look at my issue. Unfortunately, since the metrics are on different scales (Drafts are 1-10, Pages are 1-100, and Words are 1-1000), adding them and then calculating the rank over weights the influence of the larger scaled metrics. Essentially, the calculation would need to sum the ranks, however, this way gives an aggregation error.

• ###### 4. Re: Need advice on creating a total rank that can be used in a sort

Hi

once again Rank is a table calculation which is at the bottom of the order of operation -

Is there a way that the individual metrics can be weighted - in your explanation you referenced page count can that be used as a weighting factor?

Jim

• ###### 5. Re: Need advice on creating a total rank that can be used in a sort

Hi John,

I'm only on 10.3 and can't view your file. I took another look at Grand Total in my file and the average appears to get some of the ranks correct, but it still looks a bit off compared to expected results.

• ###### 6. Re: Need advice on creating a total rank that can be used in a sort

Thanks Jim, in the actual report, the metrics are on several different scales and the min/max varies for various time periods, roles, etc. I don't think a relative weighting would work.

I was hoping that there was a workaround to the order of operations for rank, but it sounds like what I'm trying to do may not be possible. I appreciate you and John taking a look at this question.

• ###### 7. Re: Need advice on creating a total rank that can be used in a sort

I haven't given up on this

see the attached

This will do a sum of the individual ranking and divide it by the number of metrics (3)

and returns this (sorted) - you see the value repeats for each column and I have not been able to eliminate the repetition

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 8. Re: Need advice on creating a total rank that can be used in a sort

Hi this one looks a lot better

I changed the calculation to Countd and it returned the sum of the rank you want and then I ranked that total ascending

Then I hid some of the columns and headers to get just the 2 columns - it does respond to the filters also

I think that is about as good as I can do

BTW can't get around the Order of operation

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 9. Re: Need advice on creating a total rank that can be used in a sort

Thanks Jim, these formulas definitely help. I'll try them out in my original file. Thank you!

• ###### 10. Re: Need advice on creating a total rank that can be used in a sort

Thanks Melissa

this one was interesting and I felt there must be a way even it was "Brute Force" - if you have trouble with your real data let me know

Jim