Identify top 80% revenue with multiple criteria

[BK_Bangkok]

Hi All,

I am totally stumped. I have a sales data set that I am working with with multiple unique criteria. roughly 1500 lines/SKUs that make up about 10 categories, 30 classes and 5 sizes.  All with unique revenues. 

E.g. Category: Food Class: Spices Size: 1g total revenue: $100,000, total lines 37

First I am trying to find the SKUs that generated the top 80% (or close to) of revenue of the matching groups for category, class and size. Summing largest values to smallest values. Once that data has been identified I want to return the MIN of the values making up that 80%? 

Does anyone know of a formula(s) or a technique that may work for this type of situation. The goal is to remove the manual filtering that I am currently doing to identify the min top 80% value. 

Thank you in advanced.  

[#2]

Hi @BK_Bangkok 

I guess this should be possible with a pivot table:

Put the category and SKU's in the rows range (I just use a simplified example)

Put the revenue in the values range

Put the revenue a second time in the values range.

Sort the SKU field descending by revenue:

Then put a values filter on SKU:

Change the value field settings on the second revenue field:

Chose "Rank Smallest to Largest" based on SKU field:

Then, your pivot table just shows the top 80% and you see also the rank (in revenue2). As the table is already sorted by revenue, the rank is not really necessary, but it helps if you change the sorting.