Filter to Remove All Records for a given Location where Each is Zero in Another Field

This one is kind of hard to put into words, but the Input and desired Output are shown below. I'd like to apply a Custom Filter (or some other tool?) to evaluate every Account for each Location, and if *all* Amounts (for every Account) are zero, filter out (exclude) every record for that Location. The resulting table would include only those Locations which have a non-zero Amount in at least one Account.

In this case all Tampa records are removed because all Account Amounts are zero, while all 5 records are retained for Lansing because not all Account Amounts are zero. Note that even if only one Account had a non-zero amount, all five (5) records would still be retained for that Location.

Thank you for any help you can provide with this.

Preparation

Accepted answers

binu_acs

@JerryCooperCMA Another way of doing this by filtering out the amount with 0 records

Filter_0_records_1109_1.yxmd

davidskaife

Hi @JerryCooperCMA

attached is a tweaked workflow that should work - define the min and max values and filter on either of them not being 0

Filter 0 balance.yxmd

All comments

davidskaife

Hi @JerryCooperCMA

Here is one way of doing this - Summerise by Location and sum the amounts, append the sum_amount back to the main data and filter where the sum_amount != 0

Workflow attached!

Filter 0 balance.yxmd

binu_acs

@JerryCooperCMA One way of doing this

Filter_0_records_1109.yxmd

JerryCooperCMA

Thank you both for the quick reply! I forgot to include a case like this, where the SUM of amounts could be zero but not all amounts are zero, in which case I would still need to retain the Location: