how to calculate average in collapsed pivot

[#1]

Hi,

I am creating a PIVOT.

Say, I have 2 column > Year | Zone | Figures

When the pivot is fully expanded, the pivot should show the sum of figures, so i do SUM(FIGURE)

When the pivot is collapsed, to Year, i need to do the average, is it possible to do this in PIVOT?

[whiteline]

Hi.

Yes its possible.

There are at least two ways:

1) =Avg(aggr(Sum(Figures), Year, Zone))

2) =if(dimensionality()=2, Sum(Figures), Avg(Figures))

The last one gives just simple average, not average of sums.

[#3]

Hi Whiteline,

Thanks, it works.

But, the games just get complicated.

Previously was single dimention, but now, i have second dimension across the pivot.

So, imagine vertical i have COMPANY | PRODUCT

horizontally, i have MONTH | WEEK.

and i need avg when both dimentions are collapse.

here is what i have done,  but the avg is wrong...

=if(SecondaryDimensionality()=1 and Dimensionality()=1, avg(aggr( (sum(figure),  WEEK)) , (sum(figures)))

Could you help ?

Thanks.