AGGR problem

martynlloyd — Wed, 24 Sep 2014 17:25:00 GMT

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

Many thanks for your help

Re: AGGR problem

anbu1984 — Wed, 24 Sep 2014 17:29:23 GMT

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

Anonymous — Wed, 24 Sep 2014 17:44:46 GMT

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

Anonymous — Wed, 24 Sep 2014 18:56:28 GMT

Include the time frame field name in your aggregate Function.

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

maxgro — Wed, 24 Sep 2014 19:07:16 GMT

sum(total Cost) / sum(total Units)

replace Cost and Units with your field