cancel
Showing results for
Did you mean:

## TopN total of sales and grouped by sales person

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

2 ACCEPTED SOLUTIONS
Community Champion

Hello

@MunroA7

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.

My sample data and Ranking Measure

Visual level filter is 1

Lima - Peru
Microsoft

Hi MunroA7,

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

4 REPLIES 4
Community Champion

Hello

@MunroA7

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.

My sample data and Ranking Measure

Visual level filter is 1

Lima - Peru

Thanks for that.  For some reason it's ranking all the totals as '1' so I can't separate out the top result?

Microsoft

Hi MunroA7,

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.

Announcements