How to bin, bucket, or categorize aggregations by a dimension

Use-case:

I’m trying to show Total Profit by Region and sort Regions with the lowest profit towards the top so that they’re seen first.

04%20PM

However, I would also like to bin Total Profit in such a way where I can categorize Regions that are “Underperforming” and “On-Track” with their Total Profit to-date using an intuitive color palette (Red=Underperforming, Green=On-Track). Regions who are “Underperforming” have a Total Profit less than $100,000.

12%20PM

Step-by-Step how to bin your aggregations into a color shelf dimension

Step 1: Create the bins to categorize your metric of interest (i.e. Total Profit). In this case our bin column is called “profit_threshold”

23%20PM

Step 2: Save “profit_threshold” as Derived Data subquery

38%20PM

Profit threshold must be evaluated and saved as a Derived Data subquery since we’re evaluating the entire Dataset to properly aggregate and categorize our total profit by regions into bins (Underperforming and On-track).

08%20PM

Step 3: Add “profit_threshold_subquery” Derived Data column to the Color shelf

10%20PM

If you analyze the query running in the background, you can see that our subquery is running to calculate the “profit_threshold” for each region, and then is joined back to our primary query, which is showing “total_profit” by region.

( SELECT TA_0.`region` as `_region`, sum((TA_0.`profit`)) as `total_profit`, (case when sum((TA_0.`profit`)) < 100000 then "Underperforming" else "On-Track" end) as `profit_threshhold_subquery`
  FROM `default`.`superstore_sales` TA_0 
  GROUP BY 1 
) SELECT TA_0.`region` as `region`, T1.`profit_threshhold_subquery` as `profit_threshhold_subquery`, sum((TA_0.`profit`)) as `total_profit`
  FROM `default`.`superstore_sales` TA_0  INNER JOIN `T1` T1 ON ( TA_0.`region` = T1.`_region` ) 
  GROUP BY 1, 2
  ORDER BY 3 ASC
  LIMIT 5000

Step 4: Select the “Colors” tab on the right of the Visual builder screen, and select “Fix colors for specified values”.

Now your bins (Underperforming and On-Track) should appear as dimensions assigned to particular colors:

24%20PM

Step 5: Select each bin or category and assign it the Color of choice.

Ideally we want to show “Underperforming” region bars as red, and “On-track” regions as green.

46%20PM

Now your chart should show Total Profit by Region and also reflect the desired categorization of profit by each of your regions with color palette selections that are most intuitive.

58%20PM

2 Likes