Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I've got a requirement to produce some total sales per customer, then I need to rank them in order of value.
They've requested that I produce the Top 10 customers, then they want the Top1 customer for each sales person.
I've tried doing this in DAX and I'm struggling.
I've made a new table using Customer Sales = SUMMARIZE('Quotes and Opps','Quotes and Opps'[Customer],'Quotes and Opps'[Account Mgr.], "Total Sales", SUM('Quotes and Opps'[Value]))
and another table for Top 10 customers = TOPN(10,'Customer Sales','Customer Sales'[Total Sales],DESC)
I've made a rank in Customer Sales using a measure: Rank = RANKX(ALL('Customer Sales'),'Customer Sales'[Sales],,DESC)
If I try and make a Top1 measure for each sales person I only get one record in total rather than one per person. Any ideas if I'm taking the wrong approach or how to do TopN per person based on their best customer?
Many thanks
Solved! Go to Solution.
Hello
@Anonymous
Use this measure
Ranking = RANKX(ALLEXCEPT('Quotes and Opps','Quotes and Opps'[Account Manager]),CALCULATE(Sum('Quotes and Opps'[Value])),,DESC)
and visual level filters select ranking is 1.
Hi MunroA7,
What is your current situation?
Could you please explain a bit about "it's ranking all the totals as '1' so I can't separate out the top result" here?
Besides, which table that you have altered following the suggestion posted by Victor?
Take a try to change the ALL('Customer Sales') with ALLEXCEPT('Customer Sales','Customer Sales'[Account Mgr.]) for the Rank measure, which should be :
Rank = RANKX(ALLEXCEPT('Customer Sales','Customer Sales'[Account Mgr.]), 'Customer Sales'[Sales],,DESC)
Then select the rank visual filter with 1, to check the result.
Reply back if you need any further assistance.
Regards
Hello
@Anonymous
Use this measure
Ranking = RANKX(ALLEXCEPT('Quotes and Opps','Quotes and Opps'[Account Manager]),CALCULATE(Sum('Quotes and Opps'[Value])),,DESC)
and visual level filters select ranking is 1.
Thanks for that. For some reason it's ranking all the totals as '1' so I can't separate out the top result?
Hi MunroA7,
What is your current situation?
Could you please explain a bit about "it's ranking all the totals as '1' so I can't separate out the top result" here?
Besides, which table that you have altered following the suggestion posted by Victor?
Take a try to change the ALL('Customer Sales') with ALLEXCEPT('Customer Sales','Customer Sales'[Account Mgr.]) for the Rank measure, which should be :
Rank = RANKX(ALLEXCEPT('Customer Sales','Customer Sales'[Account Mgr.]), 'Customer Sales'[Sales],,DESC)
Then select the rank visual filter with 1, to check the result.
Reply back if you need any further assistance.
Regards
Thank you Michael.
That did the trick. Genius.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |