11 Replies Latest reply on Jan 14, 2020 1:27 PM by Allison Bockman

    Use 2 dimensions to sum columns separately to calculate delta

    Ben Kronk

      I want to make a chart with 3 levels: CAGR (circle), 1st year production (width of bar) and production delta from 2020 – 2025 (height of bar).  The viz looks the way that I want it too, but the numbers are not correct.

       

      new tableau example.PNG

      My issue (in the attached workbook, the viz is different than the one I created and shown above) is that I can’t determine how to sum the volume by the sales parent by an individual year, for example Company A with 10,000,000 production for only 2020.  And since I can’t sum by year, I am left with the width of each bar being the total across the years in the dataset for each sales parent, and the years range from 2001 – 2031.

      This results in no bar height because I can’t determine the delta and 0% CAGR because I can’t separate the years from each other.

      For the first year, 2020, I have the following calculation (named First Value Sales Parent in workbook):

           {FIXED [VP: Sales Parent], [Years]=#2020# :

           SUM([Volumes])}

      For the last year, 2025, I have the following calculation (named Last Value Sales Parent in workbook):

          {FIXED [VP: Sales Parent], [Years]=#2025# :

           SUM([Volumes])}

      My calculation for the delta is:

           [Calc - Last Value Sales Parent] - [Calc - First Value Sales Parent ]

      My calculation for CAGR is:

           {FIXED [VP: Sales Parent] :

          AVG(

              POWER(

                  [Calc - Last Value Sales Parent] / [Calc - First Value Sales Parent ],

                  1 / DATEDIFF('year', [Calc - 2020], [Calc - 2025])

              ) - 1

          )

           }

      I have tried multiple permutations of these formulas but no luck – still lots to learn about calculations/LOD/AGG , etc. – thanks for your help!

       

      Message was edited by: Ben Kronk