Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Thanks
This worked
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
in properties of this expression there will be an option "Show total as". There you can choose 'Avg'
Where to see the properties of the expression
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?
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 |
True, That means it has aggregate only for dates not for Dept. You can try something like below
=Avg( Aggr( Sum(Total), Dept, Date))
Doesn't work