4 Replies Latest reply on Oct 24, 2016 1:40 PM by Benjamin Greene

    Show Totals when Filtered to All, show breakdown otherwise.

    william.boyd.0

      Hello, I have spent a good deal of time searching for a solution and I haven't been successful.

      The information I'm working with is sensitive, so I have replaced the names and cannot post this specific workbook. I can try to recreate the issue with the SuperStore if that helps!

       

      I want to create a sheet where the user can change a filter: -All, -Name 1, -Name 2, -Name 3 etc.

      When the filter is set to "All," the data should be displayed in a combined format, as shown below.

       

      Unfiltered.png

      When the user then selects 1 or more of the names from the filter, "Bob, Suzie, Mark" etc, I am trying to have the bars broken down by those selected names, like this:

      Filtered1.png

      Is this possible with Tableau? I also want the % calculation shown to remain as a percentage of the whole, not only of the data currently being shown, which may be an issue if my first problem is solved.

       

      Thanks!

        • 1. Re: Show Totals when Filtered to All, show breakdown otherwise.
          Benjamin Greene

          Hey William. I came up with something that I think will work for you. In a new sheet, try this:

           

          1. Drag Urgency to Columns.

          2. Create a new parameter called "Show Breakdown?" and make the Data Type String with a List of Allowable Values. Add two values: Yes and No. Click OK.

          3. Right-click "Show Breakdown?" and select Show Parameter Control. Click the little options arrow in the corner of the "Show Breakdown?" card and set it to a Single Value List.

          4. Right-click Assigned To and select Show Filter. Click the little options arrow in the corner of the Assigned To filter card and select Customize, then deselect Show "All" Value.

          5. Create a calculated field called Second Dimension. Make the formula:

          IF [Show Breakdown?]="Yes"

          THEN [Assigned To]

          ELSEIF [Show Breakdown?]="No"

          THEN [Urgency]

          END

          6. Drag Second Dimension to Columns. Then, in the menu bar, select Analysis>Table Layout and deselect Show Field Labels for Columns.

          7. Create a calculated field called Percent of Total. Make the formula:

          IF [Show Breakdown?]="Yes"

          THEN SUM([Number of Records])/SUM({SUM([Number of Records])})

          ELSEIF [Show Breakdown?]="No"

          THEN SUM({FIXED [Urgency] : SUM([Number of Records])})/SUM({SUM([Number of Records])})

          END

          8. Right-click Percent of Total and select Default Properties>Number Format>Percentage and click OK.

          9. Drag Percent of Total to Rows and then drag another instance to Label.

           

          How does this work for you?

          1 of 1 people found this helpful
          • 2. Re: Show Totals when Filtered to All, show breakdown otherwise.
            william.boyd.0

            Thank you for your time and assistance, Benjamin. This works wonderfully well and accomplishes exactly what I was looking for.

             

            On a side note. Is there any way to make this work on a dashboard for multiple sheets, where the sheets are all using different dimensions? I would imagine I need to create a "Yes/No" breakdown button for each sheet that uses different dimensions.

            • 3. Re: Show Totals when Filtered to All, show breakdown otherwise.
              william.boyd.0

              I figured out the question I just asked. It's just the same parameter controlling a different calculated field.

              Really appreciate the help!

              • 4. Re: Show Totals when Filtered to All, show breakdown otherwise.
                Benjamin Greene

                Glad to help! And you are correct; just make sure you create new versions of the Second Dimension and Percent of Total calculated fields for each sheet and you will be good to go.