2 Replies Latest reply: Apr 5, 2013 10:26 AM by Nick Gan RSS

    how to calculate average in collapsed pivot

    Nick Gan

      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?

        • Re: how to calculate average in collapsed 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.

            • Re: how to calculate average in collapsed pivot
              Nick Gan

              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.