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

Binning a measure isn't working right...

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.

-Ann-

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

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.

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

@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...

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...

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...

What Matthew said. Thanks, Matthew!

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

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...

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...

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...

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 <