Count between tables

[Anonymous]

Hi

I have a large data set that lists a customer, product(s) and employee assignment. each employee needs to be counted per product and have a weighting applied. 

however it is possible that there could be two employees listed against the same customer but in different products. 

for example:

Customer	Product 1	Product 2	Product 3	Product 4	Product 5
Customer ABC 1	Joe Blogs		Caty Biggs
Customer ABC 2		Caty biggs		Joe Blogs
Customer ABC 3			Joe Blogs
Customer ABC 4	Andy smith		Andy Smith	Joe Blogs	Caty Biggs
Customer ABC 5		Joe Blogs
Customer ABC 6	Joe Blogs		Caty biggs

using Power Query I created a table that lists all of the employee names. now I don't quite know how to do a count that counts how many times an employee appears for the customer. once I do that I then need to apply a weight. for eaample Product 1 = 10%, Product 2=15% Product 3=25%, Product 4= 40%and so on. 

so if Joe Blogs Appears in Customer 1& Product 1 then thats 10%, he then appears in Customer 2 Product 4 whcih should then add 30% to the 10% and so on. 

can anyone help with this?

[d_gosbell · Accepted solution]

What are trying to do would be hard with the structure you have. I would suggest unpivoting it so that you have a structure like the following:

Customer	Product	Employee
Customer ABC 1	Product 1	Joe Blogs
Customer ABC 1	Product 3	Caty Biggs
Customer ABC 2	Product 2	Caty Biggs
Customer ABC 2	Product 4	Joe Blogs

Then you can do distinct count of customers per employee or apply percentages based on the product