Hi everyone,
I am building an account balance report in Amazon QuickSight and running into an issue handling a semi-additive measure when collapsing a pivot table hierarchy.
Setup & Business Requirement
-
Visual type: Pivot table
-
Required hierarchy structure: The end user/client specifically requires the hierarchy to start at the customer level and drill/drop down into date (customer → date). Inverting this hierarchy (e.g., placing date first) or splitting it across unrelated tables is not an option for this workflow.
-
Row field wells:
-
customer (parent / top level)
-
date (child level)
- Value field well: account balance
Sample Data:
Each customer has a daily snapshot of their account balance:
The Problem
When the hierarchy is expanded, each individual day correctly reflects the customer’s balance for that date.
However, when a user collapses the pivot table to the customer level, QuickSight aggregates the daily balances by summing them across all dates (e.g., John displays 16, Mary displays 32, Carl displays 48). Because account balances are point-in-time snapshots (semi-additive across time), summing them produces an incorrect and misleading figure.
Desired Outcomes
Maintaining the required customer → date hierarchy, I am looking for a solution that achieves either of the following behaviors:
-
Option A (Preferred): When collapsed to the customer level, display the last available balance for that customer (e.g., John = 1, Mary = 2, Carl = 3), while still displaying daily balances when expanded.
-
Option B: Leave the measure cell blank / null when collapsed to the customer level so incorrect additive totals are not presented to business users.
What I’ve Tried
-
Format visual settings: Disabled row subtotals and row totals under the visual format pane. This removes summary rows at the bottom/top, but it does not change how the parent row cell itself aggregates values when collapsed.
-
Calculated fields: Experimented with LAC-A / LAC-W expressions (such as lastValue or maxOver), but I haven’t found a clean way to detect the visual’s collapsed aggregate state versus the expanded granular state within the pivot table.
Questions
-
Is there a recommended LAC expression or calculated field pattern to dynamically return the latest balance or NULL depending on whether the customer row is collapsed or expanded?
-
Has anyone solved semi-additive measures in QuickSight pivot tables while strictly preserving a parent-to-child (customer → date) drill-down hierarchy?
Any guidance or workaround would be greatly appreciated!
