cancel
Showing results for
Did you mean:
Highlighted
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:

 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
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:

 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

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:

 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

Announcements

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

And the winner is...

#### Announcing the New Spanish Forum

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

#### 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

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors