# Conditional Formatting Pivot Table Values - "Redacted" Text?

**URL:** <https://community.amazonquick.com/t/conditional-formatting-pivot-table-values-redacted-text/6136>\
**Category:** Q&A\
**Tags:** analysis, formatting, quick-sight\
**Created:** [September 19, 2022, 10:30pm UTC](https://community.amazonquick.com/t/conditional-formatting-pivot-table-values-redacted-text/6136 "2022-09-19T22:30:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sirwin007](https://avatars.discourse-cdn.com/v4/letter/s/35a633/32.png) [@sirwin007](https://community.amazonquick.com/u/sirwin007)\
**Post date:** [September 19, 2022, 10:30pm UTC](https://community.amazonquick.com/t/conditional-formatting-pivot-table-values-redacted-text/6136/1 "2022-09-19T22:30:06Z")

</div>

I’ve got a pivot table with 2 value columns (row\_count(sum), percent-of-total(custom))

I’ve got level aware field response\_cnt generated by the dataset of a dim\_tbl-\>left join-\>fact\_tbl  
On null row joins, this response\_cnt = 0.

On my pivot table I can do a level aware max(response\_cnt) to ensure each row has the true max (not the zero)

Now for the Value columns of Row Count/% of Total - if my max(response\_cnt) is \< 10 I need show “Redacted” instead of 5/2% for example. I don’t see any formatting that will help me here.

Another way around my issue might be to cover the row dimension value with ‘redacted’, but QS will not let me code a max( ) function on the dimension side. “CONTEXTUAL\_MISMATCHED\_AGGREGATION”

I’m stuck either way.

On the dimensions I wrote a custom function looking at the response\_cnt on the row, but the null row that value is zero - thus I need the max for the level here too.

---

<div class="post-metadata">

**Author:** ![sirwin007](https://avatars.discourse-cdn.com/v4/letter/s/35a633/32.png) [@sirwin007](https://community.amazonquick.com/u/sirwin007)\
**Post date:** [September 20, 2022, 1:03pm UTC](https://community.amazonquick.com/t/conditional-formatting-pivot-table-values-redacted-text/6136/2 "2022-09-20T13:03:35Z")

</div>

I was able to modify my function on the dataset to provide the max value over the context. Using this value I was able set a row-count value to 1, 0 or null based on I had non-null join (1 or 0) and if too few I can set to null. This would blank out the value in the 2 Value columns. Now add a conditional formatting for each of those Value columns if the calculated max (now a data value) was \< too few turn the background black. I can’t place ‘retacted’ as the text, but the effect is a blacked out box.

---

<div class="post-metadata">

**Author:** ![Max](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/max/32/2288_2.png) [@Max](https://community.amazonquick.com/u/Max)\
**Post date:** [September 26, 2022, 6:10pm UTC](https://community.amazonquick.com/t/conditional-formatting-pivot-table-values-redacted-text/6136/3 "2022-09-26T18:10:59Z")

</div>

That’s a great solution!

I might have misread this, but can you set you preferred value to Null? If you can then you can change your Null values to a text you would like.

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/d/d001fc2dab93144ddc218f4e2ce87859f5082968.png)

---

<div class="post-metadata">

**Author:** ![divey](https://avatars.discourse-cdn.com/v4/letter/d/94ad74/32.png) [@divey](https://community.amazonquick.com/u/divey)\
**Post date:** [September 26, 2022, 6:44pm UTC](https://community.amazonquick.com/t/conditional-formatting-pivot-table-values-redacted-text/6136/4 "2022-09-26T18:44:54Z")

</div>

Not sure I followed the full context of your problem but you can return text in a pivot table with a calculated field, you just have to also convert all your returned values to a string as well.

A basic example:  
ifelse(value\_to\_return \< threshold, ‘REDACTED’, toString(value\_to\_return))