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

[DeepikaChoda]

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 ?

[Deep]

Hello Deepika,

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

Hope this helps.

Cheers,
Deep

[DeepikaChoda]

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.

[Deep]

Hello Deepika,

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

Conditional formatting per row - highest value

Is there a way to use conditional formatting to show me which value of the column is the highest? ROWS: list of brands COLUMNS: list of countries VALUE: revenue

Conditional formatting in QuickSight for rows in pivot table visualization

Hello all, Could be nice to implement a function that allows the user to highlight a date that is in a pivot table using conditional formatting. For example, if I have a pivot table for project tracking control with due dates, I would like to be able to highlight when the project is overdue using a function like =Today() and then compare the due date of my project with the today’s date, so if I have a pivot with 100 due dates, I would like to see in an easy way which ones are ok, which ones pa…

Hope this could be useful.

Cheers,
Deep

[DeepikaChoda]

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.

[DeepikaChoda]

{Change} = {after} - {before}

[duncan]

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.

[DeepikaChoda]

Hello @duncan ,

I have not yet found a solution for this issue.

[duncan]

Hello @DeepikaChoda !

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

ifelse(
Segment = 'SMB', 1, 
Segment = 'Enterprise', 2,
Segment = 'Strategic', 3, 
NULL
)

Calc2:

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

Result as a table:

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!

[DeepikaChoda]

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.

[duncan]

Hello @DeepikaChoda !

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

[DeepikaChoda]

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

[duncan]

Hey @DeepikaChoda !

Can you try the following calculated fields instead?

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

calc1 =

ifelse(
name = 'weight', 1,
name = 'alcohol per week', 2,
name = 'waist', 3,
etc...,
)

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

Good Or Bad =

ifelse(
{calc2} = 1 AND {change} >= 0, 
'BAD', 
etc....)

Let me know if this throws a similar error

[duncan]

Hello @DeepikaChoda !

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

[DeepikaChoda]

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 ?