Average instead of sum in pivot table

[#1]

Hi!

I've created a pivot table with som expressions which are represented as columns. Is it possible to show an avarage at the bottom of a columns instead of a partial sum?

[Gysbert_Wassenaar]

On the expression tab of the properties screen change the Total Mode to Average of Rows. You can select Average in the combobox after you check the third radio button.

[#3]

Total mode to Average is greyed I'm afraid...

[johnw]

Pivot tables don't let you set the total mode unless it's something new in a more recent version (we're on v10).  You can do it by changing your column expressions like this:

avg(aggr(current column expression,dimension1,dimension2,...))

See the attached example.

[#5]

Works beautifully.

I know I'm getting greedy here, but any way to add the subtotal as well ? (i.e. have two columns on the right, one for avg and another for tot).

Thanks!

[#6]

Sorry to resurrect a super old thread here but this illustrates a very specific problem I'm having.  Can you *please* tell me why the number 49.13 should not be 52.39?

Why is the average of (78.50 + 38.00 + 40.67) / 3 *NOT* 52.39?

Am I reading this wrong?  I'm having the exact same "problem" with some partial totals in a pivot I'm working on....the bottom line partials aren't averaging properly, and this example is behaving the same way.  *Unless* I'm not reading how these should display properly?

Thanks SO much for your clarification!

[shun_wong]

Hi Derek,
In Johns solution, you cant really use the average of the subtotals for your final total. You should add up all the individual lines, and divide by the total number.

i.e. (72,85,30,23,61,7,31,84) / 8 =  49.125

[#8]

Hi Josh,

I'd like to know if there is a solution for when you want to display your dimmensions as columns and your expressions as rows, i.e., showing the average of each of your expressions in a column.

Thank you very much,

[svenkita]

Attached is a solution for having the totals in pivots as averages

I have used the Dimensionality() to do this

If(Dimensionality()=1,Avg(SALES), Sum(SALES))

[pratap6699]

what is dimensionality(),,,,,,will you explain something about that