5 Replies Latest reply on Mar 1, 2016 4:08 PM by Patrick Van Der Hyde

# Grand Totaling a Month-Over-Month Change Table Calculation

Hi everyone,

I'll admit, this isn't a question so much as it is a sharing of an answer I received from the Tableau Developers tech support (thanks Asher) for an issue I think might be common. Here was the issue:

I had connection to a Tableau dedicated data-source on my company's server that had Net Revenue information spread across monthly intervals. The goal was to calculate the month-over-month Net Revenue change and then total that out in a grand total column. The issue was, because month-over-month change was not a hard-coded value in the data-set, rather a table calculation, this prevented a grand total column from easily being added. I've pasted the test data I came up with below. The month-over-month change column was the table column using the formula ZN(SUM([Monthly Revenue])) - LOOKUP(ZN(SUM([Monthly Revenue])), -1):

Metric
1/1/20152/1/20153/1/20154/1/20155/1/20156/1/20157/1/2015
8/1/20159/1/201510/1/201511/1/201512/1/2015Total
ACME Company (Net Revenue)2,055,6772,713,3882,795,4211,588,6552,153,3221,327,7782,944,8601,507,2872,290,2171,746,1091,500,0001,023,000
ACME Company (Net Revenue MoM Change)657,71182,033(1,206,766)564,667(825,544)1,617,082(1,437,573)782,930(544,108)(246,109)(477,000)(1,032,677)

Rather than go into the details about how Asher did it, I'll just post his response & the workbook he sent me back below. ****NOTE: B/c I'm not looking for an absolute change, I had to slightly augment the formula he used to multiply the end result by -1. You can test it out in both Excel and Tableau to see what I mean. I'll bold the one change I made to the received copy below.

______________ From Tableau:

Normally calculating a grand total of a table calculation is difficult.  It almost always requires coming up with a non-table calculation for the total to do so.  Luckily, this instance there is a non-table calculation possible using LOD expressions.

The grand total for month over month difference can be thought of as one big long chained equation:

Jan - Feb + Feb - Mar + Mar - Apr… etc.

As you can see in the short example above, the middle values all cancel out.  Leaving us with Jan - Apr.   Or more formula oriented, the value of the minimum month - the value of the maximum month.  In order to cohort those specific values in the grand total calculation we need to use fixed LOD expressions for { MIN(Month) } and { MAX(Month) }.  The final calculation is then:

IF SIZE() > 1 THEN [MonthOverMonthChange] ELSE ((  ZN(SUM(IF [Month] = { MIN([Month]) } THEN [Monthly Revenue] END)) - ZN(SUM(IF Month = {Max([Month])} THEN [Monthly Revenue] END))  ) *-1) END

______________

Please see the attached workbook for the original solution I got, noting that it does not contain the change I made above.

Enjoy.

Joshua

• ###### 1. Re: Grand Totaling a Month-Over-Month Change Table Calculation

Thank you for sharing Joshua.  I am going to mark your post as "assumed answered" since it isn't so much a questions.

Patrick

• ###### 2. Re: Grand Totaling a Month-Over-Month Change Table Calculation

Patrick,

When we come across informational posts like this, is there a way that we could mark them as being an instruction or a how to and not a question?

-Regards,

Patrick B

• ###### 3. Re: Grand Totaling a Month-Over-Month Change Table Calculation

I can't change it but I would like to encourage the author to think about posting this in the  The specified item was not found. or Workbook & Calculation Library.  In this case, there is an example workbook so the workbook library would be a better location.  Then joshua.rauh could create this post as a document and others could contribute/update over time.

Patrick

1 of 1 people found this helpful
• ###### 4. Re: Grand Totaling a Month-Over-Month Change Table Calculation

Hey everyone,

I've updated this in the calculation reference library: