Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |