Forum Discussion

Razwan's avatar
Razwan
Helper I
5 years ago

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
)

 

 

 

idCustomer NumberNameCityYearMonthTotalRank
21AlphaParis20192€8001
11AlphaParis20191€1,0001
31AlphaParis20201€5501
41AlphaLondon20204€1,0001
51AlphaLondon20215€4501
61AlphaLondon20215€3001
82BetaNew York20206€6002
72BetaLondon20194€6002
92BetaNew York20211€8002
102BetaBucharest20211€2002
112BetaBucharest20212€1002
154ThetaBerlin202011€4003
164ThetaBerlin20211€6603
174ThetaLondon20212€3203
184ThetaNew York20213€8703
195GammaParis20209€9904
205GammaParis20216€1,2004
226EpsilonParis20212€6005
216EpsilonLondon201911€1,2005
258EtaBerlin201912€6956
268EtaTokyo20213€8806
143OmegaParis20214€5007
123OmegaParis20198€6807
133OmegaBudapest202012€2207
2910PhiParis20211€4588
3010PhiBerlin20211€7108
237DeltaBucharest20206€8559
247DeltaBucharest20208€2149
279LambdaLondon20213€32510
289LambdaParis20202€65210

 

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

  • Anonymous's avatar
    Anonymous
    Not 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.

  • 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

  • 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"). 

    • Razwan's avatar
      Razwan
      Helper 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.