cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Adam_Defries Helper I
Helper I

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
Super User III
Super User III

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

View solution in original post

1 REPLY 1
Super User III
Super User III

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

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors