Hi there,
I’m trying to build a matrix visual (pivot table) in Amazon QuickSight with the following layout:
Rows: site, site_level_1
Columns: sector
Values: distinct count of checklist_nid
The goal is to ensure that each site has visibility into which sectors are covered.
However, there’s a challenge:
Some site × sector combinations are not applicable, and in those cases, no data exists at all—not even a NULL or 0. As a result, those cells are completely missing from the pivot table.
This causes a problem when trying to apply conditional formatting to visually “blank out” or highlight non-applicable intersections. Since the cells don’t exist, formatting rules can’t be applied.
We’ve tried using calculated fields to convert NULL to 0, but that doesn’t help because the rows themselves are missing—not just the values.
What’s the best way to ensure all site × sector combinations appear in the matrix, so we can apply formatting—even if the value is zero or not applicable?