cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Adam_Defries Frequent Visitor
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:

CustomerProduct 1Product 2Product 3Product 4Product 5
Customer ABC 1Joe Blogs Caty Biggs  
Customer ABC 2 Caty biggs Joe Blogs 
Customer ABC 3  Joe Blogs  
Customer ABC 4Andy smith Andy SmithJoe BlogsCaty Biggs
Customer ABC 5 Joe Blogs   
Customer ABC 6Joe 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
d_gosbell Senior Member
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:

 

CustomerProduct

Employee

Customer ABC 1Product 1

Joe Blogs

Customer ABC 1Product 3

Caty Biggs

Customer ABC 2Product 2

Caty Biggs

Customer ABC 2Product 4

Joe Blogs

  

 

 

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

1 REPLY 1
d_gosbell Senior Member
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:

 

CustomerProduct

Employee

Customer ABC 1Product 1

Joe Blogs

Customer ABC 1Product 3

Caty Biggs

Customer ABC 2Product 2

Caty Biggs

Customer ABC 2Product 4

Joe Blogs

  

 

 

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