3 Replies Latest reply on Nov 18, 2016 12:42 PM by Sean Keleher

# Average of Averages

I've been searching for a while in the Community and haven't found an answer to this question.

Can anyone please explain how to calculate an average of average values in Tableau?

For example with the following data:

UserDayUsage
A110
A250
A320
B160
B3100
C11000
C220
C320
D340

Tableau automatically calculates the average of all usages by totaling everything to 1320 and dividing by 9 entries to get 146.7.

I'm interested to see the average of averages per user, so I got Tableau to create this table for me:

User
Total UsageNumber of DaysPer User Average
A80326.7
B160280
C10403346.7
D40140

Using this data, the Average of Per User Averages would be made by totaling to 493.3 and dividing by 4 users to get 123.3.

So far I haven't found a method to calculate the average of the Per User Average in Tableau. Any help would be appreciated!

• ###### 1. Re: Average of Averages

I believe you're going to need a LOD calculation. Attached is  a picture  and workbook.

let me know if this is the answer you needed, it recreates your numbers. Not sure what the end result you want is.

great video on LOD here: Think Data Thursday: LoD of the Rings - YouTube

1 of 1 people found this helpful
• ###### 2. Re: Average of Averages

Hi Sean,

If you are ok with having the column totals for your number of records and total usage, you can change the aggregation of the "Per User Average" field.

1) Analysis -> Total -> Show Column Totals

2) Click on the total for "Per User Average" (should be the 146.7)

3) Click "Automatic" and change the aggregation to "Average".  This should turn it over to the 123.3 you are looking for.

1 of 1 people found this helpful
• ###### 3. Re: Average of Averages

Matthew's answer was what I was looking for, as I'd like to be able to save the calculation and plot the value on separate charts. I'll research the "fixed keywords" a bit more and watch the video you linked on LoD.

Eric's answer was also helpful for showing the average of averages as a grand total in the table.

Thanks both!