The following table is to be aggregated based on the Dim 1 and Dim 2 columns. The sum is to be calculated for the Values 1 column and the average for the Values 2 column. The Weight (values 2) column is used for weighting the Values 2 column one row at a time.
Dim 1 |
Dim 2 |
Values 1 |
Values 2 |
Weight (values 2) |
A |
X |
1 |
2 |
3 |
B |
Y |
2 |
4 |
4 |
C |
Z |
5 |
6 |
3 |
A |
X |
7 |
8 |
4 |
B |
Y |
9 |
10 |
3 |
C |
Z |
11 |
12 |
4 |
Result
Dim 1 |
Dim 2 |
Sum (values 1) |
Average (values 2) |
A |
X |
8 (1+7) |
5,43 (2*3 + 8*4)/(3+4) |
B |
Y |
12 (3+9) |
6,57 (4*4 + 10*3)/(4+3) |
C |
Z |
16 (5+11) |
9,43 (6*3 + 12*4)/(3+4) |