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):



      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.