i have a problem in getting the average of the subtotals of my data group.
i have 2 groupings. product brand & product category. under each category, i have 3 columns to get the no. of stocks, qty.sold and the %sold.
to get the %sold, my formula is qty.sold/stocks. i used this formula for the subtotals and grand total as well. so i now have the subtotal and grand total for %sold. but i also would like to get the grand total using this formula: subtotal of %sold divided by the no. of products. just like getting the average. how can i do this? i can't use the "insert summary" command coz the option is missing. i tried creating a formula "sum(subtotal %sold) / no. products" or "average(%sold subtotal), but an error "this field cannot be summarized" appears.
For your needs, it looks like you can create a calculated field using the sums that you already have in your category footer. When building this sum-of-sums, pick the already existing summary fields from the field list so that the parentheses get placed correctly. Something like:
i already got the sum of all the subtotals using the techniques you posted. but there's one thing more i'd like to do. i'd like to get the average of all the subtotals i've just summed up. or just divide the sum to the no. of group. how will i do this?