I want to do the following:
I have a clustered bar combo chart and show the median of a value as bars. The values have a threshold, which is a calculated field. This threshold is displayed as a line in the visual.
Now the users want the bars in green or red, depending on if the median of the value for the selected period is above or below the threshold. I initially planned to use an ifelse and group the columns by that, but this doesn’t give me the result I want as the values are split up in two groups for each x value, which doesn’t make sense.
So how can I colour the bars based on a threshold?
Currently, Amazon QuickSight does not support conditional formatting for bar charts to color bars based on threshold conditions. Conditional formatting is only available for specific visual types including gauge charts, KPIs, pivot tables, and tables.
However, there is a workaround solution you can implement. You can create separate calculated fields for each color condition you want to display. For example:
Create a “Green” calculated field using ifelse logic that returns the value when above threshold, otherwise returns 0
Create a “Red” calculated field using ifelse logic that returns the value when below threshold, otherwise returns 0
Then add these calculated fields to your visualization and select a stacked bar chart format.
You would hide the legends and manually assign the appropriate colors to each calculated field.
This approach effectively creates the visual appearance of bars changing color based on threshold conditions.
Another approach mentioned in the community involves creating a calculated column named “Threshold” that defines different segments of colors, then bringing that field into the Group/Color section of the field well.
You can then manually assign specific colors to each segment in the chart settings.
Please find link to different documentations 12345
thank you so much for your very helpful answer. The first approach almost fits our needs but in the Vertical Stacked Bar Chart, I’m not able to set our threshold line like in the clustered bar combo chart. What does it mean to create a calculated column? I would really like to use the Group/Color function but thats not possible with the median()-aggregated values..
Creating a calculated column in Amazon QuickSight means defining a new field using calculation formulas that transform or combine existing data fields.For the Group/Color function approach, you would create a calculated column named “Threshold” that defines different segments of colors based on your conditions. Once created, you would bring this field into the Group/Color section of the field well and manually assign specific colors to each segment in the chart settings.
While working with median()-aggregated values presents challenges, you can use LAC-A functions to specify the level at which to group your median computation. For example, you could use a formula like median({YourValue}, [DimensionField]) to calculate the median at a specific dimension level.