
1. Re: Sub Total values are wrong for Table calculation
Ben Whitaker Oct 11, 2016 8:23 AM (in response to Madhumitha Palanisamy)1 of 1 people found this helpfulHello,
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 
2. Re: Sub Total values are wrong for Table calculation
Madhumitha Palanisamy Oct 12, 2016 12:30 AM (in response to Ben Whitaker)Hi Ben
Thank you for your reply.
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
Ben Whitaker Oct 12, 2016 12:51 AM (in response to Madhumitha Palanisamy)Are you able to upload an example workbook?
Thanks,
Ben

4. Re: Sub Total values are wrong for Table calculation
Madhumitha Palanisamy Oct 12, 2016 4:21 AM (in response to Ben Whitaker)Hi
I have attached the workbook.

Sample.twbx 15.1 KB


5. Re: Sub Total values are wrong for Table calculation
Ben Whitaker Oct 12, 2016 5:34 AM (in response to Madhumitha Palanisamy)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

Sample.twbx 15.3 KB


6. Re: Sub Total values are wrong for Table calculation
Madhumitha Palanisamy Oct 12, 2016 9:54 PM (in response to Ben Whitaker)Thank you Ben.
It was very helpful.