Jump to content
We've recently updated our Privacy Statement, available here. ×

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

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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])

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

 

pivot.png 

 

Best Regards

 

Fredrik

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...