Pivot table sorting by row totals and SQL pivot support

1

Hi everyone,

I’m trying to build a table that shows a metric per entity, broken down by month, with a total column and sorted by the total value.

Two limitations seem to appear:

  1. The native Pivot Table allows the monthly breakdown but does not allow sorting by the row total column.

  2. When using a native SQL query, the Pivot Table visualization is not available, even when the query is already aggregated.

Example simplified query:

SELECT
    entity,
    DATE_TRUNC('month', created_at) AS month,
    SUM(metric_value) AS total_value
FROM dataset
GROUP BY entity, month

Is there a recommended approach in Metabase to:

  • Sort a pivot table by the row total, or

  • Use pivot-style visualization with SQL-native queries?

Thanks!

1 Like
2

The pivot table viz wants to do the aggregation itself so it can react to the user changing the display. If you don’t need the pivot features, then just do the math in SQL and output the table in the order you want it. It’s not pretty, but does the job.

If you’re committed to a pivot but using a SQL query as a base, define the SQL query as a model or saved question then create a new question in the notebook editor using the previous one as a source. Aggregate the data the way you want it and it should allow you to create the pivot.

3

I've tested the workaround of using a Saved Question/Model as a source, but the pivot table sorting remains inconsistent. Specifically, numeric/value columns are not being ordered correctly, following the behavior mentioned in the previously linked issues. Has anyone found a way to force the correct sort order in this scenario?

4

Looks like what you want is not currently implemented. If you have to use a pivot you can’t get what you want. If you use a SQL query and a basic table display, you can.