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.

• ###### 1. Re: Trouble with Grand Totals

Hi Pete,

Could you re-do the attachment as a twbx please?

Cheers

Siraj

• ###### 2. Re: Trouble with Grand Totals

File updated to a TWBX.  Thanks for pointing this out Siraj.

• ###### 3. Re: Trouble with Grand Totals

Tableau's built-in totals (sub totals and grandtotals) are generated by separate calculations from those that generate the marks being displayed in the column/row. These total calcs are performed at the level of detail of the total, which of course gets less granular for the further you go up in the hierarchy of the view. Therefore, calculations that depend on certain fields being in the level of detail start giving strange results.

There are two methods to get a grand total that works for you: One is to create a separate view to calculate the total and put that in a dashboard with the original view, the second is to use a workaround to hack your own grand total values, I did a writeup in this post: http://community.tableau.com/thread/116854. However, I don't particularly recommend the latter solution because it's using undocumented functionality in Tableau that could change in a future release.

Jonathan