2 Replies Latest reply on Aug 7, 2012 5:57 PM by David Roers

# Top n vs. Percent of Total

I know this question has been asked a bunch of times, going back to 2008, but I'm wondering if more recent versions of the product may have addressed it directly.

Scenario: My data shows crop production in the US.  I want to show the top 10 crops as well as the percentage of total (across all 146 crops).  When I don't apply the top 10 filter, the Quick Table Calculation "Percent of Total" is correct (e.g., Maize makes up 31.68% of all crops grown in the US) and the percentages total 100%.

But, when I apply the top 10 filter, the percent calculation looks only at the top 10 crops, thereby bumping up the Maize percent to 41.74%.

An extra level of complexity... I have this data for 200+ countries and with several additional measures for each crop (e.g., yield, area harvested, dollar value, etc.) over dozens of years, so creating a bunch of calculated measures is probably out of the question.  Nor can I manually 'hide' the non-top-10 rows rather than excluding them because the top 10 crops in one country usually differ from other countries, and they may change year-over-year in the same country.

Attached is a packaged workbook with just US data for a single measure.

Some help would be greatly appreciated -- even if it's just a pointer to an earlier thread that is still relevent with v 7.0.

Thanks!

• ###### 1. Re: Top n vs. Percent of Total

Hi David,

As long as the data is sorted appropriately, if the view is filtered down to a different country, the top 10 will update when hiding the values--so one recommendation would be use the index function:

index()<=10

and hide the values that are false.

Hope this helps a little bit!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Top n vs. Percent of Total

Brilliant!  Thanks, Tracy!  I always forget about Index().