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

    Top n vs. Percent of Total

    David Roers

      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%.

      Snap4.png

      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%.

       

      Snap5.png

       

      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!