6 Replies Latest reply on Aug 27, 2012 12:31 PM by Wolverine .

    Using Calculated Field (Measure) as a Filter

    Wolverine .

      Fellow Community Users,

       

      I'm trying to create a filter within my workbook by the name "Actual Over Delivered" with the following code:

       

      IF [Actual Margin %]>[Budget Margin %] THEN "Over"

      ELSEIF [Actual Margin %]<[Budget Margin %] THEN "Under"

      ELSEIF [Actual Margin %]=[Budget Margin %] THEN "Equal"

      ELSE "N/A" END

       

      Now the caveat here is that Actual Margin %, and Budget Margin % are aggregated fields themselves, and Actual Over Delivered is created as a measure and not as a dimension. Bottomline: I'm not able to use it as a filter.

       

      Any pointers to how can I address this issue?

       

      Best,

      Saad

        • 1. Re: Using Calculated Field (Measure) as a Filter
          Tracy Rodgers

          Hi Saad,

           

          Create a second calculation that instead of setting the measures as strings, sets them as integers--then, the calculation will be a continuous measure.

           

          IF [Actual Margin %]>[Budget Margin %] THEN 1

          ELSEIF [Actual Margin %]<[Budget Margin %] THEN 2

          ELSEIF [Actual Margin %]=[Budget Margin %] THEN 3

          ELSE 0 END

           

          By placing this new calculation on the level of detail shelf, then right click and select Continuous. Then, you'll have the option to filter. You can still use the original calculation on the label shelf.

           

          Hope this helps a little bit!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Using Calculated Field (Measure) as a Filter
            Wolverine .

            Hi Tracy,

             

            The solution certainly worked - thank you! Which begs the next question: since the new calculation (on the level of details shelf) is all integers, the resulting filter that I see is a slider bar (0-3). Is there a way to convert it to checkboxes, or present it in a more meaningful way on the dashboard? Certainly the end users will not know what conditions do these integers 0,1,2 and 3 signify?

             

            Any thoughts on it?

             

            Best,

            Saad

            • 3. Re: Using Calculated Field (Measure) as a Filter
              Tracy Rodgers

              Hi Saad,

               

              I would recommend making legend perhaps on a different sheet and adding it to the dashboard.

               

              -Tracy

              • 4. Re: Using Calculated Field (Measure) as a Filter
                Wolverine .

                Thanks Tracy,

                Although not very favorably received by my business team, I have gone ahead with the slider functionality with added description of labels in a separate Text Box.

                • 5. Re: Using Calculated Field (Measure) as a Filter
                  Gordon Young

                  Saad, here's a trick I learned from Tableau. In the "Actual Over Delivered" calculated field, if you wrap your current calculation in a lookup function then you will be able to directly put it on the filter shelf without having a legend.

                   

                  For example, your new calc will be:

                  lookup(IF [Actual Margin %]>[Budget Margin %] THEN "Over"

                  ELSEIF [Actual Margin %]<[Budget Margin %] THEN "Under"

                  ELSEIF [Actual Margin %]=[Budget Margin %] THEN "Equal"

                  ELSE "N/A" END, 0)

                   

                  Hope that helps.

                  • 6. Re: Using Calculated Field (Measure) as a Filter
                    Wolverine .

                    Gordon, that's great. Thanks for sharing it Have you tried setting this new filter as Global? or does one have to be content with having a Local filter? Also, what does the "0" at the end signify? Can the Lookup function have any other value as well?

                     

                    Plus, there's another observation which comes into play while you've created a custom hierarchy in the worksheet. (e.g. Continent-Country-State-City-County). Now, my calculated field above (using lookup) will only compute the two types of Margins per continent (the top level in hierarchy). If my user wished to see the total count of counties (the lowest level) where a particular condition is satisfied, they'd have to navigate at the lowest level before they can enable the option of computing using county.

                     

                    Any thoughts on that?

                     

                    Saad

                     

                    Message was edited by: Saad Mazhar