4 Replies Latest reply on Oct 24, 2016 4:16 AM by Jose Silva

# Total average for the top 5 values...?

How can I display Totals (average value) from a list that is filtered using ranking?

Example:

I have a list of students with its corresponded score (in %) and the total hours of study for each of them. The data is analyzed for 2015 and 2016.

I would like to see the total average in score and hours of study for the top 5 students for each year. How can I do that? I used the total function but it seems that tableau is calculating the total average for the entire student list and I’d like to see the average only for the top 5 with the highest score.

Thank you very much!!

• ###### 1. Re: Total average for the top 5 values...?

Hi,

If you are not going to go with less than Top 2 Students then Totals may be customized.

I have to assert though that I have not verified 'custom sort' in nested table calculations thoroughly - anyway it looks good with this sample.

This is rather complicated...

Cheers,

Łukasz

• ###### 2. Re: Total average for the top 5 values...?

Hi Lukasz!

It looks pretty amazing. That’s what I am looking for. To be honest, I don’t understand what you did, it looks quite complicated. May I ask you how did you do that?

I will try to understand what you did, but if you would like to give me some ideas, I would appreciate that.

Many Thanks!

José

• ###### 3. Re: Total average for the top 5 values...?

Sure.

The best explanation of the idea that I am aware of is here: Customising Grand Totals - YouTube

It deals with 1 level/dimension of aggregation. This idea only works if there are 2 or more members of the dimension being totaled (is this the right word?).

You wanted subtotals per Group and Año so i added a copy of [Students] and [Group] dimensions to details shelf.

The equivalent of what is presented in that youtube video is implemented in our first step aggregation:

meaning: take normal AVG(Score) for individual [Students] cells but the windowed formula if we are in Total row. The trick is that we switched on [Group] & [Año] subtotals and not their copies in details and the proper addressing makes first()=last() true only in those individual cells.

In general [Group (copy)] should also be included in addressing but it was redundant in your case since your groups do not share students - Año/Students groups are the same as Año/Group/Students - so I left it unchecked so that it looked simpler... But there is another twist - since you wanted top 5 by sum(Score) all our table calculations must be sorted accordingly, i.e. by SUM(Score) descending and Año - and [Group copy] really - dimension is required for sort to take place in each Año/Group partition. Otherwise it would sort students across all data set. But we really only want students to be ranked hence I used 'At the level' of [Students (copy)].

The next level aggregation is to total Año - where both Students and Groups disappear. Here the above - [AVG Score (Group)] - is used as one of two possible calculations depending on first()=last() condition. But since you calculate average I had to retain numerator and denominator of the nested calculation in order to have the right result and not an average of averages:

The numerator and denominator should be addressed as the nested [AVG Score (Group)] which I already discussed. The overall outer calculation is different:

Although I realized now that at this level sort order does not matter anymore as it was taken care of in nested calcs so the latter screenshot setup works just fine.

I told you it was complicated... but hope i have shed some light on the powerful yet complex table calculations

Łukasz

1 of 1 people found this helpful
• ###### 4. Re: Total average for the top 5 values...?

Impressive! I thought it would have been simpler. Many thanks again Lukasz. I will take a time to better understand the process.

Cheers!

José