Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to get value of top 2 vendors spend for a particular item.
This is my sample data
Item ID | Vendoe name | Spend |
5112 | Vendor 1 | 3,000.00 |
5112 | Vendor 2 | 20,000.00 |
5112 | Vendor 3 | 25,000.00 |
6113 | Vendor 1 | 34,560.00 |
7889 | Vendor 1 | 56,000.00 |
9887 | Vendor 1 | 670,000.00 |
6113 | Vendor 2 | 34,520.00 |
9887 | Vendor 2 | 120,000.00 |
7889 | Vendor 2 | 3,000.00 |
5112 | Vendor 4 | 75,000.00 |
7889 | Vendor 3 | 10,000.00 |
9887 | Vendor 3 | 14,000.00 |
6113 | Vendor 3 | 98,760.00 |
3456 | Vendor 1 | 34,560.00 |
6113 | Vendor 4 | 100,000.00 |
7889 | Vendor 4 | 2,000.00 |
4562 | Vendor 1 | 456,890.00 |
6113 | Vendor 5 | 2,000.00 |
5112 | Vendor 5 | 60,000.00 |
3452 | Vendor 1 | 23,480.00 |
9870 | Vendor 1 | 18,000.00 |
9870 | Vendor 2 | 26,000.00 |
3452 | Vendor 2 | 35,000.00 |
6113 | Vendor 6 | 5,000.00 |
9887 | Vendor 4 | 12,556.00 |
Here i want to get total spend of top 2 vendors based on spend (i.e vendor 4 and vendor 3) for an item ID 6113. I don't want to write 6113 in formula, it should be automatic, it is 3rd item based on total spend.
I don't want to write the item ID number in formula.Is this possible? it is 3 rd spend item based on spend.
Thanks in advance
Regards
Nagarjuna
You mean you want the sum of top 2 vendor for 3rd spend item id?
=Max(Aggr(SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID])),[Item ID]),3)
Yes
I have replied.. check
Have you tried this?
=Max(Aggr(SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID])),[Item ID]),3)
Not working
Sorry , its working.
Thank you.
I have a doubt,why you have taken number 4 after spend in your formula.
Now i want total spend of item 6113.