2 Replies Latest reply on Aug 30, 2013 9:12 AM by Rossella Blatt Vital

# Averages of Sum of Averages

Hi,

I am trying to compute the sum of the averages of a dataset with a structure similar to the one in attachment and below:

 DATE CLIENT AVG POSITIONS BY WEEK EXCHANGE instrument TOTAL LOTS 8/23/2013 A 50 e1 i1 10 8/23/2013 A 67 e2 i2 3 8/23/2013 A 3 e3 i2 5 8/16/2013 A 5 e2 i2 25 8/23/2013 B 2.5 e1 i1 25 8/16/2013 B 6 e2 i1 52 8/16/2013 B 90 e3 i1 566 8/23/2013 C 100 e4 i1 43 8/23/2013 C 43 e5 i1 734 8/16/2013 C 352 e6 i1 62 8/16/2013 C 235 e6 i2 4 8/16/2013 C 523 e7 i3 47 8/23/2013 D 5 e1 i1 47 8/23/2013 D 352 e1 i2 7 8/23/2013 D 6 e2 i3 348 8/23/2013 D 64 e3 i1 48 8/23/2013 D 6 e4 i4 6 8/23/2013 D 46 e5 i2 23 8/23/2013 D 43 e5 i5 235

I would like to compute for each client the average of the sums of  AVG POSITIONS BY WEEK, i.e.:

SUM OF AVG POS CLIENT A = AVG(50 + 67 + 3; 5) = AVG(120, 5) = 62.5

SUM OF AVG POS CLIENT B = AVG(2.5; 6 + 90) = 49.25

SUM OF AVG POS CLIENT C = AVG(100 + 43; 352 + 235 + 523) = 626.5

SUM OF AVG POS CLIENT D = AVG(5 + 352 + 6 + 64 + 6 + 46 + 43) = 522

Note that I would like to display this numbers as y coordinates and labels for a bubble chart where:

- each bubble is a client (in the example above we would have 4 bubbles) and each bubble is colored by CLIENT

- x (column) value = SUM OF LOTS for each client (A_x = 43; B_x = 643; C_x = 890; D_x = 714)

- y (row) value = SUM OF AVG POS for each client (A_y = 62.5; B_y = 49.25; C_y = 62.5; D_y = 522).

I guess I need to use a Table Calculation but I can't figure out the correct way to do it. I obtained the correct values when I was dropping the date field into level of details, but then the bubble chart was displayed wrongly (I want one bubble for each client for the whole period of time).

Also, please note that I will need to add some of the other dimensions (like exchange and instrument) as well as the date dimension on the filter shelf because I want the user to be able to filter out by those fields (and the SUM OF AVG POS should then be recalculated according to the selection of the user).

One last note: in my real dataset I actually have many more dimensions and measures that will not be used in this specific view, but when I was trying to define the partitioning and addressing of SUM OF AVG POS, ALL the fields were appearing in the partitioning window of the advanced table calculation set-up and I couldn't figure out how to delete those fields that have nothing to do with this specific view.

Many thanks in advance for any suggestion.

Best regards

Rossella

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

Hi Rossella,

Can you re-post the workbook making sure to save it as a twbx file?

-Tracy

• ###### 2. Re: Averages of Sum of Averages

My apologies... Just attached the correct Tableau package.

Thanks in advance for any suggestion.

Rossella