Imagine this. You’ve got more than one grouping level, and the lowest grouping level has a calculated column. You’d like to create a conditional formatting rule (for the highest level grouping) which is based on two columns observed in the lower grouping level. Consider the following example:
Let’s say you want to make [Product Type] values appear with a red font if there is any [Product Family] with an “&” in it which has a corresponding [Sum of Revenue] >= $50M.
There are a couple ways to do this, but we’ll go through the simplest way here in this post:
- Create an indicator calculation column (under the [Product Type] grouping) using:
CountIf( Contains([Product Family], “&”) AND [Sum of Revenue] >= 50000000 )
This formula will count each row within the grouping where both conditions are satisfied.
- Rename and/or hide the indicator column.
- Click the paintbrush on the left-hand side of the page, then click “Conditional formatting” followed by “+ Add rule”. Then, set a “custom formula” = [Indicator] > 0 for the conditional “formatting rule”. To find the “custom formula” setting, scroll to the bottom of the “formatting rule” dropdown menu.