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?

       

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

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

            José

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

              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.

               

              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

              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.

                 

                Cheers!

                José