4 Replies Latest reply on Sep 6, 2016 5:14 AM by Prasanna Selvaraju

Create BINS Based on SUM(Measure) value - Group by BINS on Sales Amount

Hi Tableau guys.

Good day !
I am trying to groups the sales on shop who's sales SUM value is belonging to certain range and bucket it.S

Like :

#      SALES_ID   SALE                AMOUNT

1      1                  Direct               200

2      1                  Direct               459

3      1                  Direct               600

4      2                  Tele                 300

5      2                  Tele                 2000

6      1                  Tele                 1500

7      5                  TeleGG           300

8      3                  DirectGG         459

9      4                  DirectX             600

Just want to group by based SUM OF AMOUNT

IF SUM(AMOUNT)  , GROUP BY SALE   , BIN it under  " < 1000 " bucket

IF SUM(AMOUNT)  , GROUP BY SALE   , BIN it under  " > 1000  and < 2500 " bucket

IF SUM(AMOUNT)  , GROUP BY SALE   , BIN it under  " >2500 and < 3500 " bucket

So Result like :

@ <1000 BIN =  3 Sales happened

@ > 1000  and < 2500 Bin = 3 Sales happened

@ > 2500  and < 3500 Bin = 2 Sales happened

@ > 3500 Bin = 1 Sales happened

• 1. Re: Based on SUM(Measure) value create BINS

hi Prasanna,

So I've come up with a formula, based on your question, but I don't get the same results as you have listed...this could well be my understanding of the exact requirement. However the general logic/syntax of the formula, will (hopefully) mean you can adapt it to make it do what you want.

So from your question, I've assumed you want to SUM the AMOUNT for each sale group. We can do this several way, but probably LoD is the easiest (as it will return the correct Bin regardless of the level of detail in you viz.

So to get the SUM of AMOUNT grouped by SALE group...

{FIXED [Sale]: SUM([Amount])}

We can then use this in our IF, THEN, ELSE statement to put things in the right groups.

IF {FIXED [Sale]: SUM([Amount])}   < 1000 THEN "< 1000"

ELSEIF {FIXED [Sale]: SUM([Amount])}   < 2500 THEN "> 1000  and < 2500"

ELSEIF {FIXED [Sale]: SUM([Amount])}   < 3500 THEN ">2500 and < 3500"

ELSE ">3500"

END

Hope this makes sense, and you are able to adapt it to get the exact result you need (the 'check' tab shows what's going on with this formula)

2 of 2 people found this helpful
• 2. Re: Based on SUM(Measure) value create BINS

This was perfect starting point for me.

In Addition I found , if we apply month and Year Filter , These numbers were going crazy consider if we have a date column input.

Like at year : 2014 , JAN = What is sales Amount COUNT ?

On this case , we just need to specify  those many dimension which we wanted to have slicing of data in Quick filters.

IF {FIXED [Sale],[YEAR],[MONTH]: SUM([Amount])}   < 1000 THEN "< 1000"

ELSEIF {FIXED [Sale]],[YEAR],[MONTH]:: SUM([Amount])}   < 2500 THEN "> 1000  and < 2500"

ELSEIF {FIXED [Sale]],[YEAR],[MONTH]:: SUM([Amount])}   < 3500 THEN ">2500 and < 3500"

ELSE ">3500"

END

Overall I am happy to know how this FIXED  function works  and how can need to accommodate if we have multiples dimension to be fixed .

Good Job  Simon Runc

Thank you very much

• 3. Re: Based on SUM(Measure) value create BINS

hi Prasanna,

Excellent news...and you raise a very good point (which I should have mentioned!!)....

FIXED LoD calculations are just that...the Level of Detail (as defined in the LoD formula) is fixed, and are not affected by filters (unless the filters are made 'In Context' or above, due to the order of operations within Tableau), or the vizLoD (except in certain circumstances) As such you need to add any dimensions, that you want to filter by or have in the VizLoD, to the FIXED LoD calculation, as you have done.

I've written up my thinking on the calculation types in Tableau and how to think about them here Answer - Quora

which you might find useful.

• 4. Re: Based on SUM(Measure) value create BINS

You are too good man !  I Really like this answer.

Thanks a ton !