Top Suppliers for 80% Spend

[#1]

Hello everyone,

I have these transactions data.

Product	Supplier	Spend
Product A	Supplier 1	2000
Product A	Supplier 2	1200
Product A	Supplier 3	700
Product A	Supplier 4	500
Product A	Supplier 5	500
Product A	Supplier 6	400
Product A	Supplier 7	350
Product A	Supplier 8	325
Product A	Supplier 9	250
Product A	Supplier 10	200
Product B	Supplier 1	2000
Product B	Supplier 2	1500
Product B	Supplier 3	750
Product B	Supplier 1	700
Product B	Supplier 2	700
Product B	Supplier 3	400
Product B	Supplier 4	650
Product B	Supplier 5	200
Product B	Supplier 6	100
Product C	Supplier 1	250
Product C	Supplier 2	250
Product C	Supplier 3	200
Product C	Supplier 4	200
Product C	Supplier 5	150

I would like to create this following report (in pivot table or table). I can't seem to get the last column, which is the count (distinct) of Suppliers contributing up to 80% of the spend for each Product.

Product	Spend	# of Suppliers (Distinct)	80% Spend	# of Suppliers (Distinct - 80% Spend)
Product A	6425	10	5140	5
Product B	7000	6	5640	3
Product C	1050	5	840	3

Could anyone please help advise me on what expressions should i use to get that last column? Many thanks in advance!

[#2]

Seems that you are right on that note. My real data definitely doesn't come already sorted like the sample above. Moreover, I found that if we have 1 big supplier that has >80% of spend, then the count of supplier is 0 (which should be 1 in real case).

[Digvijay_Singh]

The work around to show 1 big supplier is as below -

Aggr(

  Count(

  if(Aggr(top(Sum(Spend)/Sum(Total<Product>Spend)),Product)>0.8,1,

  if(Aggr(Rangesum(Above(Sum(Spend)/Sum(Total<Product>Spend),0,Rowno())),Product,Supplier)<=0.8,Supplier))

  )

  ,Product)

But I suppose the expression will still exclude last supplier above 80% in the situation when 4 suppliers contribute 79% or so and adding one more goes above 80%. The last one won't be counted.

You would need to sort tables spend wise in the script to make it work as swuehl‌ pointed out.(Thanks!)

I tried to use new sortable Aggr function but I think it doesn't allow expression based sorting(Sum(Spend)).

See the attached sample where I changed the script to sort the table spend wise, surprisingly I need to change the Supplier field name to see the 'Order By' impact when doing resident load of old table, swuehl can you pl help to understand this, Is it related to unchanged symbol table order for supplier,not sure