I need to find out which of my customers has not bought which of the top 3 products based on sales within his industry.
I've got the following two tables. Table A is showing sales of the top 3 products we've sold to customers in a specific industry. Table B shows sales of a customer and the industry he belongs to.
Now I would like to select a customer ... let's say Customer1. The result should be a table with all the top 3 products (without sales per industry) for the customers industry including the customers current sales. There should be a line with 0 as en entry for the customers current sales for each of the top 3 products of his industry the customer hasn't bought yet. Sales of products which are not in table A (the one with the top 3 products), should not appear in the results. The result should look like this:
To make it a little more difficult: How about using a parameter to change the top 3 to top 2, even if i still have the top 3 products of each industry in table A. So there is no change in the underlying data, but the result should look like this:
I've tried some set analysis with E() and P() functions in it, but couldn't make it work. Is this approach the correct one or will I have to do a lot of scripting in the data editor?
Thanks and kind regards,