Calculating all Possible Combinations of customer Filters and Options

[sparshpankaj]

Hi everyone! 

I run a small F&B business and Covid hit us hard. That's why we wanted to stream line our efforts and figure out sizeable customer niches such that we could focus more on targeting these groups. For the same I have come up with different filters like categories and options for these filters. (refer to sheet 2 of sample data or below given table)

What I am facing a problem with is how to arrive at combinations of all these filters and options. 

Note: Before I give an example of a problem I should make clear that every customer and every order has a unique key column. So the final output desired between multiple combinations of filters and options should be # customers, # orders, sales value.

For eg: How do I arrive at a number for customers, orders, sales value who falls into Category 1, 2 & 3 fulfilling one of their their respective options. This as a separate problem is easy to find, however when it comes to getting numbers for each segment then this exercise would get too vast.

So in essence I need a mechanism to accommodate any combination of filters for any combination of options.

I have attached a sample of my data. Would request your guys' help with the same. 

Thank you all for your time!

[clmc9601]

Hi @sparshpankaj,

I'm glad you came to Alteryx with this question! Alteryx is great at batching results like this. 

I believe I have a solution for you. I created a cartesian join on purpose, which finds all the combinations. You'll have to finish it out with your actual data, but hopefully this will get you started. 

Once I had all the combinations, I joined to the data and then summarized, grouping by each category combination. You can sort and filter afterward to get your desired results.

If this helps, please consider marking it a solution so others may find it. Please let me know if you have any questions!