Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
=Sum(Aggr(FirstSortedValue(Volume,-Price),Material,Vendor))
Regards,
Antonio
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))
Hi
antonio, without selecting material the value is coming .
But, when we select material i is showing zero value
Hi sunny ,
it is showing zero value
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))
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)
)
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