Sumif with Unique Values

[JasmineG]

I have 2 sets of data, let's say X and Y. X is a set of ID numbers and Y is a set of principal balances. I need to first sum all of the Y values that correspond to each X and then count each of those results that are equal to zero for a unique X.

For Example:

X: ID Number

Y: Balance
Z: Sum of each "Y" under the unique "X"

After calculating Z, I will need to count each unique X that has a Z of zero. In the above case, it would be 1.

In excel, I can use a Sumif to calculate Z (SUMIF(X,UNIQUE'X,Y) where I just removed duplicates in a separate tab to arrive at "UNIQUE'X". After calculating Z, I created a flag: If(Z=0,1,0) and did a count function for all of those cells. How do I do this in alteryx?

Note that this is a very simplified version of my dataset to ease the explanation. 

[Claje · Accepted solution]

Hi,

This is a quick example of how to do something like this.

Basically, you can use the Summarize tool and the Join tool to create your Z value, and then use a Filter tool and a Summarize tool to count the distinct set of RecordIDs which have Z = 0

Hope this helps!

[JasmineG]

That worked after I eliminated "0" X's through a filter tool! Thank you so much!!