3 Replies Latest reply on Feb 9, 2012 8:25 AM by Andy Cotgreave

    How to use filter for specific measures used in a calculated field?

      Hi, 

      I am creating an aggregated calculated field which requires me to have a grand total that should not get filtered but the other measure should get filtered based on a quick filter.Please help me with a work around.

        • 1. Re: How to use filter for specific measures used in a calculated field?
          Nathan Krisanski

          Hi Ekansh,

           

          The only way to "filter" results from a view but have them still used in a total is the use the "hide" function.

           

          Without details its hard to explain for your specific example, but I'll try to outline the procedure and I hope you can get it to work for you.

           

          Instead of using a filter for some values, create a calculated field that will return either a true or false result.

          I like to do it with a "show" or "hide" text result so that it makes sense on the filter shelf.

          eg. [ShowMyState] => IF [State] = "VA" then "Show" else "Hide" end

           

          Then place this calculated field on your worksheet, row or column, based on your table or view.

           

          You should see your data seperated into two sections (and a grand total at the end). The Show pane is the data you want to show on the view and the "Hide" pane is the data you want "filtered out". The total should be the whole dataset total as expected.

           

          Now right click on the "Hide" pane and select the hide option. This will hide it from view, leaving the data you want to see and the totals including both.

           

          NOTE: You can then use a parameter to allow people to change this "filter". EG [SelectState] parameter which can be set to the state. Then change your calculated field to be IF [State] = [SelectState] then "Show" else "Hide" end. Now when users change the parameter value, it will change the data shown just like a filter.

          • 2. Re: How to use filter for specific measures used in a calculated field?

            Nathan ,

            As you can look into the data if I want to calculate discount rate and I select clearance in the quick filter ,it would give me a very wrong picture as

            discount rate is defined as discount /(sales +discount) .I want the sales should be the total of all the observations even if the filter is applied.

            I hope this would help you to get a better idea about the issue I am facing

            • 3. Re: How to use filter for specific measures used in a calculated field?
              Andy Cotgreave

              One of our true gurus (Wilson Po, for those who know him) came up with this brilliant solution to create a "Show/Hide" filter:

               

              Create a Table Calc version of [Reason for discount]:

              Lookup(MIN([Reason for discount]),0)

              Then set this field in the level of details, set it to compute along the [Reason for discount] dimension, and then apply the table calculation field again to the filters to create a quick filter. This quick filter will  now hide as oppose to filter, which will retain your totals regardless of what selection is displayed.