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.
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.
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”
Step 2: Save “profit_threshold” as Derived Data subquery
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).
Step 3: Add “profit_threshold_subquery” Derived Data column to the Color shelf
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:
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.
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.