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.
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
Solved! Go to 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.
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.
Hi,
You may download my solution file from here.
Hope this helps.
Nice solution. Thanks.
You are welcome.
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.
@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...
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.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
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.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |