Cannot display 'N/A' for null values in pivot table despite custom format settings

Question & Answer
, , , , , , , , ,
1

Hi everyone,

I’m struggling with displaying ‘N/A’ for null values in my pivot table. Here’s my setup:

Problem:

  • I have a pivot table with a column ‘value%’ that contains numeric values (percentages) and null values
  • When a value is null, it means that the KPI is not applicable for that specific site_code
  • I want to display ‘N/A’ instead of blank cells for these null values

What I’ve tried:

  • I’ve set up custom formatting for the ‘value%’ field in the pivot table
  • In the formatting settings, I’ve specifically set “Format null values as: N/A”
  • I’ve verified in the dataset that these values are indeed null (see screenshot 3)
  • I’ve also tried creating a calculated field to convert values to strings with ifelse(isNull({value%}), ‘N/A’, toString({value%})), but this doesn’t work because:
    • The field must be placed in the ‘Value’ section of the pivot table
    • The ‘Value’ section only accepts numeric fields for aggregations (avg, sum, count, etc.)
    • String fields are not compatible with this requirement

Current behavior:

Has anyone encountered this issue or knows how to properly display ‘N/A’ for null values in a pivot table’s value field?

Any help would be greatly appreciated!

Best regards

2

Hi @frapaco,
This is fairly common and expected behavior; as QuickSight’s decimal and integer fields only accept values that are valid numbers, as it’s trying to perform the selected aggregation on those values, an unaccepted value would throw off the visual. (If dataset has an invalid value in an integer or decimal datatype field, the dataset will not even load completely into your analysis).

I agree it’s a a bit odd that there is a section to replace nulls with custom text but then does not show/accept it.
The best work around in this scenario would be to setup conditional formatting on that field. While there’s no option to add text, you can ‘Add icon’; when selecting an icon for your condition, there’s an option to ‘add custom unicode icon’ where you can paste in a glyph.
The system only accepts some custom glyphs so it’s a bit of trial and error to see what works best (I have not found an ‘N/A’ that get’s accepted so you may have to get creative) but here’s a website that contains a large list that you can test out.

1 Like
3

Thank you Brett, I’ve resolved creating a new Calculated Field:

ifelse(
isNull({Value %}),
2, // to use the conditional formatting
{Value %}
)

in this way I’m able to handle the ‘null’ values as numbers and then I used what you suggested me.

es. pivot values
es. pivot values847×249 11.9 KB

It’s not ‘N/A’ symbol but it works!
TY!

1 Like