Create Buckets in Expression

[mrthomasshelby]

Hello, I have a dataset that is as follows:

Vendor       Sales    PO#

A              1300        1

B              2200        2

A              1400        1

B              2100        3

C              2500        4

A              300          2

C              900          4

D              3200        5

B              2000        3

D              1500        5

E              1100        6

F              3500        7

E              1500        8

G              1400        9

H              600          10

H              900          10

G             1200         11

Now I'd like to create two Buckets based on Sum(Sales): one by Grouped by vendor and the other Grouped by PO#. The buckets should be as follows:

<5k,5k-50k,50k-100k,100k-250k,250k-500k,500k-1Mn. I tried creating a dimension like this:

Aggr(If(Sum([Sales]) > 0 and Sum([Sales]) < 5000, Dual('5k or Below',1),

    If(Sum([Sales]) < 50000,Dual('5k-50k',2),

        If(Sum([Sales]) < 100000, '50k-100k',Dual('50k-100k',3),

        If(Sum([Sales]) < 250000,'100k-250k',Dual('100k-250k',4),

        If(Sum([Sales]) < 500000, '250k-500k',Dual('250k-500k',5),

        Dual('500k-1Mn',6)))))),[Vendor]) for Vendor Buckets

and

Aggr(If(Sum([Sales]) > 0 and Sum([Sales]) < 5000, Dual('5k or Below',1),

    If(Sum([Sales]) < 50000,Dual('5k-50k',2),

        If(Sum([Sales]) < 100000, '50k-100k',Dual('50k-100k',3),

        If(Sum([Sales]) < 250000,'100k-250k',Dual('100k-250k',4),

        If(Sum([Sales]) < 500000, '250k-500k',Dual('250k-500k',5),

        Dual('500k-1Mn',6)))))),[PO#]) for PO buckets.

But it seems to not work as only one bar with '-' as dimension is being displayed. If you could correct me or give a way to create such buckets, I'd be grateful. TIA!

stalwar1 loveisfail

[sunny_talwar]

Would you be able to share the document where you have been trying this (just to save some rework on our end)

[mrthomasshelby]

Hello, PFA the dataset.

PS: This is only a part of the entire dataset and I cannot share the entire dataset due to obvious reasons! Thanks!

[daveamz]

Hi,

Check the attach.

I strongly recommend you to try to calculate the bucket values in the script if that is possible. The aggr is a pretty heavy operation (depending of your full data set, of course)

Regards,

David

[sunny_talwar · Accepted solution]

You have some typos in your calculated dimension.... try this

Aggr(If(Sum([Sales]) < 5000, Dual('5k or Below',1),

    If(Sum([Sales]) < 50000, Dual('5k-50k',2),

    If(Sum([Sales]) < 100000, Dual('50k-100k',3),

    If(Sum([Sales]) < 250000, Dual('100k-250k',4),

    If(Sum([Sales]) < 500000, Dual('250k-500k',5), Dual('500k-1Mn',6)))))), [Vendor])

and

Aggr(If(Sum([Sales]) < 5000, Dual('5k or Below',1),

     If(Sum([Sales]) < 50000, Dual('5k-50k',2),

     If(Sum([Sales]) < 100000, Dual('50k-100k',3),

     If(Sum([Sales]) < 250000, Dual('100k-250k',4),

     If(Sum([Sales]) < 500000, Dual('250k-500k',5), Dual('500k-1Mn',6)))))), [PO#])

[mrthomasshelby]

Thanks! But the thing is these Buckets need to be dynamic according to the filters applied on the data. So I'm not sure if I can define them in the load script?

[mrthomasshelby]

Yes, got it! Thanks a lot again!

[mrthomasshelby]

Also, is there anyway I can use this Calculated Dimension as a filter in the filter pane option? I have tried putting in the same expression as dimension in the filter pane object but it shows no options. Thanks again!

[sunny_talwar]

Try this

Aggr(If(Sum({1}[Sales]) < 5000, Dual('5k or Below',1),

    If(Sum({1}[Sales]) < 50000, Dual('5k-50k',2),

    If(Sum({1}[Sales]) < 100000, Dual('50k-100k',3),

    If(Sum({1}[Sales]) < 250000, Dual('100k-250k',4),

    If(Sum({1}[Sales]) < 500000, Dual('250k-500k',5), Dual('500k-1Mn',6)))))), [Vendor])