7 Replies Latest reply on Nov 16, 2011 8:07 PM by Michael Nealey

# Calculations to affect 'others' when not in a Top N Ranking

I have a parameter that allows for selection of a top 'n' based on the overall revenue or units. When not in the top 'n', the item would go into 'others'. This is working fine for me on a simple table calculation like overall revenue. However, I have tried to create a calculation such as 'growth rate', or 'market share' and the calculation for the 'others' seems not to work - rather it seems to take the next value (i.e. #11, which is outside of the top 'n' (10)).

I'm not sure how to resolve this problem...

(I have extracted a portion of the data so it is not the entire set).

• ###### 1. Re: Calculations to affect 'others' when not in a Top N Ranking

What exactly would you like the top n of?

Total of selected measure for both years? or just 2010, or just 2009? or or top n based on something other than the sum of the selected measure?

As for the four sheets with the bars, is there a reason you want them as separate sheets? I think it would be better if they were just a single worksheet, so if you want the top 100, there would just be a single scroll bar. You can have the measure title at the top, and axis at the bottom.

• ###### 2. Re: Calculations to affect 'others' when not in a Top N Ranking

The way it is set up now is that I have the top n set on the sum of the selected measure across the 2 years...(currently set up as top company, but I would also like to do top product, top country)

Since I only have 2 years of data, growth and market share growth would not have any data for 2009.

I would like a single worksheet as you suggest, but was not sure how to do this...also, would it mean the data needs to appear as a table rather than with bars?

• ###### 3. Re: Calculations to affect 'others' when not in a Top N Ranking

Joe, this is terrific. I have seen the calculations that split measures up by years, as you have done, but not been able to do it to date. I will explore what you have done a little more and get back to you with any questions...but at first glance, it looks great!

• ###### 4. Re: Calculations to affect 'others' when not in a Top N Ranking

attached is my quick unpolished remake. Does this do what you are looking for?

1 of 1 people found this helpful
• ###### 5. Re: Calculations to affect 'others' when not in a Top N Ranking

My next challenge is this: when I select At the Level to be 'country', and let's say I click on USA, I would like to be able to see a breakdown of the top companies with their share, growth etc for USA in a separate chart. Similarly, I'd like to be able to see a breakdown by product.

By the same token, if I selected At the Level to be 'company' or 'product', I'd like to be able to drive into a by country view...etc.

I have started this here...as you can see, it works OK when I start with At the Level set at country...if I select USA, it breaks down nicely.

When I change At the Level to, say, 'company'...share and share growth do not figure out correctly...