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

[JerryCooperCMA]

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.

[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!

[binuacs]

@JerryCooperCMA One way of doing this

[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:

Is there a way to "tweak" that workflow to deal with this situation?

[binuacs]

@JerryCooperCMA Updated to reflect the new requirement

[binuacs · Accepted solution]

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

[DavidSkaife · Accepted solution]

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

[JerryCooperCMA]

That works! Thank you @DavidSkaife 

[JerryCooperCMA]

Thank you @binuacs this works great!