Get average in a pivot table

[csh8428]

How can I get the average of a dimension in a pivot table? When using the "avg" function it takes the average of the entire data-set. In this example, I'm trying to get the average tenure of for each Director's subordinate's in a pivot table. But, when I use the avg function( avg(Tenure(Mth) ) it puts the average of the entire data set into each cell next to Director. What's the expression to get the average tenure for each director's EIDs while also displaying the overall average for the entire data set like you can in a simple pivot table in excel?

I've seen lots of other posts that are similar to this and haven't been able to convert the answers to work.

Sample Data

EID	Director	Tenure(Mth)	Tenure(Yr)
3584	Smith	44	3.7
4466	Smith	26	2.2
4071	Smith	0	0
2026	Smith	75	6.2
376	Jones	10	0.8
4053	Jones	37	3.1
3751	Jones	48	4
2594	Jones	14	1.2
4633	Johnson	48	4
154	Johnson	63	5.2
2974	Johnson	2	0.2
3473	Johnson	28	2.3

Expected output

Director	Average of Tenure(Mth)
Smith	36.25
Jones	27.25
Johnson	35.25
Total Avg	32.91666667

[sunny_talwar · Accepted solution]

Avg([Tenure(Mth)]) should have worked, but you can try this Avg(Aggr(Avg([Tenure(Mth)]), Director))

[csh8428]

tl;dr:  Avg([Tenure(Mth)]) was correct. A joined data-set caused the issue. Fixed the join and that function worked.

Since you said Avg([Tenure(Mth)]) I went back and looked at the data. What was causing the issue was that it was joined to another data-set using Director; however, the other dataset only had the director's last name whereas the one I was posting about has both last and first name, which explains why the total avg worked, but not the director level.

[sunny_talwar]

okay that make sense