I am using a pivot table to display data and conditional formatting to represent data types (e.g., A, B, C, D) with colors. Since conditional formatting in my tool doesn’t work directly with strings, I’ve converted the data types to numbers (1, 2, 3, 4) and applied color formatting based on these numbers.
However, I encounter issues when collapsing rows in the pivot table:
When using MAX or MIN aggregation, the collapsed rows incorrectly display the color corresponding to the maximum or minimum data type number, respectively, even if the collapsed group contains multiple data types.
My goal is to display collapsed rows with no color when they contain more than one data type.
I attempted to use AVERAGE aggregation as a workaround, but even when all the underlying data types within a collapsed group are the same, no color is displayed. I don’t understand why AVERAGE isn’t working as expected in this case."
Hello @reiokabe hope this message finds you well!!
iIf I understand well your problem, you can use a custom calculation to determine whether a collapsed group contains more than one data type. Here is my suggestion:
Create a calculated field to identify multiple data types:
You can create a calculated field that checks if there is more than one data type in a group. For instance, you can use a distinct count function to verify this:
This calculated field will return ‘Multiple’ if the group contains more than one data type, and ‘Single’ otherwise.
Apply conditional formatting based on the calculated field:
Next, apply conditional formatting in the pivot table based on the value of the calculated field. If the value is ‘Multiple’, you can set the colour to none (or a neutral colour). If the value is ‘Single’, apply the colour corresponding to the data type.
Configure the pivot table:
Ensure the pivot table is set up to use the calculated field for determining the colour. This can be done in the conditional formatting section of QuickSight.
This approach should ensure that collapsed rows display no colour when they contain multiple data types, while still applying the correct colour when all underlying data types are the same.
@lary_andr Thank you. I understand the logic of ifelse(countDistinct({DataType}) > 1, 'Multiple', 'Single') to identify groups with multiple data types.
However, QuickSight’s conditional formatting requires numerical data, so I can’t directly use ‘Multiple’ or ‘Single’ to set the formatting. I still need a way to numerically represent the ‘Multiple’ state for color-coding.
Hi @reiokabe,
To my knowledge, there’s no way to setup different conditional formatting options for the expanded view and minimized view as you can’t assign different aggregation types based on the scenario.
I will mark this as a feature request to promote visibility to the AWS support team.
Let us know if you have any additional questions or comments to add, if we do not hear back within the next 3 business days.
Hi @reiokabe,
Since we have not heard back, I’ll go ahead and 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.
