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