Buckets based on revenue

[Julia2023]

Hi all, I have 101 customers and want to divide them into 3 buckets, then calculate the sum of the revenue for each bucket. I created a rank measure and would like to use it to create these buckets. Any ideas on how to do this?  Thanks. 

Customer Rank = RANKX(ALL(Customers[Customer ID]), [Revenue], , DESC, Dense)

[johnt75]

I think you want the dynamic segmentation pattern - https://www.daxpatterns.com/dynamic-segmentation/ 

[FreemanZ]

hi @Julia2023 ,

try to add a calculated table like:

Table =

VAR _table1 =

ADDCOLUMNS(

    ALL(Customers[Customer ID]),

   "Revenue2", [Revenue]

)

VAR _table2 =

ADDCOLUMNS(

    _table1,

   "rank", 

    RANKX(

        ALL(Customers[Customer ID]), 

        [Revenue], , DESC, Dense

    )

)

VAR _table3 =

ADDCOLUMNS(

    _table2,

    "Bucket"

     SWITCH(TRUE(), [rank]<=34, "1-34", [rank]>=68, "68-101", "35-67")

)

VAR _result =

ADDCOLUMNS(

    SUMMARIZE(_table3, [Bucket]),

    "BucketRevenue",

    CALCULATE(SUM([Revenue2])

)

RETURN _result

[Julia2023]

@FreemanZ , thanks. Actually, the table isn't working here, as I want to have a dynamic measure. I mean, if new customers come in, the buckets should be modified as well, based on the total count of customers. 

[FreemanZ]

hi @Julia2023 ,

With new customers, how do you define the bucket boundary?

[Julia2023]

I tried this one, but it only returns Bucket 1 for all customer IDs:
Bucket =
VAR TotalRanks = COUNTROWS(ALL(Customers[Customer ID])) VAR RankValue = [Customer Rank]   VAR BucketSize = DIVIDE(TotalRanks,3, 1)   RETURN SWITCH(     TRUE(),     RankValue <= BucketSize, "Bucket 1",     RankValue <= 2 * BucketSize, "Bucket 2",       RankValue > 2 * BucketSize, "Bucket 3"   )

[FreemanZ · Accepted solution]

hi @Julia2023 ,

Supposing you have a data table like:

try to plot a visual with a sum of [sales] column and a calculated column like:

Bucket = 
VAR RankValue =
RANKX(
    ALL(data[ID]),
    CALCULATE(SUM(data[sales]), ALLEXCEPT(data, data[ID])),
    ,DESC
)
VAR _totalrank = COUNTROWS(VALUES(data[ID]))
VAR BucketSize = DIVIDE(_totalrank, 3, 1)  
RETURN 
SWITCH(     
    TRUE(),     
    RankValue <= BucketSize, "Bucket 1",     
    RankValue <= 2 * BucketSize, "Bucket 2",       
    RankValue > 2 * BucketSize, "Bucket 3"   
)

it worked like below:

or directly write a calculated table like:

BucketTotal = 
VAR _table1 =
ADDCOLUMNS(
    data,
    "RankValue",
    RANKX(
        ALL(data[ID]),
        CALCULATE(SUM(data[sales]), ALLEXCEPT(data, data[ID])),
        ,DESC
    )
)
VAR _totalrank = COUNTROWS(VALUES(data[ID]))
VAR _bucketsize = DIVIDE(_totalrank, 3, 1)  
VAR _table2 =
ADDCOLUMNS(    
    _table1,
    "Bucket2",
    SWITCH(    
        TRUE(),    
        [RankValue] <= _bucketsize, "Bucket 1",    
        [RankValue] <= 2 * _bucketsize, "Bucket 2",      
        [RankValue] > 2 * _bucketsize, "Bucket 3"  
    )
)
VAR _table3 = 
ADDCOLUMNS(    
    SUMMARIZE(_table2, [Bucket2]),    
    "Revenue", 
    VAR _bucket =  [Bucket2]
    RETURN
    SUMX(
        FILTER(
            _table2,
            [Bucket2] = _bucket
        ),
    data[Sales]
    )
)
RETURN _table3

it worked like:

please find more info in the attachment. 

p.s. we can't treat measure value as categorical value and plot against other measures directly. In this case, bucket needs to be a calculated column.