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

Create Bar Charts Using a Dimension in Multiple Groups

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.

 Name Group A Group B Group C Score Jim TRUE TRUE TRUE 4 Bob FALSE TRUE TRUE 4 Nancy FALSE FALSE TRUE 3 Sarah FALSE TRUE FALSE 2 Tom TRUE TRUE FALSE 1 John TRUE FALSE TRUE 4 Bill FALSE FALSE TRUE 2 Hank FALSE TRUE FALSE 1 Frida FALSE FALSE FALSE 1 Laura TRUE FALSE TRUE 3 Michael TRUE TRUE TRUE 2
• 1. Re: Create Bar Charts Using a Dimension in Multiple Groups

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

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.

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

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

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

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

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

• 7. Re: Create Bar Charts Using a Dimension in Multiple Groups

Thanks. Any time!