AGGR problem

[martynlloyd]

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.

[anbu1984 · Accepted solution]

sum(TOTAL [Claim Total])/sum(TOTAL CountUnit)

[Anonymous]

should be sum(total [Claim Total])/sum(total CountUnit)

[Anonymous]

Include the time frame field name in your aggregate Function.

Aggr(Sum({1}[Claim Total]/CountUnit),Dealer,Date)

[maxgro]

sum(total Cost)  / sum(total Units)

replace Cost and Units with your field