1 Reply Latest reply on Feb 26, 2013 8:47 AM by Joshua Milligan

    constant referance?

    . Jasonz

      Hi folks,

       

      Is there a way to calculate a constant group average even if there are changes within a graph?

       

      Scenrio:

       

      group X

       

      has 3 people in it

       

      the average monthly rate they do tickets is 13 for the group, now I create a referance line to show that fact. However when I apply a filter to focus on say 1 of those individuals then the average changes (I expect this reaction) ... what I need is it NOT to change from 13 regardless of the number of people selected. Is there a way to use a constant equation to solve this? I cannot force it as 13 because the monthly average will change monthly.

       

      Thanks a bunch

       

      JZ

        • 1. Re: constant referance?
          Joshua Milligan

          Jasonz,

           

          I've attached a workbook that shows a possible way of solving these types of problems.  The basic idea is to use a table calculation as the filter.  The reason this works is that the average calculation is done at the data source, but the table calculation filter is performed after the data is returned to the data engine.  So the average reflects everything, even if they are subsequently filtered.

           

          Please take a look and let me know if you have any questions.  Also feel free to upload a packaged workbook of your own if you'd like someone to look at your specific case.

           

          Joshua