
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
TOTAL does not work within AGGR?
Has anyone else had issues where total does not work within the Aggr function or have any ideas for a work around?
For example, Sum(Aggr(Avg(Total Amount) does not actually show the total amount.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you post full expression?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, I got sidetracked. I'm using Aggr because the data can have multiple records per ID.
Sum(Aggr(Avg(Total Amount), ID))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you !!
Can we have an example where it fails for you, I am not seeing anything wrong in that?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This expression will not give the the total
It will calculate the average amount for all IDs and then sum that (ie multiply by) the number of IDs. Try the expression without the TOTAL keyword.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I know what the aggr function is doing. I need it because of the data structure. The issue is I need to calculate % of totals. So ideally I'd have the formula without TOTAL to get the amount per dimension and then the Formula with TOTAL unless I'm misunderstanding how total works.
This is how it looks:
ID | ID2 | Amount |
A | 1 | 100 |
A | 2 | 100 |
A | 3 | 100 |
B | 4 | 30 |
C | 5 | 10 |
D | 6 | 50 |
D | 7 | 50 |
From what I understand, Aggr would give me this:
ID | Amount |
A | 100 |
B | 30 |
C | 10 |
D | 50 |
I need to calculate % of total to get this:
ID1 | ID1Amount | % |
A | 100 | 53% |
B | 30 | 16% |
C | 10 | 5% |
D | 50 | 26% |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you looking for this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this?
Num((Avg(Amount)/Sum(TOTAL Aggr(Avg(Amount), ID))),'#,##0%')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So far so good. I didn't even think to move TOTAL to the outside sum function and was hung up trying other things for hours yesterday. Thank you!
