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:





      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.




        • 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]



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


          Hope this helps!