Identify top 80% revenue with multiple criteria

New Contributor

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.  

 

 

 

 

 

1 Reply

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.

DTE_0-1646835469864.png

Sort the SKU field descending by revenue:

DTE_1-1646835569152.png

DTE_2-1646835577926.png

Then put a values filter on SKU:

DTE_3-1646835601716.png

DTE_4-1646835615283.png

Change the value field settings on the second revenue field:

DTE_5-1646835641064.png

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

DTE_6-1646835675931.png

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.