Conditional formatting for whole rows in Pivot Table

Question & Answer
, , , , ,
1

Hello.
I have the following pivot table, that groups users (Column: Type) into their respective home regions (Column: Macro and EU region).

I want to highlight in red the two users with the lowest average percentage, for the four weeks shown, for each of the Macro regions.

In the case seen below, I’m trying to highlight the rows: CEU-EU13-AR & CEU-EU17-AR and UK-EU22-AR & EU23-AR.

It’s proven quite complex, specially due to the table being a Pivot, and not a normal Table. Unfortunately I need the Pivot format as I’m aggregating various other metrics which - visually- are best sorted in a Pivot.

I’ve tried:

  • The conditional formatting menu (under properties): Unfortunately this will only let me set numerical conditions which are neither variable (as the average will change as new weeks get added) nor allow for ranking-based conditions (or any other of condition based on tertiary variables).

  • Creating a new “indicator” variable: I tried handling the condition from the query side, adding a column that will make the ranking, and give a binary value [0,1] if the particular row is a top offender for the 4-week average or not. Unfortunately, I have to (visually) include this variable in my pivot in order to apply conditions on it, I cannot apply the condition directly on “Kipp Comp.”.

  • Creating a new “indicator” variable within quicksight: I also tried using LAC-A functions to create the same variable within quicksight, but I have the same issue linking the Pivot formatting to it, unless I explicitly include it.

  • Creating a parameter: Similar as before, I cannot base any sort of conditional formatting on a Parameter in Pivot Tables.

Any type of help or suggestion will be greatly appreciated!
I’m also open to trying using a Table again, if you find a way of aggregating all the “CEU” lines into a single (thicker) line, grouping all of the contents as the pivot does.

image
image665×751 65.2 KB

Thanks in advance.

2

According to this blog, Conditional formatting can only be applied to individual columns, not entire rows. I tried in my QS pivot table, there’s no option to highlight the whole row.

3

Hi @sgabarro,
Sophia is correct as this is a current limitation. I’ll mark this as a feature request though to provide visibility to our support team.

Thank you!

4

Hi @sgabarro,
While I’ve marked this as a feature request already, I wanted to follow up and confirm if you had any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

5

Hi @sgabarro,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!