6 Replies Latest reply on Oct 12, 2016 9:54 PM by Madhumitha Palanisamy

# Sub Total values are wrong for Table calculation

Hi

I have a table like this,

 Id Date Sales 1 01/03/16 2000 1 02/04/16 1 03/05/16 1 04/06/16 2 05/11/16 3000 2 10/11/16 2 10/20/16

I want to add a new column by dividing the sales by count of same id (For Example 2000 by 4 and 3000 by 3) and need to fill the column.

I can able to get a table like this,

 Id Date New Sales Sales 1 01/03/16 500 2000 1 02/04/16 500 1 03/05/16 500 1 04/06/16 500 2 05/11/16 1000 3000 2 11/11/16 1000 2 12/20/16 1000

I have done this using the calculated field.

But when I am displaying the month wise total for New Sales. It is showing as 500 for Id 1 and 1000 for Id 2 (i.e. the total is based on the first cell of the column). But for sales it shows correct total as 2000 and 3000.

 Id Date New Sales Sales 1 01/03/16 500 2000 02/04/16 500 03/05/16 500 04/06/16 500 Total 500 2000 2 05/11/16 1000 3000 11/11/16 1000 12/20/16 1000 Total 1000 3000

• ###### 1. Re: Sub Total values are wrong for Table calculation

Hello,

Without knowing the details of your calculation I would guess it is because of the way the totals in Tableau. They don't simply add up the figures you have in your view. The total recalculates as if you have removed the dimensions from your view. So to get your total right I would try a FIXED LOD, to fix the "New Sales" measure to the dimensions you have in your view.

{ FIXED [Id],[Date] : sum([New Sales])}

Apologies if I have misunderstood.

Thanks,
Ben

1 of 1 people found this helpful
• ###### 2. Re: Sub Total values are wrong for Table calculation

Hi Ben

Actually my table calculation is,

New Sales:

if isnull ( lookup ( sum ( sales ) , 0 ) ) then previous_value ( sum ( sales ) / ( count of days for a particular Id ) ) else

lookup ( sum ( sales ) / ( count of days for a particular Id ) , 0 )

I cannot use this in LOD (sum( New Sales )) because it is already an aggregated field.

I want total 2000 instead of 500 for ID 1 and 3000 instead of 1000 for ID 2.

• ###### 3. Re: Sub Total values are wrong for Table calculation

Are you able to upload an example workbook?

Thanks,

Ben

• ###### 4. Re: Sub Total values are wrong for Table calculation

Hi

I have attached the workbook.

• ###### 5. Re: Sub Total values are wrong for Table calculation

So this is a bit tricky due to the use of the previous_value table calc you are using. The calculation I have come up with is:

IF MIN([Date])!= MAX([Date]) THEN

SUM([Sales]) //for the grand total rows

ELSE

//Actual data

IF ISNULL(SUM([Sales])) THEN PREVIOUS_VALUE(SUM([Sales])/SUM([No of Days]))

ELSE SUM([Sales])/SUM([No of Days]) END

END

So the first if statement (IF MIN(MONTH([Date]))!= MAX(MONTH([Date]))) is giving you your grand total. As I mentioned before the Tableau totals recalculates as though the dimensions are removed from the view, so the first condition will always be true for the totals rows. The nested if is just the calculation that you had in your original workbook. Although I am not completely sure why you need to use the lookups you were using, so I have removed them. But if there was a specific reason, add them back in - the calc will still work.

Below is a screenshot of the results - I have also attached a packaged workbook.

Hopefully it makes sense.

Maybe take a look at this idea: https://community.tableau.com/ideas/1232 and vote it up.

Another useful link is: Customizing Grand Totals – Part 1 | Drawing with Numbers

Cheers,

Ben

• ###### 6. Re: Sub Total values are wrong for Table calculation

Thank you Ben.