1 Reply Latest reply on May 17, 2012 11:35 AM by Tracy Rodgers

    Calculation across all measures in a view?

    Andrew Fisher

      Hi all,

       

      Am not quite certain where to start with this one.

       

      I have a view which is shows 4 measures trended over time by day. They work on a variety of different metrics (a couple are ratios and then the others are numbers of different scale). I've dropped in a reference line for each one which shows an average across the time period.

       

      What I'd like to do is create a method to highlight variance from the average if it exceeds a certain threshold.

       

      Conceptually the way I was thinking about this is to just create a calculated field which simply outputs "good" or "bad" but it's the getting to that point that is the problem.

       

      In pseudo code I'd have thought it was something like:

       

      for current measure name:

           if <value> < window_avg(<value>) then:

                "bad"

           else:

                "good"

       

      The problem of course is that Tableau wants to work on concretely named fields not on an abstract container of fields such as [measure name].

       

      What I don't want to do though is create a stack of derived fields that is essentially doing exactly the same thing each time.

       

      Any guidance on this would be greatly appreciated.

       

      Cheers

      Andrew

        • 1. Re: Calculation across all measures in a view?
          Tracy Rodgers

          Hi Andrew,

           

          Would using a parameter which references each of the measures help get the desired outcome? Then, the user can switch between the desired measures. So, create a string parameter listing the desired measures.

           

          Then, create a calculated field that references each measure. i.e.:

           

          case [Parameter 1]

          when 'Sales' then sum(Sales)

          when 'Discount' then avg(Discount)

          when 'Profit Ratio' then [Profit Ratio]

          end

           

          Then, plug this calculation into your 'good' or 'bad' calculation and show your parameter control.

           

          Hope this helps!

           

          -Tracy