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...
Top 5 Students.twbx 45.5 KB
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.
1 of 1 people found this helpful
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
Impressive! I thought it would have been simpler. Many thanks again Lukasz. I will take a time to better understand the process.