I have been searching for a solution to my problem for the better part of a day, so I thought I would post here hoping someone could help me out. Let me first start with the question I am trying to answer: What percentage of SKUs does it take to reach 80% of revenue. Here is a simplified chart to use as an example (we can call the table "Data"):
<tbody>
</tbody>
So in this example, the quickest way to get to 80% would be ABC+CCC+CBA (50%+20%+20%). Thus it takes 3 of the 5 SKUs to get to 80% of the revenue, thus the answer is 60%.
I have used the RANKX function to add the Rank column, but this may be a red herring. I thought I could use it to return the rank when the running total hit 80% or higher. But as you can see duplicates mess that up (CCC and CBA are tied so they both show a rank of 2. I also cannot figure out a formula to return the right rank anyways.
Final note, the % of Revenue is a calculated field, % of Revenue:=DIVIDE([Net Revenue],[Total Net Revenue]).
I would appreciate any guidance on this question that you can provide.
Thank you,
Eli
SKU | Revenue | % of Revenue | Rank | |
AAA | 40 | 4% | 5 | |
ABC | 500 | 50% | 1 | |
CCC | 200 | 20% | 2 | |
CBA | 200 | 20% | 2 | |
DDD | 60 | 6% | 4 |
<tbody>
</tbody>
So in this example, the quickest way to get to 80% would be ABC+CCC+CBA (50%+20%+20%). Thus it takes 3 of the 5 SKUs to get to 80% of the revenue, thus the answer is 60%.
I have used the RANKX function to add the Rank column, but this may be a red herring. I thought I could use it to return the rank when the running total hit 80% or higher. But as you can see duplicates mess that up (CCC and CBA are tied so they both show a rank of 2. I also cannot figure out a formula to return the right rank anyways.
Final note, the % of Revenue is a calculated field, % of Revenue:=DIVIDE([Net Revenue],[Total Net Revenue]).
I would appreciate any guidance on this question that you can provide.
Thank you,
Eli