Forum Discussion
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?
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
1 Reply
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