3 Replies Latest reply: May 2, 2011 9:05 AM by Martina Brenner RSS

    Correctly Calculating Pivot Totals based on Expressions


      I posted this as part of a rather long chain that a few users have been helping me with, but have reopsted this as a new post as I think a lot of people may not get to the 28th post in the other chain. Apologies if this is a breach of forum ettiquette.

      I also need to know if it is possible to to do the following:

      I have an expression that calculates volume utilisation by essentially taking the volume of a product in a location and dividing it by the total available volume of the location and multiply it by 100. Effectively percentage utilisation.
      When the pivot table calculates a subtotal, how do I tell it to sum the product volume in all locations and divide by the sum of available volume of all locations? Instead of summing the records above?

      error loading image

      From this example spreadsheet, I would expect QV to correctly calculates the 4.5cbm as it can see that the first two lines are for the same location and it should not double count them. The product count and the used volume are also correctly counted, but the Utilisation which should be 66.89 ((3.01/4.5)*100) is incorrectly calculated as the sum of the lines above to give 200.67.

      Any ideas?