topic Re: Buckets based on revenue in DAX Commands and Tips

Buckets based on revenue

Julia2023 — Wed, 20 Nov 2024 09:33:38 GMT

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)

Re: Buckets based on revenue

FreemanZ — Wed, 20 Nov 2024 09:52:38 GMT

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

Re: Buckets based on revenue

Julia2023 — Wed, 20 Nov 2024 10:03:10 GMT

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.

Re: Buckets based on revenue

FreemanZ — Wed, 20 Nov 2024 10:16:32 GMT

hi Julia2023 ,

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

Re: Buckets based on revenue

Julia2023 — Wed, 20 Nov 2024 10:28:32 GMT

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

Re: Buckets based on revenue

johnt75 — Wed, 20 Nov 2024 12:01:58 GMT

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

Re: Buckets based on revenue

FreemanZ — Thu, 21 Nov 2024 08:38:18 GMT

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.