3 Replies Latest reply on Feb 9, 2017 6:56 AM by Jonathan Drummey

# How to make a total of the difference calculation

Hello

I have some set of data and wanted to get a total of difference calculation based on a logic.

For example in below table, the difference of Theo SS HS is calculated only if there is a different between PDT and previous PDT.

At logistic ref level, it seems correct however when I put the grand total, the total of Difference Theo SS HS is not correct.

This is my Grand Total expectation, where in Jan-Mar = 0 and only April I should see the difference.

However, this works only if I filter the logistic ref and I want to consider all the logistic ref.

Can somebody advice me , how to calculate the difference in grand total ?

I'm attaching the sample.

• ###### 1. Re: How to make a total of the difference calculation

Would this work for you?

IF [In Grand Total] THEN

IF FIRST()==0 THEN

WINDOW_SUM(

SUM([Difference Theo SS HS]) - LOOKUP(SUM([Difference Theo SS HS]), -1)

)

END

ELSE

SUM([Difference Theo SS HS]) - LOOKUP(SUM([Difference Theo SS HS]), -1)

END

• ###### 2. Re: How to make a total of the difference calculation

That does not work. I have to compare first if there is a change in the PDT then I after calculate the difference of Theo SS HS.

The only workaround which I can see right now is, I have to maintain another field which is the previous month PDT and previous Theo SS HS. Then it will work.

But I'm looking for the simple one, as maintaining another field will increase the size of DB.

• ###### 3. Re: How to make a total of the difference calculation

I suspect that what's happening here is an order of operations issue with regards to how Tableau computes grand totals.

In the detail rows (for each Plant Code and Logistic Ref) Tableau is computing the PDT, previous PDT, and previous Theo SS HS at the level of detail of Plant Code & Logistic Ref for each month.

In the grand total row (GT) Tableau is summing the PDT across *all* values of Plant Code & Logistic Ref, then computing the table calculation results on that. Since the SUM(PDT) is different for each month in the GT the following calculations are returning different results. In other words, Tableau's default grand totals are computed at a separate level of detail, you can read the posts at Grand Totals and Subtotals | Drawing with Numbers for lots of gory details.

I'm guessing that you want the previous Theo SS HS result in the GT to be the sum of the previous Theo SS HS results in the detail rows? Ideally we'd be able to choose to do that but it doesn't work for table calculations, vote up https://community.tableau.com/ideas/1232 if you'd like. So we have to do a workaround. The following screenshot (and attached workbook) uses the replicated dimension technique from the http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2 with the complication that we need to use nested table calculations because the original measures are table calculations, and turn off mark stacking as described in Customizing Grand Totals in Tableau v8 – The Stacking Snag:

Let me know if you have any questions!

Jonathan