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