Forum Discussion
Customer Segmentation By Dynamic Percentages
Hello,
I am struggling to perform a correct segmentation of customers based on their [Total Revenue] as follows:
First 20% of them = Big customers
Next 30% of them = Medium customers
Last 50% of them = Small customers
I have only a fact table where I created a calculated column for rank (based on total sales per customer for all years):
Rank =
RANKX(
ALL('Fact'),
CALCULATE(
SUM('Fact'[Total]),
ALLEXCEPT('Fact',
'Fact'[Customer Number]
)
),,
DESC,
Dense
)
| id | Customer Number | Name | City | Year | Month | Total | Rank |
| 2 | 1 | Alpha | Paris | 2019 | 2 | €800 | 1 |
| 1 | 1 | Alpha | Paris | 2019 | 1 | €1,000 | 1 |
| 3 | 1 | Alpha | Paris | 2020 | 1 | €550 | 1 |
| 4 | 1 | Alpha | London | 2020 | 4 | €1,000 | 1 |
| 5 | 1 | Alpha | London | 2021 | 5 | €450 | 1 |
| 6 | 1 | Alpha | London | 2021 | 5 | €300 | 1 |
| 8 | 2 | Beta | New York | 2020 | 6 | €600 | 2 |
| 7 | 2 | Beta | London | 2019 | 4 | €600 | 2 |
| 9 | 2 | Beta | New York | 2021 | 1 | €800 | 2 |
| 10 | 2 | Beta | Bucharest | 2021 | 1 | €200 | 2 |
| 11 | 2 | Beta | Bucharest | 2021 | 2 | €100 | 2 |
| 15 | 4 | Theta | Berlin | 2020 | 11 | €400 | 3 |
| 16 | 4 | Theta | Berlin | 2021 | 1 | €660 | 3 |
| 17 | 4 | Theta | London | 2021 | 2 | €320 | 3 |
| 18 | 4 | Theta | New York | 2021 | 3 | €870 | 3 |
| 19 | 5 | Gamma | Paris | 2020 | 9 | €990 | 4 |
| 20 | 5 | Gamma | Paris | 2021 | 6 | €1,200 | 4 |
| 22 | 6 | Epsilon | Paris | 2021 | 2 | €600 | 5 |
| 21 | 6 | Epsilon | London | 2019 | 11 | €1,200 | 5 |
| 25 | 8 | Eta | Berlin | 2019 | 12 | €695 | 6 |
| 26 | 8 | Eta | Tokyo | 2021 | 3 | €880 | 6 |
| 14 | 3 | Omega | Paris | 2021 | 4 | €500 | 7 |
| 12 | 3 | Omega | Paris | 2019 | 8 | €680 | 7 |
| 13 | 3 | Omega | Budapest | 2020 | 12 | €220 | 7 |
| 29 | 10 | Phi | Paris | 2021 | 1 | €458 | 8 |
| 30 | 10 | Phi | Berlin | 2021 | 1 | €710 | 8 |
| 23 | 7 | Delta | Bucharest | 2020 | 6 | €855 | 9 |
| 24 | 7 | Delta | Bucharest | 2020 | 8 | €214 | 9 |
| 27 | 9 | Lambda | London | 2021 | 3 | €325 | 10 |
| 28 | 9 | Lambda | Paris | 2020 | 2 | €652 | 10 |
Having only 10 distinct customers, the segmentation should be:
First 2 ranked = Big Customers
Next 3 ranked = Medium Customers
Last 5 ranked = Small customers
However I don't know how to dynamically peform this in a calculated column in the fact table (I only hardcoded it) so I use the column in a matrix like this (for example):
+ Big Customers Total Revenue
Customer 1 xxx
Customer 2 xxx
+ Medium Customers
Customer 4 yyy
Customer 5 yyy
Customer 6 yyy
+ Small Customers:
Customer 3 zzz
Customer 7 zzz
Customer 8 zzz
Customer 9 zzz
Customer 10 zzz
4 Replies
- AnonymousNot applicable
What do you mean by saying "dynamically in a calculated column"? If you want this to change when you filter a table in a visual or Filter Pane, then this is not possible. Tables in PBI are static. Once calculated/refreshed, they don't change.
- RazwanHelper I
Anonymous
Sorry, I don't want it to be changed in a visual or filter pane.
In the end I would like to have that segmentation (Big, Medium, Small) in a matrix. Then, if you will expand a group it will show their corresponding customers ranked by their revenue.
The struggle is the calculation itself in a calculated column of:
First 20% of customers = Big customers
Next 30% of customers= Medium customers
Last 50% of customers = Small customers - rohanjha1988Helper II
After the Rank column, create another calculated column and use Switch function.
Cust_Categorization = SWITCH([Rank], 1, "Big customers", 2, "Big customers", 3, "Medium Customers", 4, "Medium Customers" , 5, "Medium Customers", "Small customers").
- RazwanHelper I
rohanjha1988
Thanks for your suggestion, but if in time the number of customer increases, then hardcoding will not work.
I already tried hardcoding and it's working only for current number of customers which is 10.