Forum Discussion
Helper IPareto (80/20) Customer Segmentation
Helper IHi 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!
Helper II 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!