How to define the color of the pivot table based on the condition on colums and row

Question & Answer
, , , , ,
1

Hi Team,
I have a pivot table show below. I want to color the column based on the ‘‘Row’’ and ‘‘DPMO’’ for specific condition.
For example,
if rows = A and DPMO <50 then Green(G), 50=<DPMO<=160 then Amber(A), DPMO>160 RED(R).
if rows = B and DPMO <100 then Green(G), 100=<DPMO<=200 then Amber(A), DPMO>200 RED(R).
etc…
How can I Achieve this ?
I tried to define a calculated field to use in conditional formatting but I get this error: ''Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed"

PivotTableDpmo
PivotTableDpmo451×275 15.2 KB

2

Hi @Auro and welcome to the QuickSight community!
I think you’re on the right track with creating a calculated field for an instance like this, could you please share the calculated field you created and is giving you those error messages so that we can see what the issue might be?

3

Hi Brett,
A, B, C are defined through a calculated field where I filtered by description and grouped them under the names A, B, C. This field (let’s call it AGR_Fields) has:

  • ROLE: Dimension
  • TYPE: String

DPMO is a measure field in the dataset with:

  • ROLE: Measure

I’m trying to create a calculated field to set different DPMO thresholds for each ROW:

ifelse(contains({AGR_Fields},‘A’),ifelse({DPMO} < 55, “G”, ifelse({DPMO} > 190, “R”, “A”)))

→ The calculated field expression {{incorrectSnippet}} is incorrect. Correct the syntax and choose Create again

4

Hi @Auro,
Looking at your calculated field example, it looks like you may have to switch up the way it’s written, what if you tried this instead, let me know if it works:

ifelse(
contains({AGR_Fields},‘A’) AND ({DPMO} < 55, ‘G’,
contains({AGR_Fields},‘A’) AND ({DPMO} > 190, ‘R’,
A)

5

Hi @Auro,
Following up here as it’s been awhile since we last heard from you; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

6

Hi Brett,
Thank you for your support.
I have resolved the issue by defining three different calculated fields: two to set the Red and Amber limits for A, B, C and another one where I combine the limits with the DPMO values.
This topic can be closed.

1 Like