I need to select products where the top 2 suppliers of that product hold 80% or more of the total sales for each product

[David Pesante 5]

I am trying to do something that I think should be easy but can't figure it out to save my life. Here is the sample data I am using:

 

 

 

Product

Supplier

Sales Amount

 

 

ABC

A

890

 

 

ABC

B

25

 

 

ABC

C

20

 

 

ABC

D

10

 

 

ABC

E

10

 

 

ABC

F

10

 

 

ABC

G

10

 

 

ABC

H

10

 

 

ABC

I

10

 

 

ABC

J

5

 

 

XYZ

A

450

 

 

XYZ

B

450

 

 

XYZ

C

30

 

 

XYZ

D

10

 

 

XYZ

E

10

 

 

XYZ

F

10

 

 

XYZ

G

10

 

 

XYZ

H

10

 

 

XYZ

I

10

 

 

XYZ

J

10

 

 

XXX

A

120

 

 

XXX

B

110

 

 

XXX

C

100

 

 

XXX

D

100

 

 

XXX

E

100

 

 

XXX

F

100

 

 

XXX

G

100

 

 

XXX

H

100

 

 

XXX

I

90

 

 

XXX

J

80

 

 

ZZZ

A

450

 

 

ZZZ

B

210

 

 

ZZZ

B

190

 

 

ZZZ

C

40

 

 

ZZZ

D

40

 

 

ZZZ

E

30

 

 

ZZZ

F

20

 

 

ZZZ

G

5

 

 

ZZZ

H

5

 

 

ZZZ

I

5

 

 

ZZZ

J

5

 

 

 

below are the results that I am trying to come up with:

 

 

 

Product

Total Sales

Top 2 Suppliers Total Sales

% of Total

Selected

 

 

ABC

1000

915

91.50%

X

 

 

XYZ

1000

900

90.00%

X

 

 

XXX

1000

 

230

 

23.00%

 

 

 

ZZZ

1000

850

85.00%

X

 

 

 

 

A variation could be to simple do a uniquecount of suppliers that make up the first 80% of sales for any given product.

Basically, trying to see if we can have an expression that will help me do an 80/20 analysis in terms of Suppliers per Product.

Thoughts

[Shashank Gurnalkar]

Following are the steps I performed to achieve this.

 

1. Create a calculated column [sum] which will calculate the sum of the sales for each product:

Sum([sales Amount]) OVER ([Product])2. Create another calculated column [rank] which will calculate the rank of sales amount for each product.

Rank([sales Amount],"desc",[Product])Now create a final calculated column [Final] which will evaluate the product whose top two suppliers are forming above 80% sales. If yes, then it is evaluated to 1. If not, then 0.

If(First((If(If(([rank]=1) or ([rank]=2),[rank]) is not NULL,Sum([sales Amount]) over ([Product]))) / [sum]) OVER ([Product])>=0.8,1,0)

You can refer to [test1],[test2],[test3], and [test4] columns for better understanding where I have broken the above expression into smaller chunks.

 

Refer to the attached analysis file.

[Fredrik Rosell]

Hello,

If you want to produce that result directly using custom expressions in a visualization, so it will be responsive to filtering, the expression could look something like the following:

Sum([sales Amount]) as [Total Sales],

 

Sum(If(Rank([sales Amount],"ties.method=first","desc",[Product])

[David Pesante 5]

Wow... guys! good stuff! I do need the ability to these to react to filtering so I could not go down the calculated route which I did know how to do (I should have mention this on my original post)

I have a different variation that I thought I would know how to do once I got an answer to this but looks like it is not as simple... Basically, what if the question was "How many suppliers make up 80% of the sales. I would want a resulting table that looks like this:

Any thoughts on this Should I open another post

thanks in advanced!

 

 

 

Product

Total Sales

# of Unique Suppliers that Covers the first 80% (stop counting once Csum exceeds 80%)

 

 

ABC

1000

2

 

 

XYZ

1000

2

 

 

XXX

1000

8

 

 

ZZZ

1000

3

[David Pesante 5]

Fredrik,

 

I just noticed that the results for product ZZZ are not accurate. If you look at the data supplier B has two entries under ZZZ ... looks like you are only picking up the first entry of B en showing/calculating the ToTal Sales for the top 2 suppliers (A and B). You come up with 660 (450 + 210) but it should be showing 850 (450 + 210 + 190).

 

I tried this variation but with no luck:

 

Sum(If(Rank(Sum([sales Amount]) OVER ([Product]),"ties.method=first","desc",[Product])<3,Sum([sales Amount]) OVER ([Product]),0)) as [Top 2 Supplier Total Sales]

 

Any thoughts on how I can do the "aggregation" by product/supplier then do the ranking, etc.

 

thanks!

 

ZZZ	A	450
ZZZ	B	210
ZZZ	B	190

[Fredrik Rosell]

Ahh - I didn't notice that you had that additional complication. Well, depending on if your use case allows it, an easy solution would be that you just create a new table, with a pivot transformation on the original data so you only get one row per product and supplier, and then use that one for the cross table.

 

 

 

Best Regards

 

Fredrik