cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Count between tables

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

## Re: Count between tables

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