• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying Pareto Analysis 3 times for classifying Customers

S

shahvaladi

New Member
hello friends,

I want to do classification on my customers based on pareto rule but in my own way.

My Classes Are : A+ , A , B and C

For emxample, I have total number of 1000 customers summrized by their Sales Amount.

I want to do pareto Analysis 3 times to group my customers like below :



For example :

Step 1 : 1000 customer : 80% of Sales (400 Customers)-----20% of Sales (600 Customers) So 600 customers = Class C

Step 2 : 400 customer : 80% of Sales (250 Customers)-----20% of Sales (150 Customers) So 150 customers = Class B

Step 3 : 250 customer : 80% of Sales (80 Customers)-----20% of Sales (170 Customers) So 170 customers = Class A

and 80 Customers A+ Class.

It would be appretiated if you guys help me do this either in Powerpivot using dax or Power Bi.

So that I could have the list of each customers and dynamically filter through different categories in my data model.Class.png
 
Could you attach a workbook with your 1000 or so customers and sales? Help us to help you.

And what version of Excel are you using? MS365?
 
Last edited:

shahvaladi​

Please reread Forum Rules - those are for You too:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
vletm said:

shahvaladi​

Please reread Forum Rules - those are for You too:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
Click to expand...
Thanks my friend , I did not know that I had to mention it here. I just did'nt get the answer so I tried here .
 

shahvaladi​

About Your: Thanks my friend , I did not know that I had to mention it here. I just did'nt get the answer so I tried here .
Do You really mean that You skipped next link from the first page?

New Users - Please Start Here​

Do You really mean that You skip same kind of thing in other Forum too?
Did You read that link?
 
shahvaladi said:
Many Thanks for helping me, I Attach an excel file that I applied my method by excel formulas.
I am using Office LTSC 2021 version.
Click to expand...
See attached Sheet2
I've Assumed Table1 is your source data and copied that to the sheet then addecd a Class column with formula:
=INDEX({"A+","A","B","C"},MATCH(SUMIF([Qty],">=" & [@Qty]),CHOOSE({1,2,3,4},0,0.8^3,0.8^2,0.8^1)*SUM([Qty])))

Then a a few calculations to check the results around cell F2
Play around with changing ">=" to ">".
The results aren't as close to 20%/80% as my pic in my last post but that may be due to the distribution of Qty values (many duplicate Qtys).

Not as close as your results on sheet1 but I sorted your Table15 at cell AE1 by Qty and found Customers with the same quantity classed differently!:

1689595591863.png

So I added my formula to that table too (Class2) for comparison; no Qty classed differently.
Maybe the percentages I've got are as close as you can get with your data.

If still needed, I'll look at a Power Query solution, I'll wait to hear back.
Is your raw data Table1 on Sheet1?
 

Attachments

  • Chandoo54204test query.xlsx
    246.1 KB · Views: 0
Last edited:
You must log in or register to reply here.
Back
Top