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|
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.
AveragesOfSumOfAverages.twbx.zip 19.9 KB