Introducing advanced formatting options for pivot table totals and subtotals in Amazon QuickSight

Readers want to understand data quickly when viewing reports in Amazon QuickSight. To achieve that, authors need more capability to control the format and design of pivot tables. That is why QuickSight has launched more visual display options for pivot tables, giving you more control over the positioning of totals as well as contextual information for subtotals. These capabilities are part of a continued focus of giving QuickSight users more flexibility when designing dashboards and paginated reports. Additionally, the recent launch of the hierarchy layout for pivot tables includes a layout option for pivot tables that uses indentations to differentiate items from different fields, resulting in a more readable table.

Contextual subtotals

A common challenge when viewing pivot tables with more than a few fields is quickly understanding the subtotals. This is especially difficult when some fields have a few values and others have many values (high cardinality). This is why QuickSight has introduced contextual subtotals. Contextual subtotals refer to the labels that are displayed in a pivot table. Instead of a generic subtotal label, authors can display the group name alongside the subtotal label, enhancing clarity and context. This issue becomes particularly challenging in multi-page, multi-level reports, where subtotals lack meaningful insights if the associated group is not visible to the author or reader within the given page or view. Note that contextual subtotals are currently available in tabular pivot options. For hierarchy layout, the subtotals are part of the hierarchy levels itself.

An example of this challenge would be a pivot table analyzing sales data across various product categories. Authors can customize subtotals to include the group name (for example, “Electronics,” “Clothing,” or “Home Appliances”) alongside subtotal labels. This customization significantly improves the financial report’s clarity, aiding stakeholders in identifying the product category corresponding to each subtotal.

To turn on contextual subtotals, complete the following steps:

1. Open the Format visual menu for the pivot table by choosing the pencil icon.

The visual is in hierarchy layout by default.

2. Under the Pivot options menu, for Layout, select Tabular.

The subtotals are moved to the bottom of the list and contextual subtotals are on by default.

On the Subtotal menu, you can see that the label is referencing the parameter <<$aws:subtotalDimension>>. You can change the text as needed.

Contextual subtotal relevance for paginated reporting

When working with paginated reports in QuickSight, the contextual subtotal is of the utmost importance. Pivot tables displaying multi-level information across multiple pages require subtotals to be displayed with relevant group and level information for readability. Alternatively, when you have subtotals with contextual information displayed, you can also save visual real estate by hiding the group or level column. The following are some use cases and examples of where contextual subtotals add value:

• Financial statements: Consolidated balance sheet – In the case of financial statements where there are multiple levels in the accounting hierarchy, contextual subtotals are a must for readability. Before contextual subtotals, this was only possible with multiple visuals with level-specific filters. You can now create the balance sheet report with a single visual.

• Monthly sales rep report by region and subregion – In this case, the columns for region and subregion are hidden to save space, resulting in less white space on the visual.

Several other reports like customer invoices, itemized hospital bills, claims reports, patient orders, and more can now be authored with great ease using the contextual subtotals feature.

Positioning control for totals

Another common challenge when viewing pivot tables is finding the total for either rows or columns. The total, sometimes referred to as the grand total, may be multiple scrolls or pages away. This can make it difficult to find that data point and it’s especially cumbersome when comparing it to a data point on a different page. To address this, QuickSight now offers additional positioning options for pivot table totals, offering greater flexibility in presentation and aligning with capabilities already available in regular tables that allow for top or bottom position for row totals, and left or right positioning options for column totals, simplifying data analysis and improving the overall user experience.

To change the position of the row total in a pivot table, complete the following steps:

1. Open the Format visual menu for the pivot table by choosing the pencil icon.
2. Expand Total and under Rows, select Show totals.

The total appears on the pivot table by default. The Auto option will be particularly useful for smooth transitions between hierarchy and tabular layouts in pivot tables. In the hierarchy layout, the default position is Top, whereas in the tabular layout, the default position is Bottom.

You can then change the position of the total. The following screenshot shows the total on the bottom.

The column total position can be changed as well.

3. Expand Total and under Columns, select Show totals.

By default, the total is on the right. You can then change the position to the left, as shown in the following screenshot.

Conclusion

This post explored two additions to pivot table capabilities. Contextual subtotals give readers information about the grouping that the subtotal refers to. Positioning control for totals enables you to change the location (top or bottom, left or right) for totals in a pivot table. Both additions provide more flexibility in the way a pivot is designed and viewed.

If you have any questions or feedback, please leave a comment.

For additional discussions and help getting answers to your questions, check out the QuickSight Community.

About the Authors

Salim Khan is a Specialist Solutions Architect for Amazon QuickSight. Salim has over 16 years of experience implementing enterprise business intelligence (BI) solutions. Prior to AWS, Salim worked as a BI consultant catering to industry verticals like Automotive, Healthcare, Entertainment, Consumer, Publishing and Financial Services. He has delivered business intelligence, data warehousing, data integration and master data management solutions across enterprises.

Douglas Bergquist is a Manager of Solution Architects for Amazon QuickSight, AWS’ cloud-native, fully managed SaaS business intelligence (BI) service.  As an experienced leader, Douglas has been guiding customers strategize and implement BI solutions for 10+ years.  Prior to AWS Douglas ran Solution Architect teams, and worked hands-on deploying BI and data warehousing solutions as a BI developer.

Bhupinder Chadha is a senior product manager for Amazon QuickSight focused on visualization and front end experiences. He is passionate about BI, data visualization and low-code/no-code experiences. Prior to QuickSight he was the lead product manager for Inforiver, responsible for building a enterprise BI product from ground up. Bhupinder started his career in presales, followed by a small gig in consulting and then PM for xViz, an add on visualization product.