How to Prevent Metric Duplication and Incorrect Aggregation in QuickSight

Question & Answer
, , ,
1

I have a long-format dataset in Amazon QuickSight, where each column represents a different metric_name for a given year_month and field_name.

The metric_name field includes:
‘total_count’ → Total number of records
‘null_count’ → Count of missing (null) values
‘distinct_count’ → Unique value count

I needed to dynamically calculate a new metric based on these values using this calculated field eg:

sumIf(row_count, metric_name = ‘null_count’) / sumIf(row_count, metric_name = ‘total_count’) * 100

Issues Faced:

  1. Duplication in Pivot Tables:
    The new calculated metric appears under every metric category, even when it should only be relevant for certain metrics.

  2. Blank Values in Standard Tables:
    When I try to filter or adjust the display, some fields show blank values instead of the expected numbers.

2

Response will be highly appreciated

3

Hi @Scriptlord and welcome to the QuickSight community!
One quick note on not receiving a response, when you add an additional comment to one of your posts, it removes the overall post from the ‘New’ and ‘Unanswered’ lists, which often leads to scenarios like yours where questions go unanswered. If you need to add additional notes or information, make sure to edit your original post so that it remains in the unanswered section of the community.

In regards to your situation, if still encountering the same issues, could you please include some screenshots of your visual to show the behavior your receiving from the calculated field?

4

Hi @Scriptlord,
Following up here to see if you’re still facing the same issue as your original post or were you able to find a work around?

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

Thank you!

5

Hi @Scriptlord,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!