Create given number of buckets from a measure

[johnnyjohn]

Hello, I have the measure below.

Sum(n0_is_at_mid_bps * n0_value_EUR) / Sum(n0_value_EUR)

What is the best way to create, say 20 buckets for the this measure, dynamically as an expression, not in the load script.

The parameter to the class function doesn't allow this currently. 

Thanks

[vincent_ardiet_]

If the bucket sizes are identical across the range, you can use Mod() to compute a modulo of your measure and combined with a Match() this could maybe do the trick.

[johnnyjohn]

Could you provide an example? I do need the bucket sizes to be equal and the numbers from my measure may be positive or negative. Thanks 

[vincent_ardiet_]

In fact, this is not with MOD but with DIV, something like this for example:
if(sum(Expression1)>=0
,pick(rangemin(1+fabs(div(sum(Expression1),500)),7),'0 < 500','500 < 1000','1000 < 1500','1500 < 2000','2000 < 3000','2000 < 3000','>=3000')
,pick(rangemin(1+fabs(div(sum(Expression1),500)),7),'-500 < 0','-1000 < -500','-1500 < -1000','-2000 < -1500','-3000 < -2000','-3000 < -2000','<=-3000'))

Ideally, you can create the parameters of the pick in your loading script in a variable, which could be more dynamic. Note that I have created a 2000-3000 zone putting it twice in the series, so you can have unequal intervals if only they are multiples of the smallest one (here 500).

[johnnyjohn]

Ah but I think this is my issue here - I don't want to hard set buckets to specific sizes. I want to create buckets of equal width according the values existing in the dataset (which should respect the filters).

So for example if I have 40 values ranging from 1 to 40, 20 baskets of 1-2, 2-3, 3-4, 18-19,19-20, etc... would be created. If I filtered my dataset and restricted the data such that my data would only have 1 of 20 values, 20 buckets would still be created, with 1, 2, 3, 4, etc... buckets. 

So not quite quartile bucketing, but strict setting of buckets according to the values available and the number of buckets to be set. 

I recall doing this a few years ago, and setting a slider as a variable that let me decide how many buckets to  show, but I can't recall how now. 

[vincent_ardiet_]

The only way is to use the Class function in this case.
What is the issue when you use it?

[johnnyjohn]

Class function create how ever many bucket are needed to split the data by groups of x, where x is the input parameter. What I am trying to do is create a GIVEN, FIXED amount of buckets, where the edges of the buckets are delineated by splitting the data equally into that fixed number of bucket. Hope that makes sense

[vincent_ardiet_]

Try something like this, this is creating about 20 buckets above 0 or below 0, so 40 buckets if data are equally spread around 0 rounding to a multiple of 50 for buckets' size:
class(sum(Expression1),ceil(Div(rangemax(max(TOTAL aggr(sum(Expression1),Dimension1)),-min(TOTAL aggr(sum(Expression1),Dimension1))),20),50))

[johnnyjohn]

I tried your suggest solution, with no luck. I tried to do something similar, with the expression below, which just looks at the absolute difference between the min and the max, and divide that by 20. This should in theory give me the size needed for each bucket to create 20 buckets, but the expression fails when using it as a dimension on a histogram or barchart for instance. Any ideas?

=CLASS(
n0_is_at_mid_bps,
CEIL((FABS(Min(n0_is_at_mid_bps)) + FABS(Max(n0_is_at_mid_bps))) / 20)
)

[johnnyjohn]

Bump - if anyone can help with the expression below. When I render the second parameter into a KPI it shows me the number correctly - but it doesn't work when I use the number in the CLASS to define the width of the buckets.  

=CLASS(
n0_is_at_mid_bps,
CEIL((FABS(Min(n0_is_at_mid_bps)) + FABS(Max(n0_is_at_mid_bps))) / 20)
)

[johnnyjohn]

=CLASS(
n0_is_at_mid_bps,
CEIL((FABS(Min(n0_is_at_mid_bps)) + FABS(Max(n0_is_at_mid_bps))) / 20)
)