Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Customer Name | Sales |
Claire Gute | 261.96 |
Claire Gute | 731.94 |
Darrin Van Huff | 14.62 |
Sean O'Donnell | 957.5775 |
Sean O'Donnell | 22.368 |
Brosina Hoffman | 48.86 |
Brosina Hoffman | 7.28 |
Brosina Hoffman | 907.152 |
Brosina Hoffman | 18.504 |
Brosina Hoffman | 114.9 |
Brosina Hoffman | 1706.184 |
Brosina Hoffman | 911.424 |
Andrew Allen | 15.552 |
Irene Maddox | 407.976 |
Harold Pawlan | 68.81 |
Harold Pawlan | 2.544 |
Pete Kriz | 665.88 |
Alejandro Grove | 55.5 |
Zuschuss Donatelli | 8.56 |
Zuschuss Donatelli | 213.48 |
Zuschuss Donatelli | 22.72 |
Ken Black | 19.46 |
Ken Black | 60.34 |
Sandra Flanagan | 71.372 |
Emily Burns | 1044.63 |
Eric Hoffmann | 11.648 |
Eric Hoffmann | 90.57 |
Tracy Blumstein | 3083.43 |
Tracy Blumstein | 9.618 |
Tracy Blumstein | 124.2 |
Tracy Blumstein | 3.264 |
Tracy Blumstein | 86.304 |
Tracy Blumstein | 6.858 |
Tracy Blumstein | 15.76 |
Matt Abelman | 29.472 |
Gene Hale | 1097.544 |
Gene Hale | 190.92 |
Steve Nguyen | 113.328 |
Steve Nguyen | 532.3992 |
Steve Nguyen | 212.058 |
Steve Nguyen | 371.168 |
Linda Cazamias | 147.168 |
Ruben Ausman | 77.88 |
Erin Smith | 95.616 |
Odella Nelson | 45.98 |
Odella Nelson | 17.46 |
Patrick O'Donnell | 211.96 |
Thanks, Siva
Solved! Go to Solution.
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:
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
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:
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |