Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |