Grand Totaling a Month-Over-Month Change Table Calculation

Version 1

    Hi everyone,

     

    I can't take credit for this as I received the answer 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/2015

    2/1/2015

    3/1/2015

    4/1/2015

    5/1/2015

    6/1/2015

    7/1/2015

    8/1/2015

    9/1/2015

    10/1/2015

    11/1/2015

    12/1/2015

    Total

    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