How to show yearly totals (EU + NA) in a pivot table?

mosalvar · January 20, 2025, 10:35am

Hi!

I have a dataset containing data for EU and NA regions for the years 2025, 2026, and 2027. When using a pivot table, I want to display not only the grand total but also the total per year (EU + NA) within the same table/visual.

I’ve tried various options with calculated fields, but haven’t had any luck so far. Is there a way to achieve this?

Thanks in advance!

David_Wong · January 21, 2025, 5:40am

Hi @mosalvar,

You need to move your date above region and enable the total for columns.

If you then select collapse the date, you’ll end up with the annual total across all regions.

mosalvar · January 21, 2025, 10:24am

Hi David!
Thanks for your reply. I tried that approach already but the problem is that the 3Yr Total for a Region is not shown.
Is it possible to mimic this table structure exactly?

Another, lower priority Q: Is it possible to have cells/columns referenced? When presenting, sometimes it´s easier to refer to the data on cell H3 for example, as saying for Product X on EU on 2027.

Thanks!

David_Wong · January 21, 2025, 4:27pm

Hi @mosalvar,

Isn’t your “3Yr Total” just the subtotal?

You can enable subtotal for columns and customize the label.

QuickSight doesn’t have cell references like in Excel.

mosalvar · January 21, 2025, 5:35pm

I´ve also tried that but the result does not meet the requirement:

If you see the reference image, the requirement is to have a NA + EU for each year as well.
Is there any way to use calculated fields or any other method beyond the properties of the visual to achieve this?

mosalvar · January 29, 2025, 6:01pm

Hi David, any updates on this request?
Or is it confirmed that is just not possible.
Thanks!

David_Wong · January 30, 2025, 5:37am

Are EU and NA the only 2 regions in your dataset? You’re only showing those 2 regions in your screenshots.

If EU and NA are the only 2 regions in your dataset, your 3Yr Total is the same as the subtotal. You can even customize the label to have it say “3Yr Total” instead of “Subtotal”.

I replicated your dataset and created this pivot table:

mosalvar · January 30, 2025, 10:19am

Thanks David,
Yes, EU and NA are the only regions in the dataset.
The subtotal I’m already using and renamed to 3Yr Total but the request is also to have a Total per year (2025 EU+NA, 2026 EU+NA, 2027 EU+NA).
Please see the attached image (columns J, K, L).

David_Wong · January 31, 2025, 11:51pm

Hi @mosalvar,

I don’t think you can do this using QuickSight features but there’s a workaround. For every row that has “NA” or “EU” in your dataset, create a duplicate row that has “Total” as region. This essentially creates a region called “Total” in your dataset, giving you the desired pivot table.

Brett · February 12, 2025, 11:06pm

Hi @mosalvar,
It’s been awhile since we last heard from you, did you have any additional questions or were you able to find a work around for this case?

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

Thank you!

mosalvar · February 13, 2025, 12:49am

Hi Brett,
Sorry for not following up sooner. I was exploring a solution within Quicksight, but now I understand it’s not possible. I really appreciate your support. I think we can close this topic now.
Thanks again!

Brett · February 13, 2025, 4:02pm

Hi @mosalvar,
No worries at all, I’ll mark this as a feature request for you as well and close out for the time being.

Thank you