In this example, I pose a question, “How do I create a conditional group and display the results on a table in Tableau?” and walk you through my thought process of how to solve it. After reading this, I hope to give you a glance into my problem solving methods and that you learn something new in Tableau.
Let’s say I have a table of data I’ve created in Tableau Desktop. In this example, the table is sourced from Tableau’s infamous “Sample – Superstore” data set. The table contains 1 dimension and 1 measure. The dimension, which I’ve dragged onto my Rows shelf, represents all Products in my data set. The measure, which I’ve dragged onto my Columns shelf, represents the sum of Sales each across each Product.
What I’d like to do, to better analyze this data, is to see which Products have the greatest amount of Sales. I right click my blue dimension pill for Product on the Rows shelf, then Sort – Descending – by the sum of the field Sales. Now I can see which Products have generated the most Sales across the entire data set.
With my new ability to see the distribution of product performance across Sales, I also notice there are many Products listed on this table that have Sales amounts that are dwarfed by a small handful of top performing Products. I don’t want to roll up my Products to the next level of the hierarchy – Sub-Category, as shown in the Dimensions list under the Product hierarchy – because I prefer having a granular view of my data by Product. However, I would like to bucket certain products into a group based on whether they exceed a certain threshold I select for Sales. This is what I mean by “conditionally grouping” the members of a dimension presented in the table based on a metric.
How do I begin testing this out? Where do I begin? What feature of Tableau will help me solve my problem?
Since I know I want to do something related to grouping, I immediately think about Tableau’s out-of-the-box ability to group dimensions. I right click my dimension – Product – and go to the “create – group” option. In this window, I have the ability to manually create groups. I see I have the ability to create an “Other” group, but nowhere does it allow me to dynamically control how certain members within the Product dimension will be grouped into “Other” based on a specific metric. Rats.
What do I do now? I suppose I should now tackle this from the other side of my question – through the use of parameters to set the metric that will be used to control how the members of the Product dimension will be grouped. I create a parameter (Sales Threshold) to control the quantity of Sales that needs to be met in order for a Product to either be listed out on the table as is, or be bucketed into the group.
Then I create an IF statement based off this parameter that tells Tableau to use the data source’s product name if the sum of sales for a given product is greater than or equal to the Sales Threshold parameter I just created.
When I drag it onto the chart, it seems to work, as I’ve set the Sales Threshold at $10,000 so any product with less than that amount will be conditionally grouped into “Other”.
Here’s where I run into a problem with this particular method. I now want to view the data as a table, instead of a bar chart, so I go to Show Me and select the text table option. However, when I remove my Product Name field from the rows shelf or move my Product Name field to the Details mark, you can see that Tableau wants to treat each Product Name in the Other group as an individual Product, rather than aggregating the Other group as a whole…
No matter what I do or try with this method, nothing seems to work. I simply want the “Other” products to show the sum of sales across every product grouped into it. UGH.
So Tableau’s group function doesn’t work, and although I can get close using the parameter+IF statement function, I can’t quite get Tableau to sing to my tune.
What about Sets? Sets are the elusive and misunderstood function that most of us overlook in our day-to-day Tableau development, but Sets are a powerful tool, especially for what I’m about to show you.
Revisiting my original question – “How do I create a conditional group and display the results on a table in Tableau?” – I’m basically looking to see what products fall within a certain sales threshold, while also identifying those that fall outside of the sales threshold. Sets are designed to do just that – show you what’s IN and what’s OUT.
So, I remove my Product Threshold calculated field, right click on my Product Name in the dimension shelf and create a Set. I rename it to “Product Set” (for ease of understanding on the chart), include all values, and create a condition where the Set will include all products with a sum of sales greater than or equal to 10,000.
I take a deep breath and place it next to my Product Name. It’s doing what I want it to do!
Now, in order to rename the Products (that fall below the $10k threshold I manually set in the Set logic) to “Other” I go back to my IF statement. I know that Sets act as Boolean fields, where there is a binary outcome of T/F, so I use the following logic…
Here’s where I ran into another brick wall. How do I conditionally control the threshold using Sets? For this, I do what any desperate Tableau developer would do and reached out to Twitter late at night. I quickly received a great tip from Brian Prestige (who I hope isn’t on Mountain Time like I am…) upon waking up the next day that sparked an idea in my mind.
Instead of creating a calculated field I could set in the “By field” section of the Condition tab, all that was needed was a bit of custom logic in the Set logic’s “By formula” section. I already created my parameter “Sales Threshold” so why not use it here?
It turns out that was EXACTLY what I needed and saved me an additional calculation. I often overlook the “By formula” section in the Condition tab since the “By field” section usually can give me what I need. You can see from the image below that the “Other” product group is perfectly summing up sales based on the Sales Threshold set in the parameter (circled in red in the 2 examples). I now have a view that allows me to look at the sum of sales, at the product level, and conditionally group certain products into an “Other” bucket based on a end-user defined value.
Thanks for reading!
This post was intended to:
- Remind you to break from your usual Tableau habits and remember the solution to your problem may have been starting at you the entire time
- Appreciate the tedious process of problem solving and always keep asking questions
- Ask for help when you’re stumped – there’s always a friendly person out there waiting to help
- Teach you about the wonders of Sets!
- Spark an idea in your mind as to how you could apply this to your own Tableau dashboards
Rapid fire solution to Conditional Grouping in Tableau:
- Create a float or integer parameter based on a measure
- Create a set including all members of a dimension
- Create a condition within the set that says the sum([measure])>=[parameter]
- Create an IF statement to show the dimension names when it satisfies the Set logic, else it will show something like “Other”
- Drag your newly created IF statement to rows and your measure to columns, show your parameter control in the view, and you’re done!
And finally, the dashboard for you to play with…