3 Replies Latest reply on Apr 3, 2018 5:46 PM by Deepak Rai

# Dimension Calculation for Top 5 Excluding Others

Hello,

My data set has market share for a list of 20+ vendors. One of the vendors in the list is called "Others." I want to show the top 5 vendors, but often the Others category shows in the top 5 and I do not want this (see table example below). I cannot filter it out because that will cause errors in the total market sum and in the market share calculations.

Two questions:

1. How do I see top 5 vendors but exclude the Others category from the top 5 calc?

2. How can I get the total market line to sum all vendors in the market? Not just top 5 vendors and include the others category.

Vendor DimensionUnitsMarket Share
Vendor A (top ranked)3113.8%
Vendor B (second ranked)2310.2%
Others (third ranked)198.4%
Vendor C (fourth ranked)114.8%
Vendor D (fifth ranked)94%
Total Market225100%

Thanks

Kaitlyn

• ###### 1. Re: Dimension Calculation for Top 5 Excluding Others

Use this Filter

LOOKUP(MIN(Vendor Dimension),0)

Drag it to Filter and Take out Others

• ###### 2. Re: Dimension Calculation for Top 5 Excluding Others

Thank you Deepak - this filter works great to remove Others while maintaining the correct Total Market sum. I am still having the problem when I try to apply a top 5 filter, the grand total only shows the sum of the top 5 vendors now. I need the total market sum to show all the vendors even though the table is only displaying top 5. My approach is filtering on the vendor dimension - is there something different I can do to see only top 5?

• ###### 3. Re: Dimension Calculation for Top 5 Excluding Others

I avoided the filtering of underlying data by this filter. I think you also want that. Now in this situation what you can do is create another sheet for grand total and put these together in  a dashboard. your grandtotal needs to be static irrespective of filter so I think it will work.