3 Replies Latest reply on Apr 24, 2012 5:55 AM by Jonathan Drummey

    Trouble with Grand Totals

      Dear Community,


      Can anyone help with a simple calculation that I am stuck with please?


      I am inner joining 2 tables that have a many to one relationship.  The 1st table holds transactional data and many records per customer.  The 2nd table holds pretty static data such as region, credit limit etc. and there is only a single record per customer.  I’ve attached a simplified example.


      My starting point for the join is the transactional table and as a result I end up with the relevant credit limit duplicated for each transactional record.  I have therefore created a calculated field called ‘Credit Limit – calculated’ which is meant to correct for this duplication.


      I want to be able to show both sales and credit limit data dynamically filtered by a number of different dimensions (not included in the example).  At an individual transaction level the calculation works; at a customer sub-total level it still works; however, at the grand total level it falls apart.


      I tried to reverse engineer the calculation and I can see that the problem relates to the number of transactions – set the table to show multiple customers each with exactly the same number of transactions and the calculation holds true but vary the number transactions and the results begin to drift.  The more data I use, the greater the variance.


      I realise that I could fix this quickly by creating a separate data connection to the 2nd table and my data blending but preference is to avoid this if possible.


      I am currently going round in circles, so it would be great if someone could set me straight.


      Thanks in advance. Pete