How to take sum of amount based on max of one dimension aggregated by another dimension

[User0]

Hi,
I have data with the amount; each amount is linked with a contract number and id.
I want to take the sum of the amount field for each contract with max id and aggregate based on the contract number

Contract number	Amount	Id
1001	4500	2
1001	4500	4
1001	4900	5
1002	2122	1
1002	2500	2
1003	1220	4
1003	1000	3

So, for contract 1001 , amount should be - 4900 
for contract 1002 , amount should be - 2500
for contract 1003, amount should be - 1220

and the required sum should be (4900+2500+1220)=8620

[PedroNetto]

Hi @User0 !

Try: Sum( Aggr( max([number Amount], Contract))

Result:

[User0]

Hi @PedroNetto  thank you for suggestion, could you please tell me what is [number Amount], we need to take max of ID against the contract number 

[PedroNetto]

@User0 the correct formula is: Sum( Aggr( max([Amount], Contract))

[User0]

The amount need not be max, we need to take the amount with max id, for example:

Contract number	Amount	Id
1001	4500	2
1001	4500	4
1001	4000	5
1002	2122	1
1002	2000	2
1003	1000	4
1003	1300	3

So, for contract 1001 , amount should be - 4000 
for contract 1002 , amount should be - 2000
for contract 1003, amount should be - 1000

total amount = 7000.

[PedroNetto · Accepted solution]

@User0  finally!

Try: Sum(If(Aggr(NODISTINCT Max(Id),Contract)=Id,Amount))

Result:

[User0]

@PedroNetto  Thank you so much 🙂 It worked!!!