Admin setting to configure default totals/subtotals for new Pivot Tables

Feature Requests
1

Summary

Please add an instance-level admin setting to configure whether totals and subtotals are enabled by default when creating new Pivot Tables.

This is different from requests for more granular subtotal/grand-total controls within an individual Pivot Table. The request here is specifically about administrator-configurable defaults for newly created Pivot Tables across the entire Metabase instance .

Specifically:

  • Show subtotals for row dimensions by default: ON/OFF
  • Show row totals by default: ON/OFF
  • Show column totals by default: ON/OFF

This is not a request to change Metabase's default behavior for everyone , nor a request for additional per-question controls. The current defaults could remain unchanged.

The request is to allow administrators to choose the initial defaults for their Metabase instance.

This is particularly important for performance. Pivot totals and subtotals can cause additional aggregation queries to be generated, and these queries may execute while the user is still configuring the Pivot Table , before they have an opportunity to disable the totals.

Problem

Currently, when creating a Pivot Table, totals and subtotals are enabled by default.

They can be disabled manually for each question, but this becomes problematic with large datasets or expensive aggregations.

For example, when building a Pivot Table:

  1. Create a Pivot Table.
  2. Add several fields to Rows.
  3. Metabase enables subtotals for those fields by default.
  4. Additional aggregation queries are immediately executed.
  5. The query can become extremely slow before the user has an opportunity to disable the subtotals.

In this situation, manually disabling totals after configuring the Pivot Table is not a practical workaround, because the expensive queries have already been triggered during configuration.

This appears related to the Pivot Table query-generation and performance issues discussed in #13573, #14336, and #70779.

Proposed solution

Add instance-level settings under Admin Settings, for example:

Pivot Table defaults

  • Show subtotals by default: ON/OFF
  • Show row totals by default: ON/OFF
  • Show column totals by default: ON/OFF

For backward compatibility, the Metabase defaults could remain:

  • Subtotals: ON
  • Row totals: ON
  • Column totals: ON

Administrators working with expensive queries could instead configure:

  • Subtotals: OFF
  • Row totals: OFF
  • Column totals: OFF

Users could still enable any of these options for individual Pivot Tables when needed.

Important implementation detail

The configured defaults should be applied when the Pivot Table visualization is initialized, before the first pivot query using those settings is executed .

The issue is not only the visualization settings that are eventually saved with the question. The performance problem occurs while the Pivot Table is being configured: automatically enabled totals and subtotals can trigger expensive additional queries as row dimensions are added.

Applying the instance defaults before query execution would prevent those unnecessary queries from running in installations where totals and subtotals are normally not required.

This could also provide a relatively simple mitigation for existing Pivot Table performance issues without changing the default behavior for existing Metabase installations.

1 Like