Average - Department level Sum

[vikas_nandanwar]

Hi,

My data is as below -

Date	Dept	Total
1-Jan	A	100
1-Jan	B	90
1-Jan	C	500
2-Jan	A	110
2-Jan	B	70
2-Jan	C	480
3-Jan	A	150
3-Jan	B	50
3-Jan	C	580
4-Jan	A	120
4-Jan	B	60
4-Jan	C	510
5-Jan	A	180
5-Jan	B	50
5-Jan	C	550

Pivot -

Row Labels	Sum of Total
1-Jan	690
2-Jan	660
3-Jan	780
4-Jan	690
5-Jan	780
Grand Total	3600

I want Average of Total of Date in Grand Total

Answer = 720

please help

[swuehl · Accepted solution]

Try as expression

=Avg( Aggr( Sum(Total), Date))

with Date being your pivot table dimension.

In a straight table chart, you can use Sum(Total) as expression with total mode on expression tab set to avg of lines.

[vikas_nandanwar]

Thanks

This worked

[vikas_nandanwar]

Hi

Addition to this My data has multiple entries of Dept

Date	Dept	Total
1-Jan	A	100
1-Jan	B	90
1-Jan	B	30
1-Jan	C	500
2-Jan	A	110
2-Jan	B	70
2-Jan	C	480
2-Jan	C	30
3-Jan	A	150
3-Jan	B	50
3-Jan	B	60
3-Jan	C	580
4-Jan	A	120
4-Jan	A	30
4-Jan	B	60
4-Jan	C	510
5-Jan	A	180
5-Jan	A	30
5-Jan	B	50
5-Jan	C	550

Pivot

Row Labels	Sum of Total
1-Jan	720
2-Jan	690
3-Jan	840
4-Jan	720
5-Jan	810
Grand Total	3780

Answer = 756

Pleaes help

[shraddha_g]

in properties of this expression there will be an option "Show total as". There you can choose 'Avg'

[vikas_nandanwar]

Where to see the properties of the expression

[Anil_Babu_Samineni]

This option won't work in Pivot table and only available this in straight table.

Why are you looking another option because stefan already gave right direction. Is that working or not?

[vikas_nandanwar]

Stefen's expression is working only if there are single entry for dept for single date
If there are multiple entries of dept for a single date the expression doesn't work, like

Date	Dept	Total
1-Jan	A	100
1-Jan	B	90
1-Jan	B	30
1-Jan	C	500

[Anil_Babu_Samineni]

True, That means it has aggregate only for dates not for Dept. You can try something like below

=Avg( Aggr( Sum(Total), Dept, Date))

[vikas_nandanwar]

Doesn't work