5 Replies Latest reply on Aug 25, 2016 8:49 AM by Harry Lawrance

    Filtering an aggregated group of measures

    Harry Lawrance

      Hello, I have a question that may be quite straightforward, but I can't figure out how to do it. Any help would be appreciated.

       

      I have the following worksheet as shown in the screenshot below. In the measures box you can see I have 6 goal completion measures (goal 1 completions, goal 4 completions etc) taken from my Google Analytics data. I have created a calculated field that sums up each of these goal completion values. This is the measure named 'sum of all goals' which is then plotted on the chart:

       

      Untitled.png

       

      Now what I want is a filter from which I can choose 1 or more of my goal measures and have these update on the chart - or of course I can choose to see the sum of all the goals as i currently have.I thought I'd just be able to right click on my 'sum of all goals' calculated measure and choose 'show filter' - but this just gave me a filter for the range of values (as can be seen in the top right of the screenshot above). What I wanted was something like this (excuse my awful MS Paint mock-up):

       

      Any idea how I would go about this?

       

      Thanks, H

        • 1. Re: Filtering an aggregated group of measures
          Naveen Agarwal

          Since the sum of all goals is a calculated field, when you use this as a filter, it will only show calculated values to filter from.

           

          Looks like you want to be able to select 1 or more of the Goal Completion measures and show them on your chart while also showing the Sum of Goals. Is that right?

           

          The question is: do you want the Sum of Goals to include only the selected Goal Completion Measures or does it always need to sum all of them at once?

           

          In the first case, you would need to have a dynamic calculation (using LOD) to include only selected Goal Measures. In the second case, you can probably use your current calculation, which I assume simply sums up all of the Goal Completion Measure values.

           

          Are you able to post an extract of your data?

          • 2. Re: Filtering an aggregated group of measures
            David Li

            Hi Harry! You're running into this problem because filters act on individual records (except for table calculation filters). This means that you can't use normal filters to filter out specific Goal Completions, because they're all attached to the same record.

             

            Arguably, the best way to get around this this would be to normalize (unpivot) your data so that you only have one measure field for goal completions instead of 9. Tableau is designed to work with normalized data, anyway. This solution assumes that unpivoting your data source makes conceptual sense and that you have the access to do so, of course. Then, you'd be able to filter on whatever dimension separates the unpivoted fields.

             

            If you can't unpivot your data, another option is to use parameters. You could set up 9 Boolean parameters, each one corresponding to one of the Goal # Completions. Then, you'd just chain IIF() statements together, kind of like this:

            IIF([Include 1], [Goal 1 Completions], 0) + IIF([Include 2], [Goal 2 Completions], 0) + IIF([Include 3], [Goal 3 Completions], 0) + ...

            And so on.

            • 3. Re: Filtering an aggregated group of measures
              chris.moore.11

              I think the best way would be to un-pivot your data like David said.

               

              Set it up like this...

              long.PNG

               

              Instead of like this...

              wide.PNG

               

               

              Then you can sum "Value" and filter on "Goal #".

               

              In that scenario if you also want to include individual goals on the chart you could make a calculated field for each goal that is basically..

              Goal 1.....(if [Goal #] = 1 then value else null end)

              Goal 2...(if [Goal #] = 2 then value else null end)

              Goal n...(if [Goal #] = n then value else null end)...

               

              The issue with using a parameter (I think) would be you cant select more than one value with a parameter.

              • 4. Re: Filtering an aggregated group of measures
                Harry Lawrance

                Thanks for your answer Chris - however I don't think I can change how these are setup can I? They are just pulled from Google analytics so I assume that the way they are setup is the way they are setup?

                • 5. Re: Filtering an aggregated group of measures
                  Harry Lawrance

                  Hi Naveen - thanks for your answer. To respond to your questions:

                   

                   

                   

                  Looks like you want to be able to select 1 or more of the Goal Completion measures and show them on your chart while also showing the Sum of Goals. Is that right?

                   

                  A: Sort of. If I select goal 1 then I want just goal 1 completions shown on the chart. If I select goal 1 and goal 2 then I want the sum of these 2 goals shown on the chart. If I select all of the goals then I want the sum of all of the goals shown on the chart.

                   

                  Are you able to post an extract of your data?

                  A: Yes I can do. Is it a .twbx file you would need?