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

    Total average for the top 5 values...?

    Jose Silva

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



      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...?
          Łukasz Majewski



          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...





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

            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!


            • 3. Re: Total average for the top 5 values...?
              Łukasz Majewski



              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




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

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