TOTAL does not work within AGGR?

[greend21]

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.

[Anil_Babu_Samineni]

Can you post full expression?

[greend21]

Sorry, I got sidetracked. I'm using Aggr because the data can have multiple records per ID.

Sum(Aggr(Avg(Total Amount), ID))

[Anil_Babu_Samineni]

Thank you !!

Can we have an example where it fails for you, I am not seeing anything wrong in that?

[jonathandienst]

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.

[greend21]

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%

[tresesco · Accepted solution]

Are you looking for this?

[Anil_Babu_Samineni]

Try this?

Num((Avg(Amount)/Sum(TOTAL Aggr(Avg(Amount), ID))),'#,##0%')

[greend21]

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!