10 Replies Latest reply on Jan 19, 2013 7:39 AM by James Egan

# How to display a line graph percent of total for only one of the values in a dimension?

I have a simple report:  Sales by new vs. renewal customers.  I show that as a stacked bar graph over time.  I'd like to add a line graph representing renewal revenue as a percent of total revenue.  But I can't figure out a way to only show just the renewal line without also limiting the bar graph to only renewals.  In excel, this would be a simple matter of creating a column of the percentage of renewal business and including it in the graph.  Is there a way to do this in Tableau?

Thank you.

• ###### 1. Re: How to display a line graph percent of total for only one of the values in a dimension?

James,

I think the answer is going to be yes and it will probably include using a dual axis (read this: http://onlinehelp.tableausoftware.com/v6.1/public/online/en-us/i1003545.html and this: http://kb.tableausoftware.com/articles/knowledgebase/Secondary-Axes).  And you may possibly need a calculated field for renewal revenew as percent of total revenue.

But it will all depend on exactly how your data is structured.  If you can post a packaged workbook (even with dummy data), then I can take a look!

Joshua

• ###### 2. Re: How to display a line graph percent of total for only one of the values in a dimension?

Hi Joshua, Thanks for replying.  I've created the dual axis report and it resulted in the same issue.  I also created a new metric, albeit one based on revenue, to see if that worked.  It didn't.  I suppose because it's really just derivation of the same data, the calculated metric and the bar chart are lined with regard to new vs. renewal distinctions.  So if I try to just show the percentage renewal line, I'll still only see the renewal bar chart.

It's surprising because this is such a simple thing to do and I can't find a way to do it.

It's a really simple chart.  Sales over time by one dimension: new/renewal.

• ###### 3. Re: How to display a line graph percent of total for only one of the values in a dimension?

Hmmm...

You should be able to edit the Marks shelf separately for each axis.  In fact you are doing this to an extent, because you have different mark types.  If you use the <  and   >   buttons on the Marks shelf (see highlighted part of image) to switch between axes, you should be able to remove the field from color on your % of Total Revenue axis.

So here is stacked bar that is split on color by a dimension and a dual axis line that isn't:

• ###### 4. Re: How to display a line graph percent of total for only one of the values in a dimension?

Right.  The difference between your graph and mine is that you have two, separate measures.  My measures are based on the same field:  revenue.  That's why, I think, I can't filter on my line graph without also filtering on the bar graph.  Still hoping there is some answer to this.

Thanks again.

• ###### 5. Re: How to display a line graph percent of total for only one of the values in a dimension?

James,

I think it should work even with the same measure, but maybe I'm missing something.  Would you be able to post a packaged workbook (even with dummy data, if your current data is sensitive)?  That might help me see what's causing the difficulty.  Thanks!

Joshua

• ###### 6. Re: How to display a line graph percent of total for only one of the values in a dimension?

Okay.  I've attached one.  Not sure if this is what you're looking for.

• ###### 7. Re: How to display a line graph percent of total for only one of the values in a dimension?

James,

Thank you very much for the workbook!  It did let me see the issue -- you were correct, using the same measure was an issue.  Specifically because the table calculation relied on the color slice to come up with the right values for each.

So, the solution I came up with for this case was to do the same thing without the table calculation.  I used a couple of other calculations:

1. A row level calculation to determine only revenue for renewals.

2. An aggregate calculation to determine the percent of renewal revenue to total revenue.

That final calculation now makes up the secondary axis and is only a single line for renewal revenue.  I've attached the workbook so you can see!

Joshua

• ###### 8. Re: How to display a line graph percent of total for only one of the values in a dimension?

Just got to this.  It works great.  Thank you very much Joshua!