13 Replies Latest reply: Jul 29, 2014 12:35 AM by Settu Periyasasamy

# Pivot table Sum of average

Hi All,

Give me the suggestion for the below screen shot. Attached the example file.

regards,

settu

• ###### Re: Pivot table Sum of average

Hi,

in that above Screen shot showing correct..nothing wrong in it..

In that average showing Average(1267+1281+1277)=1275..ie (1267+1281+1277)/3=1275..Hope this helps..

Dharma

• ###### Re: Pivot table Sum of average

Hi Dharmarasu,

Already there is an average for country level. I need the sum of Country average in the dimension level. i.e

if (dimensionality()=0, sum(801 average, 802 average and 803 average). Hope i clear now..

Settu

• ###### Re: Pivot table Sum of average

Hi

This expression will do it:

=Sum(Aggr(Avg(Sales), Country))

HTH

Jonathan

• ###### Re: Pivot table Sum of average

Sorry, correction to the above:

If(Dimensionality() = 0, Sum(Aggr(Avg(Sales), Country)), Avg(Sales))

• ###### Re: Pivot table Sum of average

hi Jonathan,

If we give that expression, the sum value is changed. Already the sum value is correct. i need to change the dimension total only. see the arrow mark in that picture.

• ###### Re: Pivot table Sum of average

Did you see my correction?

If(Dimensionality() = 0, Sum(Aggr(Avg(Sales), Country)), Avg(Sales))

• ###### Re: Pivot table Sum of average

hi jonathan,

thank you for your expression. It showing the value. but day value shows 0. see the below screen shot.

• ###### Re: Pivot table Sum of average

Hi Jonathan & Dharmarasu,

i got the result using the below expresson, and attached the final layout.

if(dimensionality()=0 and RowNo()=0 and ColumnNo()=0,

Sum(Aggr(Avg(Sales), Country,day1))/Count(DISTINCT day1),

if(Dimensionality()=0,

Sum(Aggr(Avg(Sales), Country,day1)),Avg(Sales)))

• ###### Re: Pivot table Sum of average

pls see this attachment

Dharma

• ###### Re: Pivot table Sum of average

Hi Dharmarasu,

i need the result like the below screen shot

• ###### Re: Pivot table Sum of average

Hi settu,

Pls see the above attached Application..it shows as it is what you required..

Dharma..

• ###### Re: Pivot table Sum of average

Hi Dharmarasu,

Thank you. i checked it.  average is not correct. see the red border in the above screen shot.

Instead of 6131, it should be 1226.

Settu

• ###### Re: Pivot table Sum of average

otherwise u can use this expression in

average

=Sum(Aggr(Avg(Sales), Country,day))