4 Replies Latest reply on Oct 26, 2017 6:24 AM by Shawn Wallwork

    Is there a way to use the name of the measure on the Text shelf in a calculation?

    Ryan Eavey

      Hello,

       

      Is anyone aware of a way that I can dynamically use the name of the measure on the Text shelf in a calculation?  The workbook I'm dealing with needs to have a table similar to the attached view.  Each column is the results for a month and each month's result must be color coded to show whether or not it hit target.  In order to get the colors to properly calculate I've created a separate color calculation for each month that looks only at the results for that month.  Doing this results in many redundant color calculation measures, one for each month. 

       

      I'd like to be able to write a single color calculation that would use the name of the measure on the Text shelf in the calculation. 

       

      So instead of having multiple calculations, one for each month like this:

      Color Calc - 01 Jan = IF AVG([12 Month Calc - 01 Jan])>3.5 THEN "MISSED TARGET" ELSE "HIT TARGET" END

      Color Calc - 02 Feb = IF AVG([12 Month Calc - 02 Feb])>3.5 THEN "MISSED TARGET" ELSE "HIT TARGET" END

      Color Calc - 03 Mar = IF AVG([12 Month Calc - 03 Mar])>3.5 THEN "MISSED TARGET" ELSE "HIT TARGET" END

       

      I would instead be able to have a single calculation for all months like this:

      Color Calc = IF AVG([Measure Name on Text Shelf])>3.5 THEN "MISSED TARGET" ELSE "HIT TARGET" END

       

      Any ideas on how this dynamic calculation can be accomplished?

       

       

      Below is an image of the desired type of view using superstore, and a 9.2 workbook is attached with the same.

       

      Thank you,

      Ryan