Top Sales Person By Product

[StuBee]

Hi Everyone

I am struggling to create a view of the top sales person in a grid that lists total overall sales.

Amongst my data is a roll up of total sales, joined to a product chart, this can be then filtered by Person.

I have tried the Rankx filtering the Person on the graph, to no luck.

The columns I need are something like:

SalesRep	Product	Total Sales
James	A	10
James	B	12
Paul	A	25
Paul	B	2
Paul	C	16
Jeff	A	18
Jeff	C	29

I am looking to display it like:

Product	Sales	Top Sales Rep
A	53	Paul
B	14	James
C	45	Jeff

Thanks for looking

[StuBee]

Hi and Thanks So much @amitchandak 

If I had another table with just a list of regional office in it, that joines to each offices sales data on the office name column, is there anway that I could do the same  but by "Office" instead of "Sales Rep"?

I wanted to ask if it were possible to do the ranking from the offices, which are all listed together on a seperate Table like:

Office

1

2

3

4

The sales data comes from each office as above but with an office name,

SalesRep	Product	Office	Total Sales
James	A	1	10
James	B	1	12
Paul	A	1	25
Paul	B	1	2
Paul	C	1	16
Jeff	A	1	18
Jeff	C	1	29

pulled to power bi, summed on the product table in the same format, then listed as in the first example.

Could I display it like

Product	Sales	Total Office
A	320	1
B	250	2
C	180	3

Again thanks so much for the response

 

[amitchandak · Accepted solution]

@StuBee , Create a rank like this and use visual level filter  =1

rankx(filter(allselected(Table[SalesRep],Table[Product]), Table[Product] =max(Table[Product])), calculate(sum(Table[Sales])))

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures