cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MunroA7
Advocate III
Advocate III

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
Vvelarde
Community Champion
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 MeasureMy sample data and Ranking Measure

 

Visual level filter is 1Visual level filter is 1

 




Lima - Peru

View solution in original post

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

View solution in original post

4 REPLIES 4
Vvelarde
Community Champion
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 MeasureMy sample data and Ranking Measure

 

Visual level filter is 1Visual 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?    

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors