Need easier solution for Unique Count of Vendors then count of a stat

[ELPC]

Hi all, 

There has to be a better way to do what I need to do in Alteryx so need your help.  I have some vendor data. I can use the summarize tool to get a unique count of vendor by Department and total spend of the department fairly easily.  The part that I need help with is a count of a statistic of the vendor.  A vendor can either be On Network or Off Network.  Once I get the count of vendors by Department, I need a column of how many of those vendors are On Network and another column of how many of the vendors are Off Network.  I was able to use a lot of tools to complete what I need by using one filter tool per Department.  However, if I have 100 departments, that means I will have to use 100 Filter tools and I don't want to do this.  Please see my example workflow. 

How do I simplify it?  

[Claje · Accepted solution]

Hi,

I've attached a potential solution.

This should work for your use case.  Basically we do a second set of summarization grouped by the "Network" column, and then use a Cross Tab and data cleansing tools.  This gives us the same output as your filter->summarize->cross tab->union solution.

Let me know if you have any questions!

[ELPC]

Thank you so much!  That worked and will save me a ton of time instead of using 100 filter tools.