Remove zero rows from Google Sheets pivot table

Question

I have a pivot table to aggregate some financial balance by counterparties. Link to the example.

Over time, it's expected that the aggregate amount for most of the parties will be 0. I want to show only those rows in the pivot table where the aggregate is nonzero. Otherwise, my pivot table will be cluttered by tons of empty zero rows.

In the above example, this corresponds to hiding John, Mary, and Thomas records.

Is there a way to accomplish this?

Answer 1

You can achieve this by adding a filter on top of your pivot table.

1. Select the row with currencies (2nd row of pivot table)
2. Data->Create a filter...
3. Click on the last column
4. Filter by condition...->Custom formula is
5. =ABS(B3) + ABS(C3) + ABS(D3) > 0

See the example.

Answer 2

you could use a double query like:

=QUERY(QUERY(Transactions!A1:C, 
 "select A,sum(B) 
  where A is not null 
  group by A 
  pivot C", 1),
 "where Col2>0 
     or Col3>0 
     or Col4>0", 1)