Pareto (80/20) Customer Segmentation

[slashfemme]

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!

Thank you!

[v-chuncz-msft]

@slashfemme,

You may try adding PERCENTILEX.INC Function.

[slashfemme]

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]

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!