Sum of Filed on a condition of different field ?

[gauravgg]

Hi All

I want to sum the Volume who has Max Price group by Material Vendor combination and show in KPI

For example

I have the following table

Material	Vendor	Price	Volume
1	A	10	100
1	A	20	200
2	B	40	300
2	B	50	400

Expected Output  in KPI .

For Material 1 and Vendor A  the max price is 20 , so we have to consider 200 as Volume for that Material Vendor Combination

same goes for

Material 2 and Vendor 2 the max price is 50 , so we have to consider 400 as Volume for that Material Vendor Combination

So , in kpi  the value should show 600

200 + 400 = 600

Can anyone help me in this ?

Thanks in Advance

Regards

Gaurav Gopale

[antoniotiman]

May be this

=Sum(Aggr(FirstSortedValue(Volume,-Price),Material,Vendor))

Regards,

Antonio

[sunny_talwar]

Another option would be this

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Volume), Sum(Volume)), Material, Vendor, Price))

or this

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Volume), Volume), Material, Vendor, Price))

[gauravgg]

Hi

antonio, without selecting material the value is coming .

But, when we select material i is showing zero value

[gauravgg]

Hi sunny ,

it is showing zero value

[antoniotiman]

[sunny_talwar]

My bad, I had typos

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Price), Sum(Volume)), Material, Vendor, Price))

and this

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Price), Volume), Material, Vendor, Price))

[gauravgg]

Hi

Sunny and Antonio , thanks for the reply

The following expression in working.

sum(

Aggr(If(PRICE_MAT_New = Max(TOTAL <MATERIAL,VENDOR> PRICE_MAT_New),

FirstSortedValue(VOLUME,-VOLUME

)

), MATERIAL,VENDOR, PRICE_MAT_New)

)

[sunny_talwar]

Awesome, we are glad you were able to figure it out yourself. Please close this thread by marking your own response as correct.

Best,

Sunny