Pivot Table Doesn't show all columns in dataset until rows loaded by scrolling

Q&A
, , , ,
1

We have had users complaining about a behavior of the Quick Sight pivot tables observed in our production dashboards. The dataset in question is related to customer payments and is a SPICE dataset. Records essentially have 4 relevant fields

  • Transaction Date
  • Category (Payment, Refund, Fee, Tax, etc)
  • Subcategory (CreditCard, CashOrCheck, GiftCard, etc)
  • Amount

The problem I’m observing is due to the fact that pivot tables in Quicksight do not load all rows before presenting the data. The totals are being calculated correctly, across all rows in the dataset for those subcategories among rows which have been loaded, but if there are some subcategories which are included in the dataset but not the loaded rows, they will not be represented in the pivot table and the totals of the subcategories will not add up to the totals of the category.

I’ve attached a video documenting the behavior: https://drive.google.com/file/d/1x21bBuPS5i7EyVmaRywURAktbabP8Q91/view?usp=sharing

For this video, the data is filtered to YTD 2025. In it you can see that the Payment category contains both CreditCard and CashOrCheck. The sum of CreditCard is $786,379.59 and CashOrCheck is $16,963.80. I’ve confirmed that both these amounts are correct, but they sum to $803,343.39, which the total for the category is listed as $804,810.89.

As I scroll down, I can see that at around the beginning of August, more rows are being loaded. Once they load, new Subcategory “GiftCard” appears and totals $1,467.60. When this is added to the previous sum, the total is now equal to $804,810.89 which was the total for the category.

This discrepancy has been confusing to customers who are trying to reconcile their payment history and seeing a different grand total or a lack of categories than what is really present. In the example from the video, the user might incorrectly think that they did not process any Gift card payments and the only way they would know is if they scrolled for a very long time. This is clearly a bad experience and I would like to know if there is a fix or workaround.

I am wondering if…
a) The behavior of pivot tables could be modified (optionally) to wait to load all rows and show some sort of loading indicator for large datasets. I would think it’s better to show the right thing slowly rather than the wrong thing quickly.
b) Is there any hack I could use to ensure that all subcategories are being pulled in in the initial load for this pivot table?

2

Hi @TheCardlessMoose,

Hope everything is well! I would definitely try this suggestion from a previous thread, as it not only contains documentation for best pivot table practices, but it may also lead to less computations occurring and therefore can potentially fix the loading issue. Let me know if this helps!

3

Hi @WLS-Luis,

Thank you for the comment.

The best practice documentation says “Create additional pivot tables so that each displays fewer metrics.” and “It can be better to use multiple visuals on the same dashboard, each showing a single metric”

I’m not sure this advice will work in our case since this table is meant to show a full breakdown of financial data. Splitting it into multiple visuals would somewhat defeat the purpose as this table is meant to show the full breakdown of customers’ payments into taxes, fees, etc.

To be clear, the issue is not the performance of the Totals or Subtotals feature in the pivot table, which seems to be the topic of the linked support forum discussion. Those are functioning just fine for my dataset and are correctly computing the totals in a timely manner.

The problem is that it appears (please correct me if I’m wrong) that pivot tables only pull in about 5k rows at a time and more rows are loaded upon scrolling down. While the total of the values are correctly being represented across the whole dataset, the dimensions used for the column and row breakdowns are only based on the loaded rows.

This is why in my example, the problem is not that the Totals are being computed incorrectly, but the total for dimension values which have not been loaded are not represented at all.

Please tell me if that makes sense or if you think I’m misunderstanding what is going on in any way. I’d love for the answer for our customer base not to have to be that they need to keep scrolling down until all data is loaded.

4

Hi @TheCardlessMoose,

Thanks for explaining your use case in further detail! I definitely agree with you not approaching the pivot tables in that way since that does not apply well with displaying the financial data. In terms of how many rows can be pulled, users can edit the amount in page size like this and it can go up to 10000.

If that doesn’t work, I would definitely create a support ticket so that AWS Support can look into your case further and potentially see if pivot table behavior can be modified.

5

Hi @TheCardlessMoose,

Just checking back in to see if you received my last message. Were the suggestions helpful or were you able to find a workaround in the meantime? If we do not hear back in the next 3 business days, I’ll go ahead and close out of this topic.

Thank you!

6

Hi @TheCardlessMoose,

Since we have not heard back, I’ll go ahead and close/archive this topic. However, if you have any additional questions, please feel free to post again in the Quick Suite Community and link this thread for any relevant information!

Thank you!