
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Get average in a pivot table
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 |
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Avg([Tenure(Mth)]) should have worked, but you can try this Avg(Aggr(Avg([Tenure(Mth)]), Director))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Avg([Tenure(Mth)]) should have worked, but you can try this Avg(Aggr(Avg([Tenure(Mth)]), Director))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
okay that make sense
