AVG, AGGR on straight table.

[Fquiroga95]

Hi, the next table is what I need to achieve...

So week, distributor, etc are dimensions.

Revenue is a metric created as a variable in the backend, basically is:

vRevenue=sum(SalesRevenue*$(vCurrency)), and it depends on the front end selection of Currency. 

I need to get the average of the revenue like in the table showed by week and segment.

For example, in the first case, week 2, lower midmarket segment: I have revenue of = 18718.42 + 99420.4 + 1878.11. As there are three lines, the average would be (18718.42 + 99420.4 + 1878.11 / 3 )= 120017.

I need that number in each line. Could be more than three lines, that's an example, but basically the average by week, and then by segment, depending of the amount of records in the table.

I don't know how to achieve the AVG columb, I tried with AGGR:

=AGGR(NODISTINCT
Num(avg(SalesRevenue*$(vCurrency))
,Week,Segment)

But it doesn't work, it brings me the wrong values.

Would appreciate any help.

[rubenmarin]

Hi, I think you need TOTAL qualifier here, try with:

Avg(TOTAL <Week,Segment> SalesRevenue*$(vCurrency))

TOTAL is used to ignore dimensions, and group values by the fields defined between <>