8 Replies Latest reply on Apr 2, 2014 3:21 PM by Mark Isaacs

# How Can I Create a Measure from a Quick Table Calculation Result

Help.

I want to create a scatter plot that compares Market CAGR (y-axis) versus Product CAGR (x-axis) - ideally with the plot sized by the sum of product sales (but this last point is of less concern).

CAGR is not a field in the source data.   It has to be calculated in Tableau.

I can create a table to generate the CAGR for each Product and/or Market using Tableau's built in CAGR quick table calculation.   How do I convert the results of the table calculation into a measure I can use for each axis of the scatter plot?

Thanks.

• ###### 1. Re: How Can I Create a Measure from a Quick Table Calculation Result

If you click on "Edit Table Calculation..." and click on "Customize..." in the Table Calculation window you can assign a name to the calculated field.

Hope this helps...

• ###### 2. Re: How Can I Create a Measure from a Quick Table Calculation Result

Unfortunately, I tried this approach.   I am a neophyte to Tableau formula construction.   The generated formula does not produce the same results as the Quick Table Calculation - even when everything else is equal.   It does not even produce 0% in the start year.

The Tableau generated formula from the Edit Table Calculation is:

• POWER(ZN(SUM([Amount]))/LOOKUP(ZN(SUM([Amount])), FIRST()),ZN(1/(INDEX()-1))) - 1

I see the POWER formula embedded in the calculation.  I understand ZN addresses the NULL values.  I do not understand the INDEX reference.

I am working from the assumption that the formula to calculate a CAGR for 2013-2019 growth would be as follows:

• POWER(SUM([2019])/SUM([2013])^(1/6) - 1

What am I missing?

• ###### 3. Re: How Can I Create a Measure from a Quick Table Calculation Result

The INDEX reference is basically the offset in your array of values.  So, if your years are 2013 to 2019, thinking of this as an array of seven values, the values of INDEX() will be 1 for the 2013 value, 2 for the 2014 value ... 7 for the 2019 value.

Your formula POWER(SUM([2019]/SUM([2013])^(1/6) - 1 is the value of the formula for one specific value in this array.  The "(INDEX()-1)" term in the Quick Table formula would equal 6 when it calculates the value for the year 2019 (the seventh value in the array), which corresponds to the denominator in the "1/6" portion of your explicit formula for the year 2019.

The FIRST() parameter in the LOOKUP function gets the first value in the array for the denominator, which corresponds to your "/SUM([2013]".

I don't see why the Quick table formula wouldn't produce what your expecting.  Can you perhaps post a small workbook with some data and your formula?

• ###### 4. Re: How Can I Create a Measure from a Quick Table Calculation Result

Mark,

Thank you for your assistance.  It is greatly appreciated.

I am failing at the logic structure of the formulas.   The attached image depicts what I want to generate.

I need a Measure that has a CAGR for each Budget Line (Dimension).   I then need a Measure that generates the CAGR for each Market Segment (Dimension) - i.e., a group of Budget Lines.

The Tableau generated CAGR calculation only works when I recreate a Table with a Dimension vs. Year.  I need the results to be the Measure, not the calculation - just like each Budget Line has an amount associated with it for each given year of the forecast.

I hope that makes sense.

Steve H.

• ###### 5. Re: How Can I Create a Measure from a Quick Table Calculation Result

Steve,

Is there an image to be uploaded?  I don't see one anywhere.

• ###### 7. Re: How Can I Create a Measure from a Quick Table Calculation Result

Sorry, here is an image that depicts what I am trying to do.   It's not the table calculation that I want.  I want the results of the table calculation to be used as a Measure - in total (budget line) and in sum as a subset (market segment).

I hope this helps, because I am stuck.

• ###### 8. Re: How Can I Create a Measure from a Quick Table Calculation Result

Hi Stephen,

OK, I see what you're trying to do.  It's more complex due to the 2-step nature - first calculate the two sets of CAGR values, then use those (new) sets of values in a scatterplot.  Offhand I don't know if this can be done.

In a sense this is similar to a question I had posted not that long ago, and a great answer was provided by Joshua Milligan.  He called my question a form of "slicing by aggregate', and if you view the calculating of the CAGRs as a form of aggregation then it may apply to your situation too.  Have a look at his site's article on  Slicing by Aggregate | VizPainter  Perhaps something there will be help.

Good luck!

Mark