Expand and Collapse

Looker Q&A Looker
1

Hi everyone,

I’m building a financial dashboard in Looker Studio using a pivot table connected to a Google Sheet. My data structure looks like this:

  • Each category has one row that contains a pre-calculated total
  • Below that, the individual line items that make up that total are listed as detail rows

The problem:

  • When expanded: everything looks correct — I can see the total row and the individual detail rows underneath it
  • When collapsed: Looker Studio sums up the total row together with all the detail rows, resulting in double counting

This happens because Looker Studio applies SUM aggregation across all rows within a group, including my pre-calculated total row which already contains the summed value of all detail rows.

I want to keep this structure (pre-calculated totals + detail rows in the same dataset) but I need the collapsed view to only show the pre-calculated total value, not the sum of total + all detail rows.

Has anyone found a clean solution for this? I’ve considered using a level indicator column to filter on, but Looker Studio doesn’t seem to support dynamic filtering based on the expand/collapse state.

Any help is appreciated!

2

I don’t think Looker Studio has a dynamic “use this row when collapsed, use those rows when expanded” behavior for pivot totals. The collapsed value is going to aggregate whatever rows are in the group, so mixing a pre-summed total row with detail rows will double count.

The cleanest fix is usually to split the model a bit:

  • keep detail rows in one source/table for the expandable view
  • keep pre-calculated totals in a separate source/table or blended view for the summary
  • add a row type / level field and build separate charts for summary vs detail if they need different aggregation rules

A level indicator can help, but only if you explicitly filter on it. I would not rely on expand/collapse state to control the calculation; it is more of a display interaction than a data modeling layer.