4 Replies Latest reply on Jul 22, 2016 6:26 AM by madhuri.tanniru

    Equal sized bins

    madhuri.tanniru

      Hi,

       

      I am trying to build a view with 'n' number of equal sized bins.

       

      How can we have Tableau define the size of the bin automatically?

       

      Thanks,

      -Madhuri

        • 1. Re: Equal sized bins
          Stoyko Kostov

          Hi Madhuri,

           

          You can create a calculated field AutoNumBins as follows:

           

          CEILING({ EXCLUDE [Sales (bin)] : MAX ([Sales])-MIN([Sales])}/[Bucket Size])

           

          This is an LOD expression. I exclude [Sales (bin)], so that I get the absolute range of sales. If I didn't exclude it, I would get the value per bin, which is not what we want.

           

          A small catch is that if you decide to add additional dimensions to your columns, you would have to exclude them explicitly in the AutoNumBins calculation - otherwise, it will be relative to the new dimensions by default, and not absolute.

           

          Finally, replace your sales filter to reference the new calculated field: INDEX()<=MAX([AutoNumBins]).

           

          I'm attaching the workbook I produced. Let me know if you have other questions.

          1 of 1 people found this helpful
          • 2. Re: Equal sized bins
            madhuri.tanniru

            Hi Stoyko Kostov,

             

            Thanks for putting this together!

             

            You are letting Tableau define the number of bins based on the bucket size given by user.

             

            However, I am looking for the other way around. The user wants to have 5 equal sized bins after placing the measure on filter shelf without specifying bucket size via parameter.

             

            Can you help with that?

             

            Thanks,

            -Madhuri

            • 3. Re: Equal sized bins
              Stoyko Kostov

              Hi Madhuri,

               

              Sorry for my misunderstanding. Yes, I see you wanted the size of the bins computed automatically.

               

              Unfortunately, I wasn't able to do it. Technically, the size of the bins should be BinSize = (MAX(Sales)-MIN(Sales)) / NumBins, but with this calculation there are several problems:

               

              1. If you have a filter on Sales (as you do), the filter is not reflected in the calculation.

              2. If I want to create a bin dimension (as you did - right-click Sales, choose bin), I need to set the bin to a parameter, and this parameter doesn't get updated as the data changes.

              3. If I compute the bin number based on the bin size (as FLOOR(Sales/BinSize)), Tableau doesn't allow me to use it as dimension, as it contains aggregations.

               

              I'm afraid the best I can do is the following:

              1. Create a calculated field, AutoSizeBins, and set it to FLOOR((MAX([Sales])-MIN([Sales]))/([Number of Bins] - 1))

              2. Right-click on it, and choose Create Parameter.

              3. Edit Sales(bin), and set the bin size to the parameter created in 2.

               

              I know this is not elegant and has the drawbacks listed above, but it's all I could figure out.

               

              It may be satisfactory if you have no filters on Sales, or if you define the filter on the data source, and not on the sheet. See attached.

              1 of 1 people found this helpful
              • 4. Re: Equal sized bins
                madhuri.tanniru

                Hi Stoyko,

                 

                Thanks for taking time and explaining the limitations in setting up the workbook the way I want.

                Awesome explanation, Thank you very much!

                 

                -Madhuri

                1 of 1 people found this helpful