Bucket wise sales

[Anonymous]

Hi 

Sales

100

50

20

30

120

200

300

Above sales data i want to convert bucket wise sales:

Bucket   Sales

0-30        20

0-30        30

31-60      50

>61        100

>61        120

>61        200

>61        300

Regards,

[Anonymous · Accepted solution]

@Anonymous 

Create a separate table (Bucket) as below.

Sales Bucket	Lower	Upper
0-30	0	30
31-60	31	60
>=61	61

Then create this calculated column in your table. 

Bucket = 
VAR X = Sales[Amount]
VAR Filtered =
    FILTER (
        'Bucket',
        VAR Y = 'Bucket'[Upper] RETURN 'Bucket'[Lower] <= X
            && IF ( ISBLANK ( y ), TRUE (), Y >= X )
    )
RETURN
    MAXX ( Filtered, Bucket[Sales Bucket] )

Appreciate with kudos.

Please mark as solution if this resolves your problem.

Thanks

[Icey · Accepted solution]

Hi @Anonymous ,

If "Sales" is a column, then you can add a calculated column in your table like so:

Bucket =
SWITCH (
    TRUE (),
    [Sales] >= 0
        && [Sales] <= 30, "0-30",
    [Sales] >= 31
        && [Sales] <= 60, "31-60",
    [Sales] >= 61, ">61"
)

Or, create a measure:

Bucket =
SWITCH (
    TRUE (),
    SUM ( 'Table'[Sales] ) >= 0
        && [Sales] <= 30, "0-30",
    SUM ( 'Table'[Sales] ) >= 31
        && [Sales] <= 60, "31-60",
    SUM ( 'Table'[Sales] ) >= 61, ">61"
)

If "Sales" is a measure, then create a measure like so:

Bucket =
SWITCH (
    TRUE (),
    [Sales] >= 0
        && [Sales] <= 30, "0-30",
    [Sales] >= 31
        && [Sales] <= 60, "31-60",
    [Sales] >= 61, ">61"
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

@Anonymous 

Create a separate table (Bucket) as below.

Sales Bucket	Lower	Upper
0-30	0	30
31-60	31	60
>=61	61

Then create this calculated column in your table. 

Bucket = 
VAR X = Sales[Amount]
VAR Filtered =
    FILTER (
        'Bucket',
        VAR Y = 'Bucket'[Upper] RETURN 'Bucket'[Lower] <= X
            && IF ( ISBLANK ( y ), TRUE (), Y >= X )
    )
RETURN
    MAXX ( Filtered, Bucket[Sales Bucket] )

Appreciate with kudos.

Please mark as solution if this resolves your problem.

Thanks

[amitchandak]

@Anonymous ,

Create a new column like

new column = switch(true()
[Sales] <=30 , "  0 -30",
[Sales] <=60 , " 30 - 60",
">61")

refer segmentation

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization