Top 20% of Sales per Agent

[rezacs]

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

 

[sbawnh]

How do you want the output to be designed?

 

[rezacs]

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.