3 Replies Latest reply on Nov 14, 2012 8:02 AM by Mercy Joseph

    Calculation with multiple filtered values

    Mercy Joseph

      Hi All -

      I'm trying to calculate a benchmark for individual clients within their geographical region;

      So I would like to filter my data (say Sales) for the client under review and get it as a percentage of the region it belongs to.

      ie. Sum(Sales) filtered by client divided by Sum(Sales) filtered by region.

      I would not like to hard code either value, as I would like to pull this up whenver I'm doing a review for a particular client.

      Appreciate any help -

      The following threads were helpful, but not completely applicable




        • 1. Re: Calculation with multiple filtered values
          Tracy Rodgers

          Hi Mercy,


          I've attached a sample with instructions with what I think the desired outcome is.


          Hope this helps!



          • 2. Re: Calculation with multiple filtered values
            Mercy Joseph

            Hi Tracy -

            THANK you so-o much for taking the time to set up the example response. This was a great exercise for me in many ways -taught me the power of the parameter control which I have been slow to adopt for my own work - as soon as someone shows me where it fits I can see, yes, this is exactly what I was looking to do...So thank you for that extra insight.

            I was able to set my own worksheet along the lines you've instructed, however the percent of sales looks to be showing the percent of the total company sales, and not percent of Region - the product (in my case clients) are part of a region, and I wanted to get the clients sales as a  percentage of Region's sales.

            • 3. Re: Calculation with multiple filtered values
              Mercy Joseph

              Tracy -

              Yesss! it works now!

              so I think I needed to have the Percent of Sales calculated prior to setting the Parameter control maybe? because after setting the control I was not able to correct its "Table (Down"" or "Table (Across)" etc. feature, or/and somehow it was not recognizing the Region as its partition - so I switched to a % field I had previously calculated and it works fine with that field. Feels very precarious though, as if I have to continuously monitor that it doesn;t slip off to another partition (additionally I am tracking this across 5 years).

              But I will continue to work with this.

              So again THANK you!!! It works as intended.