5 Replies Latest reply on Oct 13, 2014 4:32 PM by Patrick Van Der Hyde

    average of average calculation

    Syed Arif Ullah Shah



      I have an employee name as dimension, Team of employee as dimension and meeting time(in minutes) as the metric. My requirement is to create a plot in the following way


      Y axis has the meeting minutes average x axis has the employee name. Now I need to draw a reference line that has average for the team. However, Team average is the average of average of employee meeting time. Then there is another reference line that is the average of teams average. I think it sounds a bit complex to explain so here is an attached excelsheet. Please have a look at teams average and total average and tell me how to do and draw this calculation in tableau



        • 1. Re: average of average calculation
          Matt Lutton

          Let me know if this is close -- you'd receive a far better response if you took your sample data (the Employee Name, Meeting Date, Team, and Meeting time in minutes from the Excel data) and posted it as a sample packaged workbook in Tableau.  For this example, I simply copied and pasted those Columns from your Excel file directly into Tableau as a new data source, then created an Extract from that data in order to post a TWBX here on the Forums.  Its often that simple!


          In the attached, I've created a Bar chart that gives you the Avg Meeting Time in Minutes per Employee, and I've further partioned the data by Team, and provided the Avg for the Team via a Reference Line.


          Let me know if this meets your needs, or provide a mockup if you're looking for something different.



          • 2. Re: average of average calculation
            Matt Lutton

            Of course, you could edit this as needed to get the Individual Averages shown in your Excel file:


            AVGS IN EXCEL.png


            The only figure I am confused on is your "Total Average" in the Excel file.

            • 3. Re: average of average calculation
              Syed Arif Ullah Shah



              Many thanks for all the help that you have been providing. Your solutions is correct. However, I need some enhancements to it


              1) There is a third average above the team average. This is the average of the team average that we got above. This should be a straight line across all teams. If you have a look at the excel, there is a single average that is calculated over the teams average. I need to calculate that one and draw it as a straight line across all teams/panes. Can you please tell me how to do it


              2) If I had to do it through a calculated field, how would I calculate the team average above the average of individuals and also the organization average above team average?




              • 4. Re: average of average calculation
                Matt Lutton

                No problem, I'll probably look at this again tomorrow if nobody else has provided you with a solution by then. It is definitely possible to achieve all the results in your excel workbook, but I am not clear on how you'd like to display the data in Tableau--do you want the data segmented by each person or by the team amd the people on each team (My first screenshot had the team included in the partitioning while the second screenshot only used the team for coloring purposes.


                A mock up of the final chart you'd like to generate might help encourage others to jump in and work on this. Best of luck!

                • 5. Re: average of average calculation
                  Patrick Van Der Hyde

                  Hello Syed Arif Ullah Shah,


                  The Avg overall can quickly be calculated and shown as a reference line just as the per Pane example was but this time using a Scope of "Entire Table".  I have added this here with red dashes and the value formatted in big red numbers to set it apart.


                  Sheet 1 shows how to calculate these values in a crosstab where we need to utilize Table Calculations.  Here the use of Window_avg(avg([Meeting Time in Minutes])) can be used as two calculations.  One set as Table Down to cover the entire spectrum of avgs and the other one set to "Compute Using" (or Addressing) with the fields [Meeting Date] and [Employee Name] since these are the combined fields that define the level of aggregation.  The field [Team] is the Partitioning field (or the avg per Team is maybe easier to think of it). 


                  I hope this helps.