I need to alter the background colour of certain rows of a table depending on the value. I know this can be done as it is displayed in a different quicksight which i don’t have access to however I am using the same dataset. I have 5 rows which all have different ranges, for example row one should be red if the value is over 400, amber if it is between 150-400 and green if under 150. However row 2 will have different ranges.
Hi @PaulV_7 ,
welcome to the QS Community!
Let me know if I got it correctly: you need conditional formatting on entire rows of a table, but you want different formatting colours and conditions for each row?
With conditional formatting you can format an entire row in this way:
If you try to create another formatting condition with “entire row” selected though, you’ll see that you cannot have more than one in the same table.
A work-around can be this one: create a calculated field, with a switch or ifelse function, where you can define a set of conditions much more complicated. Then you can do conditional formatting based on the value you calculated, and you can also hide the field you are using for determining the color of the row.
E.g. ifelse(Sales < 10000, "RED", Sales >= 10000, "ORANGE", Discount > 0.5, "PURPLE", Discount >= 0.2 AND Discount <= 0.5, "BLUE", "YELLOW")
Obviously, the first condition which is true is the one used, so you have to build the calculated field in the correct way.
But then you’ll have your color value and you can format using it.
Andrea
Hey @andrepgn! Thanks for the reply.
The issue is that my rows require different ranges for each row. I aim to get something like below, as you can see the red of the bottom row is lower than the green of 2nd row.
Hi @PaulV_7 , that’s clear 
What I suggest is move the “color decision” in a calculated field, then apply the color based on the new field.
E.g. (I just invented the thresholds, let’s focus on the idea)
I’ve these data:
Rows,DPMO,Throughput,Jam Freq
AFE,223.35,7159.035,1599
INBOUND,401.46,2986.576,1199
PACK,357.55,1398.388,500
RSP,426.9,4759.858,2032
SHIP,220.64,7029.589,1511
For what I’ve understood, your thresholds changes based on the value of field Rows and value of DPMO, so let’s create a calculated field similar to this:
ifelse(
Rows='AFE' AND DPMO < 200, 'GREEN',
Rows='AFE' AND DPMO >= 200 AND DPMO < 500, 'ORANGE',
Rows='AFE' AND DPMO >= 500, 'RED',
Rows='INBOUND' AND DPMO < 500, 'GREEN',
Rows='INBOUND' AND DPMO >= 500 AND DPMO < 1000, 'ORANGE',
Rows='INBOUND' AND DPMO >= 1000, 'RED',
Rows='PACK' AND DPMO < 400, 'GREEN',
Rows='PACK' AND DPMO >= 400 AND DPMO < 800, 'ORANGE',
Rows='PACK' AND DPMO >= 800, 'RED',
Rows='RSP' AND DPMO < 200, 'GREEN',
Rows='RSP' AND DPMO >= 200 AND DPMO < 500, 'ORANGE',
Rows='RSP' AND DPMO >= 500, 'RED',
Rows='SHIP' AND DPMO < 50, 'GREEN',
Rows='SHIP' AND DPMO >= 50 AND DPMO < 150, 'ORANGE',
Rows='SHIP' AND DPMO >= 150, 'RED',
'GREY'
)
As you see, in this way I can define a different threshold for different combinations of values.
Now I can create conditional formatting in my table based on the value of my new field (let’s say I’ve called it MyColor):
My table will result in something like this:
If I want, I can also hide the MyColor column, to obtain the table you provided as example:
If you want to color just the DPMO cell and not all the row, configure the conditional formatting in this way (notice I’ve selected Column = DPMO instead of Column = [Entire Row]):
Andrea
Hey @andrepgn, thanks for the help! I had very similar code to what you have suggested there but was unable to complete due to ‘Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.’ I am not sure how to get around this despite looking through other posts. Can you advise?
Paul
This error occurs when you’re trying to mix aggregated and non-aggregated fields in the same calculated field formula.
Like trying to mix things in this way:SUM(revenue) > cost.
So that’s a formula issue. Do you want to past the formula here and explain better how the dataset is?
Andrea
1 Like
That’s what i’ve got from reading up however i’m struggling to find a way around it.
The ‘Rows’ column is just generated from ‘area’ in the dataset.
DPMO column is a value from DPMO_availibility_JAM (Custom), this just provides the value.
Is there anything else I can provide to aid you? Just to also add when i’m trying to create a calculated field it’s a custom aggregation field and not a dimension.
Paul
Can you share the dataset fields list and the calculated field you created?
Hi Andrea. Here is a screenshot of my page. I have created the calculated field ‘DPMO_availability_colour’ however not saved anything into it with the errors discussed.
Thanks again,
Paul
Hi @PaulV_7, I see that DPMO is indicated as a standard number field. Have you done any other calculation in DataPrep directly on the DataSet?
I’m trying to understand why it’s indicated as Custom Aggregation.
Can you also share the formula for the Colour field?