Averages of Sum of Averages
Rossella Blatt Vital Aug 30, 2013 9:11 AMHi,
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 setup 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

AveragesOfSumOfAverages.twbx.zip 19.9 KB