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

View solution in original post

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

View solution in original post

Thank you Michael.

 

That did the trick.  Genius.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors