Maximum Sum of Dimensions - Aggregation

[lorenwood]

Hello all, wanted to know if anyone can help with this issue: I have a table see below:

Payer	Restrictions	Type	Volume
A	N	Actual	300
A	N	Actual	500
A	Y	Preferred	200
B	N	Actual	300
B	Y	Preferred	200
B	Y	Preferred	500

What I need to calculate is based on the  highest sum of the volume , show only associated dimensions with the highest sum of volume at that level:

So for Payer A , the result should be Volume =800 and Restrictions = 'N' and Type = 'Actual'

For Payer B, the result should be Volume = 700 and Restrictions = 'Y' and Type ='Preferred' so the end result should look something like this:

Payer	Restrictions	Type	Volume
A	N	Actual	800
B	Y	Preferred	700

Is there any way to accomplish this in an expression in a straight table?

Any help would appreciated !

Thanks

Loren

[whiteline]

Hi.

If you need the expression you can start from that one:

=if(Sum (Volume)>Sum (total<Payer>Volume)/2, Sum (Volume))

It works for the sample provided, but I guess you real data are more complex.

Also consider calculating the flag that the row is relative to the highest sum of the volume in script.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw