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

    Prasanna Selvaraju

      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
          Simon Runc

          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
            Prasanna Selvaraju

            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
              Simon Runc

              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)

               

              Order-of-Operations - JM.png

               

              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
                Prasanna Selvaraju

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

                 

                Thanks a ton !