
1. Re: Total average for the top 5 values...?
Łukasz Majewski Oct 21, 2016 8:25 AM (in response to Jose Silva)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

Top 5 Students.twbx 45.5 KB


2. Re: Total average for the top 5 values...?
Jose Silva Oct 24, 2016 1:42 AM (in response to Łukasz Majewski)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...?
Łukasz Majewski Oct 24, 2016 3:15 AM (in response to Jose Silva)1 of 1 people found this helpfulSure.
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.
Addressing is the complicated part;
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

4. Re: Total average for the top 5 values...?
Jose Silva Oct 24, 2016 4:16 AM (in response to Łukasz Majewski)Impressive! I thought it would have been simpler. Many thanks again Lukasz. I will take a time to better understand the process.
Cheers!
José