Average in Pivot Total instead of sum

[sangram_01]

I have a pivot table which displays quarter wise count of employees,  I want to show averages in stead of sum in Total of pivot table , how to have average in total instead of sum ? 

I tired using Aggr function as 'avg(aggr(count(Emp),FYQ))' but it does not give appropriate result 

[Anil_Babu_Samineni]

The number is correct to me. Where is the wrong for you?

[sangram_01]

It is manually modified in column 3 after exporting in the Excel

[Gopinathan]

Hi,

Please use the dimensionality() function to write the average expression TOTAL rows.

For Example, if(Dimensionality()=0, avg(Value), sum(value)) in the expression. Dimensionality() function will return 0,1,2,.. based on the number of dimensions in the pivot table.

Thanks,

Gopi

[sangram_01]

Thanks Gopi, but it gives result as 1 instead of 2503 

[Gopinathan]

Share the sample data and the expression used like count(employee).

[Gopinathan · Accepted solution]

consider your dimension is Period and the expression is count(Employee) than the expression should be in the pivot table as,

if(Dimensionality()=0,Avg(aggr(Count(Employee),Period)), Count(Employee))

[sangram_01]

Great, Thanks this worked.