Hi, I have a table like the one below. I am calculating average (mean) cost per unit for dimension 1
Dimension 1 Can be Dealer, Customer, or Model
I need to calculate a benchmark of the average of all rows in the set of data, and display it next to the individual dim cost as a comparison.
I have tried using
Aggr(sum([Claim Total])/sum(CountUnit),Dealer)
where CountUnit is 1 for each unit (instead of using Count(Unit)
However, the result is not at all what I am expecting; as you can see the benchmark is supposed to be 213.33
| Dealer | Units | Claims | Cost | Labour | Parts | Misc. | Per Unit | Benchmark |
| Dealer 1 | 1 | 1 | £105.95 | £105.95 | £0.00 | £0.00 | £105.95 | - |
| Dealer 2 | 1 | 4 | £124.72 | £124.72 | £0.00 | £0.00 | £124.72 | £124.72 |
| Dealer 3 | 2 | 2 | £232.38 | £227.62 | £4.76 | £0.00 | £116.19 | £116.19 |
| Dealer 4 | 3 | 6 | £549.48 | £535.20 | £14.28 | £0.00 | £183.16 | £183.16 |
| Dealer 5 | 2 | 4 | £672.44 | £653.40 | £19.04 | £0.00 | £336.22 | £336.22 |
| Dealer 6 | 3 | 4 | £38.60 | £18.90 | £19.70 | £0.00 | £12.87 | £12.87 |
| Dealer 7 | 3 | 10 | £770.12 | £735.42 | £34.70 | £0.00 | £256.71 | £256.71 |
| Dealer 8 | 1 | 1 | £88.56 | £16.67 | £71.89 | £0.00 | £88.56 | - |
| Dealer 9 | 5 | 9 | £993.97 | £917.78 | £76.19 | £0.00 | £198.79 | - |
| Dealer 10 | 4 | 20 | £1,756.94 | £1,680.42 | £76.52 | £0.00 | £439.24 | - |
| TOTAL | 25 | | £5,333.16 | | | | | £213.33 |
Many thanks for your help
M.
