9 Replies Latest reply on Nov 6, 2018 2:53 PM by Justin Dean

# Adding color dimension to my chart changes table calculations

Hello, I am trying to create a horizontal bar chart tracking YOY growth for office using employment in different cities. I have figured out how to do this with table calculations fairly easily.

However, when I try to add a color dimension to show how each sector is fueling growth for office using employment, it calculates the growth of each dimension and sums them together on the bar. I've searched for a while and can't find a way around this.

In the attached workbook, sheet 1 shows the correct calculations without colors and sheet 2 shows the colors but incorrect calculations.

• ###### 1. Re: Adding color dimension to my chart changes table calculations

Adding another dimension to the sheet will make table calcs react to that new calc.  Marks will be compartmentalized by that calc.

So you have to tell the table calc how to handle that new dimension.

I'll take a look and see what you need to do in there.

• ###### 2. Re: Adding color dimension to my chart changes table calculations

What do you expect to see for Albany with the colors added?

• ###### 3. Re: Adding color dimension to my chart changes table calculations

Sum to -.66%. I want to colored bars in sheet 2 sum up to the uncolored bars in sheet 1

• ###### 4. Re: Adding color dimension to my chart changes table calculations

Summing percentages doesn't result in the percentage of the overall market.

In the attached I broke out the chart in copy 2 of sheet 1.

On copy 3 I show text values of what's going on.

Looking at Albany, Financial activities has a small increase.  Information has a full 2% decrease.  Professional and Business has a 1% decrease.  But because P&B is the largest sector in Albany, it tempers the Information decrease.

I made a calc that gives me the total for the Market in each year.  (Albany has 99.31 in 2017, and 98,65 in 2018.)  The percent change for those two numbers is .66%.  Even though the sum of the three individual  sectors totals -2.58, the market as a whole only dropped .66%.  That's just how the math works out.

Tableau is doing what it's supposed to do here.

• ###### 5. Re: Adding color dimension to my chart changes table calculations

So there is no way around this then? I have a similar chart I've made in Excel, showing the three sources of populations growth for cities: Immigration, Domestic Migration, and Natural Growth (births minus deaths). Should I just leave these kind of charts in Excel?

• ###### 6. Re: Adding color dimension to my chart changes table calculations

Again I'll ask, what do you expect to see?

One chunk of Albany has growth, and two have decline.  You're going to get a portion of the bars in the positive range for those segments that have increases.  And unless all the sectors are equally proportional, the larger sector's growth or decline will dominate what the sums add up to.

As an extreme example, if you have three sectors with values 1,1,8 (for a total of 10), and the next year the values are 2,2,6 (also for a total of 10), the three sectors have growth of 100%, 100%, and - 25%,  The sum of those percentages is 175%, but the overall growth was in the whole market was actually zero.

Excel would arrive at those same numbers.

• ###### 7. Re: Adding color dimension to my chart changes table calculations

So where the total growth equals exactly zero is the only situation where this wouldn't work but with these numbers that own't be an issue.

I'll modify your extreme example a bit. Values of 1,1,8 and the next year the values are 2,2,7. The overall grows 10% from 10 to 11. The bar chart here would show 10% of the growth from category 1, 10% of the growth from category 2, and negative 10% from category 3, summing to 10%.

Or to make it a bit easier with only positive numbers. Values 1,1,8 grow next year to 2, 2, 10. Overall growth is 40%. Of that growth, 10% comes from category 1, 10% of comes from category 2, 20% from category 3. The way tableau is doing this would show that 100% growth in category 1, 100% growth in category 2 and and 25% growth in category 3.

• ###### 8. Re: Adding color dimension to my chart changes table calculations

OK, but that's not what the calc is doing.

On version 4 if Sheet 1 I added new calcs to break down the actual change by Market and by Sector.  And finally the share of change for each sector.  That's what you're really looking for here.

You can investigate the calcs and come back with questions if you have any.

On version 5 I made the same graph as you wanted in Sheet 2, but I used my share-of-change calc instead.  The sum of the shares adds up to the original values you have on your original sheet 1.

1 of 1 people found this helpful
• ###### 9. Re: Adding color dimension to my chart changes table calculations

Thanks a bunch! I didn't realize you'd have to create so many calculated fields to get to the correct answer.