9 Replies Latest reply on Aug 4, 2016 2:06 AM by Andrew Watson

# How to calculate the difference of column grand total

Hi all,

I want to create a new row that shows me the difference between the Final Forecast and Qty Ordered for just the Grand Total.

I tried creating a calculated field to do this but I get a monthly difference for each region as well as for the Grand Total. Is there a way I can see the Difference calculation only under Grand Total?

Thank you

• ###### 1. Re: How to calculate the difference of column grand total

Hi.

It's no possible to do it.

You'll need to build another sheet for this calculation.

• ###### 2. Re: How to calculate the difference of column grand total

Hi Luciano,

Thank you.

Can you please elaborate on what you mean by building another sheet for this calculation?

• ###### 3. Re: How to calculate the difference of column grand total

Actually this might be possible using SIZE(). If you create a calculated field with the formula SIZE() and put it in the table you should see the grand total has a different 'size' than the monthly values. In which case you could modify your difference formula to be something like:

IF SIZE() = 1 THEN DifferenceFormula END

This will return a blank (null) for those without a size of 1 - which hopefully in you case is the month totals.

See attached in 9.3 for an example.

1 of 1 people found this helpful
• ###### 4. Re: How to calculate the difference of column grand total

I didn't know that. Very useful.

• ###### 5. Re: How to calculate the difference of column grand total

Hi Andrew,

My difference formula is

[Final Forecast]-[Qty Ordered]

And my difference formula with size is

IF SIZE() = 1 THEN SUM([Final Forecast]) - SUM([Qty Ordered]) END

This is the result I'm getting. Is there somewhere I'm going wrong?

• ###### 6. Re: How to calculate the difference of column grand total

Andrew's seems like yours:

Calculation done just to grand total column.

In your print we can't see grand total. Is it calculating for grand total?

• ###### 7. Re: How to calculate the difference of column grand total

As Luciano says, please include the grand totals in your screenshots so we can see what's happening. Thanks

• ###### 8. Re: How to calculate the difference of column grand total

Andrew,

Your file is calculating grand totals for rows, whereas I am looking to calculate the difference between column grand totals.

Referencing the screenshot Luciano posted above, I don't want SalesMinusProfits to appear for Central, East, South, and West. I want it to only appear in the Grand Total row. In other words, I want the circled section to appear but not the crossed out rows.

• ###### 9. Re: How to calculate the difference of column grand total

Whether the grand totals are for rows or columns shouldn't make much difference. Can you also drag the INDEX calculation into the view so we can see the index number for all cells including the grand total? Feel free to post a twbx as that'll cut down most of the guess work.