cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slashfemme Frequent Visitor
Frequent Visitor

Pareto (80/20) Customer Segmentation

Hello again! 

 

I currently have my customers grouped in static tiers (high, mid and low by total sales ranges) in a separate table so I can create a slicer. Is there a way to group these customers by the percentage of sales, such as the top 20% and bottom 80%? I'd like to recreate the following measure and chart but with the 80/20 rule that is dynamic if I change a slicer to a different country or year.

 

Customer Sales by Group =
CALCULATE([Total Sales],
FILTER(VALUES(Customers[Customer Name] ),
COUNTROWS(
FILTER( 'Tiers',
[Total Sales] >= Tiers[Min] && [Total Sales] < Tiers[Max] )) > 0 ))

 

I have a sales fact table with the obvious columns [customer id, product id, units sold, revenue, date sold] and measures for Total Sales, Total Qty. I also have a Customer Table and Product Table with relationships to the Sales Table.

 

Thank you!

donut.JPGtiers.JPG

 

Thank you!

3 REPLIES 3
Community Support Team
Community Support Team

Re: Pareto (80/20) Customer Segmentation

@slashfemme,

 

You may try adding PERCENTILEX.INC Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
slashfemme Frequent Visitor
Frequent Visitor

Re: Pareto (80/20) Customer Segmentation

Hi @v-chuncz-msft,

 

Thanks for the suggestion! I've never used this function before, and can't find any examples of it "in the wild" to see how it works. On my data, it looks like it takes 20% from each customer's sales, instead of segmenting those customers in the top 20%. Do you have an example of this function?

 

Thanks!

slashfemme Frequent Visitor
Frequent Visitor

Re: Pareto (80/20) Customer Segmentation

I am so close it's driving me nuts!

 

I made a helper table, Pareto, that has column MIN values for "Top20" and "Bottom80" to use in my pie chart with these measures:

Pareto        | Min

Top 20       | Top20

Bottom 80 | Bottom80

 

Top 20% Customer YN = IF(ISBLANK([Total Sales]),BLANK(),IF(RANKX(ALL(Customers),[Total Sales],,DESC)<(CALCULATE([Customer Count],ALL(Customers))*0.2),"Top20" ,"Bottom80"))

 

TEST Customer Sales by Pareto2 =
CALCULATE([Total Sales],
FILTER(VALUES(Customers[Customer Name] ),
COUNTROWS(
FILTER( 'Pareto 2',
[Top 20% Customer YN] = 'Pareto 2'[Min] )) > 0 ))

 

 

TEST Customer Count by Pareto2 =
CALCULATE([Customer Count],
FILTER(VALUES(Customers[Customer Name] ),
COUNTROWS(
FILTER( 'Pareto 2',
[Top 20% Customer YN] = 'Pareto 2'[Min] )) > 0 ))

 

 

I thought it worked great until I realized the customer count this was pulling was not 20% of my customers. [Total cust = 471, above pulls 111 and below pulls 94, the correct answer.]

 

The below formulas pulls the correct #  and sales of the top 20% customers, but I cannot for the life of me translate into a similar usable formula like the Top 20% Customer YN one above to relate to my Pareto ranking:

 

TEST Count of Top 20% =
VAR
customerpercent = DISTINCTCOUNT(Sales[Customer Number]) * 0.2
RETURN
CALCULATE([Customer Count], FILTER(VALUES(Sales[Customer Number]), RANKX(VALUES(Sales[Customer Number]),[Total Sales],,DESC) <= customerpercent))

 

TEST Sales of Top 20% =
VAR
customerpercent = DISTINCTCOUNT(Sales[Customer Number]) * 0.2
RETURN
CALCULATE([Total Sales], FILTER(VALUES(Sales[Customer Number]), RANKX(VALUES(Sales[Customer Number]),[Total Sales],,DESC) <= customerpercent))

 

Thanks!