Since no one esle is answering, I would typically do this in a two step process. First I would load the table as a temp table, and do the needed calcuation. Then, I would do a resident load of that table with the appropriate WHERE clause. It is a bit clunky, but it will work and is pretty simple. Just remember to drop your temp table after the resident load.
There is likely a way to do this with a GroupBy function, but I have not explored that at all.