How to color the values of the table based on the condition on rows and columns

HI Team ,
I have a pivot table as shown below. I want to color the column based on the “name” and "change " for individual condition.
For example , for weight if change>0 then I want it to red,
if (name = “alcohol per week” and change >0 then to Red,
if(name="fruits per week " and change >0 then to green and so on based on the impact it shows.
How can I Achieve this ?

Hello Deepika,

You could use conditional formatting - For more info see -Conditional formatting on visual types in QuickSight - Amazon QuickSight

Hope this helps.


1 Like

Thank you @Deep , I have tried that but couldn’t get the desired result . We need a condition with the combination row and column values to get my required result.

Hello Deepika,

I am not sure how helpful it is but you could refer this couple os post to get some insight/ideas

Hope this could be useful.


1 Like

Actually in this case I am getting an error of mismatched aggregation while creating the calculated field. My calculation is as below
ifelse({name}=“weight” and {change} >=0 , bad,
{name}=“alcohol per week” and {change} >=0 , bad,
{name}=“waist” and {change} >=0 , bad,
{name}=“fruits per week” and {change} >=0 , good,
{name}=“vegetables per week” and {change} >=0 , good,
{name}=“junk foods per week” and {change} >=0 , bad,
{name}=“physical workout” and {change} >=0 , good,
{name}=“water per week” and {change} >=0 , good, null)
so that I can make all “good” to green color and "bad " to red color.

{Change} = {after} - {before}

Hello @DeepikaChoda and @Deep !

Have you been able to find a solution for this or are you still working on this issue?

The mismatch aggregation error happens because you are pulling an aggregated field and a non aggregated field from the same row. What this means is you could try nesting your conditional statements to workaround this issue.

It has been some time since there has been activity on this thread. If there is no activity in the next three business days this topic will be archived.

1 Like

Hello @duncan ,

I have not yet found a solution for this issue.

Hello @DeepikaChoda !

Here is an example of how I was able to workaround the mismatch aggregation error:
diff calc: sum(Sales) - sum(Profit)

Segment = 'SMB', 1, 
Segment = 'Enterprise', 2,
Segment = 'Strategic', 3, 


sum(Calc1)= 1 AND margin <= 50, 'bad',
sum(Calc1)= 2 AND margin <= 100, 'bad',
sum(Calc1)= 3 AND margin <= 60, 'bad',

Result as a table:
Screenshot 2024-04-04 094643

The numbers are made up so it looks a little weird but the main point is to show the idea. You basically need to create another aggregation. For a table visual I recommend using a row_id or other similar attribute to order the table so that calc1 doesn’t roll up. You can just hide the row in the table formatting options.

Let me know if this helps!


Hi @duncan , This works on sum function, but when it comes to average function we are getting different. Here in my calculation Change= Before Average - After Average
when i tried to implement your logic I am facing two problems ,

  1. Getting an error of "Custom aggregation fields cannot be as a Dimension.
  2. when I place the “Calc 2” as a measure , I got only "good " (i.e., the else argument) regardless of calculation formula.

Hello @DeepikaChoda !

Thank you for following up, can you share your calculated field?

1 Like

Hi @duncan , My calculations are
baseline and completion are the normal numeric value fields and Name is String field.
Before Average = avg(baseline) , After Average = avg(completion) ,
Change= Before Average - After Average


Hey @DeepikaChoda !

Can you try the following calculated fields instead?

change =
avgOver({baseline}, [{name}], PRE_AGG) - avgOver({completion}, [{name}], PRE_AGG)

calc1 =

name = 'weight', 1,
name = 'alcohol per week', 2,
name = 'waist', 3,

calc2 = maxOver({calc1}, [{name}], PRE_AGG)

Good Or Bad =

{calc2} = 1 AND {change} >= 0, 

Let me know if this throws a similar error

Hello @DeepikaChoda !

Were you able to test the calculations above, and if so did they work or did you run into any errors?

Hi @duncan , Thank you for following up

I have tried your formula and I am getting error on (change) calculation , where I have used avgif and sumif instead of avgover because i need to add a condition on top of it.

In my data set we have a field called metric type to describe the type of the metric like (baseline , completion) . So I have used avgif and sumif function for this.

Following are the formulae That I have used to build my analysis,

  1. Baseline sum = sumIf ({metric value},{metric type}=‘baseline’)
  2. Baseline average = {Baseline sum}/{Graduation Count}
  3. Graduation Count = distinct_countIf ({id},{metric type}=‘completion’)
  4. Graduation Average = avgIf ({metric value},{metric type}=‘completion’)
  5. Change = Graduation Average - Baseline average
  6. Change % = (Change/{Baseline Average })
    Can you suggest the calculation based on these ?