Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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#])
Would you be able to share the document where you have been trying this (just to save some rework on our end)
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!
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
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#])
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?
Yes, got it! Thanks a lot again!
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!
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])