Help to get the top sales person

[Werick]

I have the following table

Person	Sale amount	Date
A	2	2020/04/21
A	53	2020/04/21
A	1	2020/01/25
A	58	2020/01/04
A	91	2020/05/22
A	51	2020/03/08
B	42	2020/04/16
B	73	2020/01/31
B	53	2020/03/28
B	52	2020/03/28
C	27	2020/05/17
C	96	2020/01/01
C	96	2020/05/29
D	17	2020/05/07
D	54	2020/04/27
D	59	2020/04/27
D	27	2020/04/27
D	58	2020/05/07
D	66	2020/01/20
D	80	2020/04/09

I am trying to see who the top overall salesperson is and secondly who the top sales person is for a spesific month.

I have tired adding a coloum that calculates the total sales for a person and then Rank that but it does not work as the ranking "sees" the additional values as the same total and then ranks then incorreclty (hope this makes sense)

Please help

[v-zhenbw-msft]

Hi @Werick ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You may download my solution file from here.

Hope this helps.

[Syndicate_Admin]

Nice solution. Thanks.

[Ashish_Mathur]

You are welcome.

[v-zhenbw-msft · Accepted solution]

Hi @Werick ,

If you want to get the Top sales person and get the day with the most Sales Person’s sales, please refer the following steps.

1. Create a measure to show the TOP sales.

TOP sales = 
var _total_person = SUMMARIZE('Table','Table'[Person],"Sales",CALCULATE(SUM('Table'[Sale amount])))
return
MAXX(_total_person,[Person])

2. Then create a measure to get the date with the most sale person’s sales.

Top Sales Date =
VAR _total_person =
    SUMMARIZE (
        'Table',
        'Table'[Person],
        "Sales", CALCULATE ( SUM ( 'Table'[Sale amount] ) )
    )
VAR _max_person =
    MAXX ( _total_person, [Person] )
VAR _total_date =
    SUMMARIZE (
        'Table',
        'Table'[Date],
        'Table'[Person],
        "Sales_a", CALCULATE ( SUM ( 'Table'[Sale amount] ) )
    )
VAR _max_sale =
    MAXX ( FILTER ( _total_date, [Person] = _max_person ), [Sales_a] )
RETURN
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( _total_date, [Sales_a] = _max_sale )
    )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Werick , Rank and TOP n will Help

https://databear.com/power-bi-dax-topn-function/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[harshnathani]

Hi @Werick ,

I assume you must be having a Date Table and a Salesperson Table.

If not please create them to get your data modelling correct.

The relation should look like Salesperson Table 1 ---> * Sales Table   and   Dates 1 ----> * Sales Table.

Post that create a measure.

Total Sales = Sum(Sales Table[Total Sales])

and a Ranking Measure

Rank = RANKX(ALL(Salesperson Table [Salesperson Names]), [Total Sales])

Pull Customer name from Customer Table, Year and Month from Date Table and Rank Measures.

Apply th visual filter Rank = 1 and you will be able to find the top Salesperson each month.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[AilleryO]

Hi,

You can use the filter panel to get a TOP N, or you can use formulas depends on what you really want.

If you want to use formulas, maybe you should have a look at :

https://community.powerbi.com/t5/Desktop/DAX-TOP-N-BOTTOM-N-Sales/m-p/523386#M244890

Hope this helps, otherwise tell us more.