2 Replies Latest reply on Aug 20, 2016 8:41 AM by Nicholas Ng

    Subtotals on dimension/measure based on calculated field

    Nicholas Ng

      I am trying to create a viz on product sales where products are categorized based on year-on-year (YoY) decline/gain (products where sales decline are placed in a "decliner" category, and vice versa). While this is easy enough, the stumbling block has been calculating subtotals as I would like Tableau to give me the aggregated total decline/gain in sales of the products declining/gaining in sales (essentially, this second part is a "2nd-pass-through" aggregation, based on categories created on the 1st pass through aggregation).

       

      To illustrate my point, the following has been created using the sample superstore data:

      In the table, the measure used is a calculated field of YoY decline/gain in sales (Sales %growth = sales in 2012 / sales in 2011 - 1), and D vs. G is calculated as:

      Unfortunately, as seen in the first screenshot, this does not give the subtotals that we are looking for.

       

      On the other hand, I have tried to use an LOD calculated field and calculated set as well. Both methods give a similar result, but if I were to filter the data on a dimension outside of the view, the grouping becomes incorrect:

      At this point, I am wondering if this is possible, or am I asking too much of Tableau? Any advice would be appreciated!