Stacked Column chart with percentage line

[rodneym30]

I am trying to create a chart that has the primary axis as a stacked chart that shows 2 figures (numbers of sales & number of referrals). I am then adding a line that shows percentage across the chart in a secondary axis. The issue is that I need this line to show the percentage of sales that were referrals. I have the chart created, however due to the referrals changing each month the line is not accurately showing the percentage. The percentage number is correct; it is the spot where the line intersects that is not calculating properly on the stacked bars. Any thoughts?

 

[Gerald Higgins]

Hi, welcome to the board.

I'm confused.

What does this

due to the referrals changing each month the line is not accurately showing the percentage

mean exactly ?

And this ?

The percentage number is correct; it is the spot where the line intersects that is not calculating properly on the stacked bars

Don't worry too much about explaining in more detail how your business works, but can you explain what exactly you want the chart to do, and why it's not doing it now ?

 

[rodneym30]

Maybe a different outline will help others understand? I have a chart that utilizes a stacked chart showing 2 figures - the bottom portion shows Referrals and the top portion shows number of sales recorded. These reside on the primary axis. I then added a line to show the percentage of referrals recorded as the second axis. It all is there, however I cannot figure out how to get the percentage line to match up to the proper place on the stacked chart showing the accruate referrals recorded. The percentage number it shows is correct, it just places the line in the wrong location across the chart in conjunction to the stacked chart.

 

[rodneym30]

Please reference the URL to see the chart - the line should match up to the top of the red line for each month. I have messed with it a bit and can get it closer than it is here but it still does not match month to month.

http://www.asensualportrait.com

 

[rodneym30]

Hi, welcome to the board.

I'm confused.

What does this

mean exactly ?

And this ?


Don't worry too much about explaining in more detail how your business works, but can you explain what exactly you want the chart to do, and why it's not doing it now ?

Please see my posts after your reply Truly appreciate any input and your time...

 

[Gerald Higgins]

Hi.

Why should the line be in the same place as the red bar ?
They're measuring different things.

 

[rodneym30]

Hi.

Why should the line be in the same place as the red bar ?
They're measuring different things.

I agree that they are measuring different things right now. What I need is for the percentage line to be measuring the referals and showing it as a percentage.

This chart is being created to show a sales overview - the stacked bar shows the sales from referrals (red) and other sales recorded (blue) (the total of both these figures in the stacked bar being all sales for each month). The line is to represent/show the percentage that is generated from referrals each month. The line needs to match to the referrals and should equal the top of the referrals portion of the stacked chart while showing a percentage for the data being represented. I am just not sure how to make that happen...

 

[Jon Peltier]

If you want the percentage line to line up with the top of the referrals column, you need to use a 100%-stacked-column type for the columns. In which case the line is redundant. And make sure when you do that the number of sales stacked on top of referrals isn't total number of sales, but rather number of sales that weren't referrals.

 

[rodneym30]

If you want the percentage line to line up with the top of the referrals column, you need to use a 100%-stacked-column type for the columns. In which case the line is redundant. And make sure when you do that the number of sales stacked on top of referrals isn't total number of sales, but rather number of sales that weren't referrals.

So there is not a way to do this without the 100%-stacked-column type? The problem with this is that it does not clearly show a visual of monthly sales in the stacked column as the columns all go the full hight of the chart. What is required is a good visual from the stacked columns of where each month is compared to others in terms of sales and then a clear depiction of the percentage that is coming from referrals.

 

[Jon Peltier]

Rodney -

You mean you want percent labels at the top of the referrals. Okay, you do not need to add a line chart series.

You can use the Chart Labeler from Rob Bovey's site (http://appspro.com) to add labels from the worksheet to a series in the chart.

Add labels to the upper series, in the "inside base" position (which is the bottom of the bar), using the range of percentages you've already calculated.