7 Replies Latest reply on Feb 1, 2019 8:56 AM by Ken Flerlage

    Create Bar Charts Using a Dimension in Multiple Groups

    Jeremy Milkes

      Good afternoon. I'm curious if it's possible to create a stacked bar chart where each column would be one of the below example groups. The stacked bar would have the number of 1's, 2's, 3's and 4's in each group. There are rows in multiple groups. The table below is a mock up but it reflects the structure of the real data.

       

      The idea is to have column 1 be the population, column 2 be only the rows in Group A, columns 3 be only the rows in Group B, and column 4 be only the rows in Group C. Is such a set up possible? I've played around with parameters and sets, but I cannot figure out if all of this information can be placed in one worksheet. Any ideas? Help is greatly appreciated.

       

      NameGroup AGroup BGroup CScore
      JimTRUETRUETRUE4
      BobFALSETRUETRUE4
      NancyFALSEFALSETRUE3
      SarahFALSETRUEFALSE2
      TomTRUETRUEFALSE1
      JohnTRUEFALSETRUE4
      BillFALSEFALSETRUE2
      HankFALSETRUEFALSE1
      FridaFALSEFALSEFALSE1
      LauraTRUEFALSETRUE3
      MichaelTRUETRUETRUE2
        • 1. Re: Create Bar Charts Using a Dimension in Multiple Groups
          Ken Flerlage

          I don't quite understand what you're trying to do. Could you give us a better idea of the expected output?

          • 2. Re: Create Bar Charts Using a Dimension in Multiple Groups
            Jeremy Milkes

            Hi Ken,

             

            Here is an image of what I am trying to accomplish. I created these bars using multiple worksheets and putting them together in a dashboard. I'd like to see if it's possible to do this all in one worksheet.Grouped stacked bars.PNG

             

            As shown by the mock up data above, there are 3 groupings of people. Many people are in multiple groups. For example, Jim is in all three groups. Each person has a score attached to them. I would like to create a stacked bar chart which shows, for the population and each group, the proportion of people with a score of 1, 2, 3, or 4. This has proven difficult since there are people in multiple groups.

             

            Using a parameter, I was able to get the population and Group 1 in the same worksheet. However, creating columns for the other groups in the same worksheet has proven difficult. Is it possible? If so, how can I best accomplish this? Thank you so much for your help.

            • 3. Re: Create Bar Charts Using a Dimension in Multiple Groups
              Ken Flerlage

              Something like this? If so, let me know and I can show you how to do it. It'll require some trickery though!

              1 of 1 people found this helpful
              • 4. Re: Create Bar Charts Using a Dimension in Multiple Groups
                Jeremy Milkes

                Yes! That's exactly what I'm looking for. Could you please show me how to do this? Thanks so much for your help.

                • 5. Re: Create Bar Charts Using a Dimension in Multiple Groups
                  Ken Flerlage

                  Okay, it's a bit tricky and we'll have to do some strange things with our data. Here's how.

                   

                  Start by bringing in your data. Next, pivot the three group columns (here's how to do that: https://www.kenflerlage.com/2018/06/pivoting.html). That should make the data look like this:

                  I'm then going to rename the fields to make it a bit more understandable.

                  I'll then flip over to a sheet and start by changing Score to a dimension. Then I'll build a view like this with Group on columns, Number of Records on Rows, Score on color, and a filter on In Group to only show those with True.

                  Then we'll change the SUM(Number of Records) to use a quick table calc of "Percent of Total" and we'll change it to compute using Score.

                  But that only gives us the 3 groups. The problem is that we don't really have a separate field for "Population". One option would be to artificially create that in your source, but I don't know if that's an option for you. If not, then we'll have to artificially create that group. We'll do that by going back to the data pane and unioning the source to itself. Just drag the sheet onto itself until you see the note about creating a union.

                   

                  If you flip back to the sheet, it'll look the same, but we've actually duplicated all of our data. To allow you to differentiate between the data in the two separate copies, Tableau gives you a Table Name field. So we'll use that to trick Tableau into thinking there's another group. Start by creating a couple of calculated fields:

                   

                  Group Modified

                  // Use the union to artificially create the "Population" group.

                  IF [Table Name]="Sheet1" THEN

                      "Population"

                  ELSE

                      [Group]

                  END

                   

                  In Group Modified

                  // Ensure that everything in the "Population" group is included.

                  IF [Table Name]="Sheet1" THEN

                      TRUE

                  ELSE

                      [In Group]

                  END

                   

                  Count Modified

                  // Since we're pivoting the data for the 3 groups...

                  // ...we need to adjust the count for the "Population" group.

                  // We'll do this by counting the distinct number of groups.

                  IF [Table Name]="Sheet1" THEN

                      1/{FIXED : COUNTD([Group])}

                  ELSE

                      1

                  END

                   

                  Then, on your view, replace Group with Group Modified, replace the In Group filter with In Group Modified, and replace SUM(Number of Records) with SUM(Count Modified). Be sure to change the measure to be a percent of total table calc computed using Score. It should now look like this:

                  We'll now need to update a few minor things. Right-click on the Score pill and change it to sort descending (so the lower numbers are at the bottom). Drag Score to the label card if you want each portion of the bar to have the number. Right-click on the Group Modified pill and then use a manual sort to push Population to the front. Finally, you can change the colors as desired.

                  See attached.

                   

                   

                   

                  1 of 1 people found this helpful
                  • 6. Re: Create Bar Charts Using a Dimension in Multiple Groups
                    Jeremy Milkes

                    You're a wizard Ken! This did the trick. Thanks so much for your help. Really appreciate it!