I'm not sure I completely understand the desired outcome. For 15206, how do you know that Self-Pay is supposed to be part of the Financial Class, and for 98006, how do you know Commercial is included?
Sorry for not being clear, I hope this clarifies what I'm looking for:
The data source is set up so that each row is a combination of customer, zip code and financial class. The same customer could have multiple financial classes. The idea is, I want to know how many customers have each unique combination of financial classes. Since there are 5 options for financial class, there could be 100+ combinations. Writing a formula for each would be very time consuming!
For this example, I aggregated the data source by zip code (trying to simplify) - so that same data structure applies. The data source is set up like this:
zip code financial class
15206 Self Pay
15206 Self Pay
98006 Medicaid/BHPP HO
98006 Self Pay
So the reason I know that for 15206 "Self Pay" is supposed to be part of the financial class is because that record exists in the data set - if there was no 15206 "Self Pay" field, it wouldn't be. I hope that makes sense and thanks for your help!
I see what you're trying to do... kinda a 5 dimensional venn diagram to see how many records in each unique combination (there are 31 combinations)
If it were me, I would first aggregate your data some more so you'd have:
15206 Self Pay 2
15206 Commercial 1
98006 Commercial 3
98006 Medicaid 1
Then create a truth table for each zip and convert back to your 31 individual strings
Self Pay Commercial Medicaid Charity
15205 1 1 0 0
98006 1 1 1 0
1100 would be "Self Pay, Commercial", 0101 would be "Commercial, Charity" etc.
Probably easiest to do this in Excel then chuck the results back into Tableau.
Thanks Robin. Yes, that's what I ended up doing for this project. But I would still love to see if anyone has any great ideas for how to do this more easily using Tableau. I have a few projets that would benefit from this type of analysis, so all suggestions appreciated!