3 Replies Latest reply on Jul 26, 2012 9:18 AM by Jonathan Drummey

    Calculating an average of an average with Grand Total

      I have a set of data that looks like:

       

      ProductCriteriaMet
      AppleCriteria 11
      AppleCriteria 21
      AppleCriteria 30
      AppleCriteria 41
      AppleCriteria 50
      BananaCriteria 10
      BananaCriteria 21
      BananaCriteria 30
      BananaCriteria 40
      CherryCriteria 11
      CherryCriteria 21
      CherryCriteria 31
      CherryCriteria 41
      CherryCriteria 51

       

      I want to summarize the data above to show the average % of criteria for each product that is Met (or = 1).   I can create the following summary with a Grand Total average % as follows :

      Avg
      Apple60.0%
      Banana25.0%
      Cherry100.0%
      Grand Total64.3%

       

      The problem is that the Grand Total is not giving me the average of the averages listed.  It is recalculating the average based on the lowest level of data across the products as the total number of  criteria met/total number of criteria so it is calculating 9/14 = 64.3%.  I want it to calculate the  average of each product's average so average(60, 25, 100) = 61.7%.   How can I do this with a Grand Total or a table calculation? 

      Thanks for the help!