All People > Americo Guazzelli > Innovation Viz > 2015 >
March >
04

**How to use the Grand Total to calculate the difference between 2 periods**

As most of you know, Row Grand Totals allows us to see the Average, Sum, Minimum and Maximum by row.

But, what happens when we want to see the difference between two periods?

For example, you want to compare the Sales of 2013 x 2012 (or any other year).

Your desire output is a table (or a bar chart) with 3 columns: 2012, 2013 and the difference:

It seemed so easy to do... so I started to try to build the red column with Tableau.

After a couple hours, I did not find any solution to achieve that output.

Even the famous table calculations (like "Difference") did not fit my requeriments.

So, I went to the Community Forums to looking for a solution.

What a found (I hope I did not miss any simpler solution) were many ways to achieve those values, but not the layout that I wanted.

By the way, I suggest you to see this thread (which received many posts with different solutions) How to hide blank columns created by table calculations.

Should I give up and use the workarounds or maybe change the layout to fit the solutions I found?

No! There is always a way (or almost always).

I decided to put away all the crazy solutions that I was trying to develop and spend sometime thinking what I was missing...

After a while....That's it! .. much simpler than I imagined.

The solution that I figured out is relative simple:

**+ **you will need two parameters (one for the first year and other for the second year)

**+ **Two calculated fields: one to filter the years and other to calculate the Sales

**+** Mark "Show Row Grand Totals" (Analysis -> Totals)

**Calculated filed # 1** - Filter (Use this field to filter the "true" values)

(year([Order Date]) = [Year 1] or year([Order Date]) = [Year 2]) and [Year 1] <> [Year 2]

**Calculated Filed # 2 - How to calculate the Sales**

if last()=first() then

//GRAND TOTAL

sum( if year([Order Date]) = [Year 2] then ([Sales]) else ([Sales])*-1 end)

else sum([Sales])

end

**Visit my personal blog ****Data Visualization & Preparation tips.**

This content has been marked as final.
Show 10 comments

- 3 things you should know when using the DataStax ODBC driver to connect Tableau with Apache Cassandra
- Enhance your chart label
- Data Extract - Aggregation
- LOD Expressions #2 - A better understanding
- Data Blending #1
- Data Preparation #1
- Grand Total Difference
- LOD Expressions #1 - Over (partition by) x LOD
- Compare a filtered dimension to all values
- DATEDIFF Semester

- Re: Need to find the difference between the 2 data variables in a dimensions
- Re: Differences between Year - Calculated Fields
- Re: How to calculate differences when using parameter-based measures and dimensions
- Re: How to calculate differences when using parameter-based measures and dimensions
- Re: Help with shapes Part 2
- Re: Difference Grand Total

## Comments