9 Replies Latest reply on Oct 30, 2014 4:27 AM by Jonathan Drummey

    Binning a measure isn't working right...

    ann.stolzman

      So, I have this dataset (it's live, and I can't extract it), and I've built a workbook very similar to what is in the attached workbook.   what I'm trying to do should be achievable via binning, but it's a little more complicated than that.

       

      What I want is to change the slider labeled SUM(Sales) into a dropdown, where you can select a value, and everything over that value should appear.  I can do this via binning with the superstore data, but can't with my data extract.  When I bin my measure the minimum value and the maximum value appears as the same number (well over $20M), which isn't even a value that comes up when I look at individual items (but it could be a sum of all values). 

       

      Does anyone have any ideas how I can get binning to work the right way, or have an alternate way to create this as a parameter?  Ideally, I'd like the values to be in increments of 250K up to 1M, and then by 1M increments until I reach 5M in my live dataset.

       

      Thanks in advance!

      -Ann-

        • 1. Re: Binning a measure isn't working right...
          Steve Mayer

          Since your end goal is to have variable size bins, I recommend going with an approach that uses Parameters and Calculated Fields.

           

          In the attached workbook, I created a Parameter called "Minimum Sales", and added the values that you outlined (250K, 500K, 750K, 1M, 2M, 3M, 4M, 5M). Then I created a Calculated Field called "Include Sales" as follows:

           

          WINDOW_SUM(SUM([Sales])) > [Minimum Sales]

           

          This is a table calculation that will SUM all Sales in each window of the partition specified in the Table Calculation. I used Sales by Continent in the Superstore data as an example, since the amounts matched your 250K - 5M range. I added the "Include Sales" table calculation to the Filters shelf and (IMPORTANT STEP), set the partition to Continent, so that the table calculation sums up Sales for each Continent.

           

          Show the Parameter control, and you can select Minimum Sales and see the data filter correctly. I added the "Include Sales" calculated field to the Viz for clarity, but it just needs to be on the Filters shelf (not in the Viz). I hope this is what you were after.

           

          Capture.PNG

          • 2. Re: Binning a measure isn't working right...
            Jonathan Drummey

            @Steve - I'm glad to see more people helping out on the forums! I took a look at your workbook and I don't think it's doing what you expect it's doing. The table calculation has a Compute Using of Table (Across) so it's partitioning on each Continent, therefore it's returning the same result as SUM([Sales]) for each continent. The parameter is working fine, you can just remove the WINDOW_SUM() part.

             

            @Ann - When you bin a measure using Tableau's built-in Bins, you're binning the record-level values of the measure. You're wanting to bin at an aggregate level, which leads to the kind of solution that Steve proposed.

             

            Jonathan

            • 3. Re: Binning a measure isn't working right...
              Steve Mayer

              Jonathon - I started out trying SUM([Sales]) > [Minimum Sales], but Tableau did not allow using that calculated field as a filter (hence the addition of WINDOW_SUM). Is there a trick to being able to use just the aggregate without the windowing function? Just for clarity, I'm trying:

               

              // Tableau doesn't seem to want allow this to be dragged on to the Filters shelf

              SUM([Sales]) > [Minimum Sales]

               

              vs.

               

              // Allowed

              WINDOW_SUM(SUM([Sales])) > [Minimum Sales]

               

              Perhaps you can shed some light on why the different behavior.

               

              -Steve

              • 4. Re: Binning a measure isn't working right...
                Matt Lutton

                Hi Steve:

                 

                Try using a conditional statement that results in two distinct options:

                 

                IF SUM([Sales]) > [Minimum Sales] then 1 else 0 end

                 

                We cannot filter on DISCRETE aggregates in Tableau, but we can filter on CONTINUOUS aggregates.  Make the field/measure a Continuous (Green pill) measure, and you can filter for the 1 values.

                 

                Tableau's KB article for this methodology is below, although they use String values in the calc:

                Adding Calculated Field to Filter Shelf | Tableau Software

                 

                I hope this helps!

                • 5. Re: Binning a measure isn't working right...
                  Jonathan Drummey

                  What Matthew said. Thanks, Matthew!

                  • 6. Re: Binning a measure isn't working right...
                    Jonathan Drummey

                    PS: Here's an Idea for Tableau to support discrete measures on the Filters Shelf: http://community.tableau.com/ideas/2190

                    • 7. Re: Binning a measure isn't working right...
                      Steve Mayer

                      Thanks Matthew for the very clear explanation & Jonathon for providing the initial review & insights.

                       

                      @Ann - I put together a revised workbook with the suggestions from Matthew & Jonathon. Their approach is simpler & more straightforward. There are always many ways to do things in Tableau, and it is a journey learning the most elegant solutions.

                       

                      The calculated field is now a Continuous measure & can be used on the filter shelf, with Range set to "At Least" 1.

                       

                      IIF(SUM([Sales]) > [Minimum Sales], 1, 0)

                       

                      Workbook attached.

                      • 8. Re: Binning a measure isn't working right...
                        ann.stolzman

                        Thanks so much Steve, and Jonathan, and Matthew!  This thread has been really helpful, and Steve's final solution is precisely what I needed! 

                         

                        I appreciate you all so much, and all the help you give on these forums.  I'm still trying to get to a confidence level where I can start helping others over here, until then, I read and experiment, and work and learn.

                         

                        -A-

                        • 9. Re: Binning a measure isn't working right...
                          Jonathan Drummey

                          Hi Ann, we're glad to help, and I'm glad that you'd like to contribute as

                          well! The variety of forum questions is a firehose that none of us can

                          totally take in, we have to specialize to some degree. Therefore one

                          suggestion is to pick an area to focus on (certain chart types, dashboards,

                          filter actions, calculated fields, Sets, table calcs, data blending, server

                          admin, JS API, etc.) and build some mastery in.

                           

                          Jonathan

                           

                           

                          On Mon, Oct 27, 2014 at 6:25 PM, ann.stolzman <