is this the sort of thing you want
I did this by grouping the specific "purchase Items" and naming the Group as shown
If there were a lot of these I would recommend creating a file with sample item and purchase item cross match then joining it with your data
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
FruitSamples_v10.2.twbx 23.9 KB
No, this still doesn't let me filter the way I need the data to filter, nor does it give me a sample count. In this workbook (as with mine), if I filter to Apple and Orange, or filter to Rye and Pink Lady, I lose the selection of clients who don't overlap - and I include those who sampled Bread, but I don't want those who sampled Bread, just who sampled Apple or Orange, or who bought Rye or Pink Lady (in this example). Then how many customers who bought Rye also Sampled Apple? What percentage of those who Sampled did not buy anything?
But it also needs to be dynamic enough to compare against ANY Sample to ANY product -we need to limit our data set dynamically to anyone mix of Samples and Products. And the Samples can be Apple1, Apple2, Apple3, Orange, Bread1, Bread2. So if Pink Lady is my new apple type, and I know that people who buy apples almost always get Rye, I may want to see all my customers who bought Pink Lady or Rye and compare them to any clients who sampled from Apple3 or Apple2, but I don't want to see any one who didn't purchase or sample from those selections. Then my boss is really interested in seeing what the correlation between Sampling Orange is to our entire apple line - Pink Lady, Granny Smith, etc. Can we structure our data so we can both look at the same data set, filter it to what we want to see, and still run queries against that smaller, filtered set.
After I posted this yesterday, we worked on it for a while and are coming to the conclusion that because we need to do summary calculations and percentages, we really need to break this down inside the SQL, which is frustrating, because it doesn't deliver the dynamic analysis we were hoping for.