Top 20% of Sales per Agent

rezacs

New Member
Joined
Sep 24, 2018
Messages
22
I'm looking to find a way whether it be in excel, power query, power BI or Pivot to display the top 20% of customer sales per agent without the need to create a Top 20% Conditional Format for each agent and for each update.

Agent/Cust is a concatenation of the agent name and customer number.

Below is an example of the data:

Agent/Cust Sale
John 12312 4,315,285
John 12414 4,289,698
John 12515 3,075,437
John 12616 2,918,548
John 12717 2,618,088
John 12818 1,916,799
John 12919 1,740,048
Beth 21212 1,873,115
Beth 21313 1,866,855
Beth 21414 1,844,919
Beth 21515 1,680,048
Beth 21616 1,582,468
Beth 21717 1,540,549
Beth 21818 1,495,924
Beth 21919 1,343,121
Beth 22111 1,280,746
Beth 22212 1,160,971
Beth 22313 1,111,249
Beth 22414 951,301
Beth 22515 867,384
Joan 31111 8,819,264
Joan 31212 685,446
Mike 41111 7,405,412
Mike 41212 5,161,727
Mike 41313 2,584,936
Mike 41414 2,362,040
Mike 41515 1,959,150
Mike 41616 1,920,940
Mike 41717 4,909,177
Mike 41818 1,213,664
Mike 41919 2,066,105
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
At this point just a table showing the same list but with an indicator in a new column that it is one of the top 20% for that agent.

By top 20% I want to see which records have the top X sale values where X is 20% of the number of customers that particular agent.

Guessing it might require the agent name to be separate from the cust number.
 
Upvote 0

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top