Top Customer Salesperson with Power Query

Power Query
1

Hi, I want to display in Power Query for each customer only the salesperson with which they have made the most sales. Example data are attachedcustomer_sales.xlsx (37.2 KB)

image
image808Ă—142 4.06 KB

Does anyone have an idea how I calculate this in Power Query for each customer? Thanks

2

Hi @Mario,

Check this m code:
let
Source = Excel.Workbook(File.Contents(“D:\Downloads\customer_sales.xlsx”), null, true),
customer_sales_Table = Source{[Item=“customer_sales”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(customer_sales_Table,{{“customer_id”, Int64.Type}, {“sales_person_id”, Int64.Type}, {“sales_qty”, Int64.Type}, {“sales_price”, type number}, {“sales_ammount”, type number}}),
#“Grouped Rows” = Table.Group(#“Changed Type”, {“customer_id”, “sales_person_id”}, {{“Sales_Amount”, each List.Sum([sales_ammount]), type number}}),
#“Added Custom” = Table.AddColumn(#“Grouped Rows”, “Custom”, each let
_customerID = [customer_id],
_salesPersonID = [sales_person_id],
_salesAmount = [Sales_Amount] in

if _salesAmount =  List.Max(Table.SelectRows(#"Grouped Rows", each [customer_id] = _customerID)[Sales_Amount]) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Top Customer Salesperson with Power Query.pbix (51.2 KB)

3 Likes
3

Hi @Mario,

For this type of scenario you can use Group By

I created 2 queries, the first shows the Top Sales Person and Value in your Customer Sales table

image
image1383Ă—377 33.4 KB

And the second is just a summary table with each Top Sales Person and Value per Customer

image
image761Ă—380 17.7 KB

Here’s my sample file. eDNA - Top Sales Person per Customer.pbix (69.7 KB)
I hope this is helpful.

4 Likes
4

Hi @ricardocamargos88,

We responded almost at the same time and both chose an approach utilizing Group By but still ended up creating a different solution. That’s what I like most about the forum - the same tools - and yet, so many different solutions…

Liked the way you created the second environment for your conditional filter logic :+1:
I don’t see many on the forum do that, where did you pick that up?

5

Hi @Melissa,

I like using lists, I believe it’s faster than merge all the columns and select them.
Also I have studied some a little bit about m language, we do not see many documentations about it e when we find it, is not so clear. So we need to practice a lot. :rofl:

6

Hi @Mario, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

7

Hi Mario,

I have uploadedcustomer_sales.xlsx (44.8 KB) sample file. you can use Group by feature of Power Query to get this result.

8

@ricardocamargos88 great solution. This is what I’ve been looking for.