2 Replies Latest reply on Mar 19, 2018 9:02 AM by Bob Lindas

# Average of Average per time Aggregate Specified

Hello all,

I'm hoping this would be achievable within Tableau and that someone could help me to get started.

I have a dataset that has multiple users, in multiple departments, who generate multiple samples throughout the course of a year.

What I would like to achieve is somewhat complex, but I'll start out with a basic example. On a time graph, for a given time aggregate that a tableau user may choose (day, week,month, etc), what I would like to do is take all samples of a unique user for the time aggregate specified, and average all their samples into 1 sample. This would be done for each unique user (lets call this unique user average), and then in the end, all these unique user averages would be averaged into 1 final sample for that given time aggregate, per department.

To put this into an example

 Department A Department B DeptA U1 DeptA U2 DeptB U1 DeptB U2 01-Jan 5 3 5 02-Jan 5 03-Jan 3 3 3 2 04-Jan 3 2 05-Jan 3 1 06-Jan 3 3 1 3 07-Jan 1 2 1 08-Jan 2 2 2 09-Jan 1 5 1 10-Jan 3 2 11-Jan 3 3 3 4 12-Jan 3 1 1 13-Jan 5 3 14-Jan 1 4 5

If the tableau user chooses weekly time aggregate, what I'm trying to achieve is the following

Dept A week 1 average = take samples from Jan 1 to 7, and avg(avg(DeptA U1)+ avg(DeptA U2)) = 3.1

Dept B week 1 average = take samples from Jan 1 to 7, and avg(avg(DeptB U1)+ avg(DeptB U2)) = 2.3

Dept A week 2 average = take samples from Jan 8 to 14, and avg(avg(DeptA U1)+ avg(DeptA U2)) = 2.8

Dept B week 2 average = take samples from Jan 8 to 14, and avg(avg(DeptB U1)+ avg(DeptB U2)) = 2.7

And then graphed over time

Any help on this would be greatly appreciated.

• ###### 1. Re: Average of Average per time Aggregate Specified

Hi, Bob

Hope this helps

ZZ

• ###### 2. Re: Average of Average per time Aggregate Specified

Hi ZZ,

Thank you so much for this! In my example i had just listed 2 users per department, but in actuality, there's several hundred users. The solution you have is making a formula for each individual user. Is there a way to formulate this so that it can cascade to multiple users from multiple departments?