Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
saanvikamaruva
Frequent Visitor

Categorize the customers based on entire sales

Hi All,

 

Please help me on the below problem.

 

Categorize the customers into Score between 1 to 4 based on the Total Sales value of the customer in the entire data

    • Bottom 25% customers – Score =1
    • Next 25% customers – Score =2
    • Next 25% customers – Score =3
    • Top 25% customers – Score =4

 

Customer NameSales
Claire Gute261.96
Claire Gute731.94
Darrin Van Huff14.62
Sean O'Donnell957.5775
Sean O'Donnell22.368
Brosina Hoffman48.86
Brosina Hoffman7.28
Brosina Hoffman907.152
Brosina Hoffman18.504
Brosina Hoffman114.9
Brosina Hoffman1706.184
Brosina Hoffman911.424
Andrew Allen15.552
Irene Maddox407.976
Harold Pawlan68.81
Harold Pawlan2.544
Pete Kriz665.88
Alejandro Grove55.5
Zuschuss Donatelli8.56
Zuschuss Donatelli213.48
Zuschuss Donatelli22.72
Ken Black19.46
Ken Black60.34
Sandra Flanagan71.372
Emily Burns1044.63
Eric Hoffmann11.648
Eric Hoffmann90.57
Tracy Blumstein3083.43
Tracy Blumstein9.618
Tracy Blumstein124.2
Tracy Blumstein3.264
Tracy Blumstein86.304
Tracy Blumstein6.858
Tracy Blumstein15.76
Matt Abelman29.472
Gene Hale1097.544
Gene Hale190.92
Steve Nguyen113.328
Steve Nguyen532.3992
Steve Nguyen212.058
Steve Nguyen371.168
Linda Cazamias147.168
Ruben Ausman77.88
Erin Smith95.616
Odella Nelson45.98
Odella Nelson17.46
Patrick O'Donnell211.96

 

Thanks, Siva

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @saanvikamaruva ,

Here are the steps you can follow:

1. Create calculated column.

all_Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Customer Name]=EARLIER('Table'[Customer Name])))
rank = RANKX('Table','Table'[all_Sales],,DESC,Dense)
Customers – Score =
var _all_count = DISTINCTCOUNT('Table'[Customer Name])
var _divide = 1-DIVIDE('Table'[rank],_all_count)
return
SWITCH(
    TRUE(),
    _divide<=0.25,1,
    _divide>0.25&&_divide<=0.5,2,
    _divide>0.5&&_divide<=0.75,3,
    _divide>0.75&&_divide<=1,4)

3. Result:

vyangliumsft_0-1640223737943.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @saanvikamaruva ,

Here are the steps you can follow:

1. Create calculated column.

all_Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Customer Name]=EARLIER('Table'[Customer Name])))
rank = RANKX('Table','Table'[all_Sales],,DESC,Dense)
Customers – Score =
var _all_count = DISTINCTCOUNT('Table'[Customer Name])
var _divide = 1-DIVIDE('Table'[rank],_all_count)
return
SWITCH(
    TRUE(),
    _divide<=0.25,1,
    _divide>0.25&&_divide<=0.5,2,
    _divide>0.5&&_divide<=0.75,3,
    _divide>0.75&&_divide<=1,4)

3. Result:

vyangliumsft_0-1640223737943.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thank you @v-yangliu-msft , it works 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.