Set Analysis Get MAX and AVG values after aggregate same value on few dimensions level

[samp]

Hi All, need help on set analysis on below sample data and expected out put.

Load * Inline [
ID, YEAR, GRP1, GRP2, COMP, AMOUNT
1, 2020, app1, xyz, Comp1, 100
1, 2020, app2, xyz, Comp2, 300
1, 2020, app1, xyz, Comp1, 100
1, 2020, app2, xyz, Comp2, 600
2, 2020, app1, xyz, Comp3, 400
2, 2020, app2, ttt, Comp4, 200
3, 2020, app3, xyz, Comp5, 400
3, 2020, app2, xyz, Comp6, 300
3, 2020, app2, xyz, Comp6, 300
4, 2020, app1, xyz, Comp7, 500
4, 2020, app1, xyz, Comp7, 500
4, 2020, app2, ttt, Comp8, 200
4, 2020, app2, xyz, Comp8, 500
4, 2020, app1, ttt, Comp9, 200
]; 

Expected Out Put for just ID selection 1:

GRP1	Level Of Total	%Max Level Of Total	%Min Level Of Total	%Avg Level Of Total
app1	1600	1000/1600	200/1600	533/1600
app2	2000	900/2000	500/2000	666/2000

Here "Level Of Total" metric is working fine, but I am unable to get above numerators as in below images to calculate percentages,.

The selection ID = 1 is associated to GRP1 values(app1, app2) and GRP2 value ( xyz), my out put should exclude ID selection.

Measures:

1) Level Of Total: need total AMOUT for  field GRP1(app1,app2) and field GRP2(xyz) by excluding ID selection, below expression is working fine, I am ok with this.

AGGR(Sum({<YEAR={$(=Max(YEAR))},ID=>}AMOUNT),GRP1,GRP2) //ok

2) %Max Level Of Total: Here I need to get max value after sum of AMOUNTS on GRP1, GRP2 and COMP level as in below

image1 (selected app1 and xyz values since both are associated to ID:1 by excluding ID filter) same with Image2.

AGGR(MAX(AGGR(Sum({<YEAR={$(=Max(YEAR))},ID=>}AMOUNT),GRP1,GRP2,COMP)),GRP1,GRP2) //Not ok

3) and 4) Once max expression works fine then I can do for min and avg expressions.

Appreciate for your help, thanks in advance.

Expected Out Put for just ID selection 1:

GRP1	Level Of Total	%Max Level Of Total	%Min Level Of Total	%Avg Level Of Total
app1	1600	1000/1600	200/1600	533/1600
app2	2000	900/2000	500/2000	666/2000

[JGMDataAnalysis · Accepted solution]

You have to add the same set modifiers in the outer aggregation function...

[samp]

thanks it works