cancel
Showing results for
Did you mean:
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!

Thank you!

3 REPLIES 3
Community Support Team

## Re: Pareto (80/20) Customer Segmentation

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.
Frequent Visitor

## Re: Pareto (80/20) Customer Segmentation

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!

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!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 46 members 1,080 guests
Recent signins: