Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

@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.

 

 

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

 

@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.

 

 

My sample data and Ranking MeasureMy sample data and Ranking Measure

 

Visual level filter is 1Visual level filter is 1

 




Lima - Peru
Anonymous
Not applicable

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

Anonymous
Not applicable

Thank you Michael.

 

That did the trick.  Genius.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.