Chart with two sets of data on same axis

Hi,

I've attached a file with data for Net Profit across four years.

The Net profit is made up of two components across four regions.

The Regions have the net profit achieved by a specific product across 2015, 2016, 2017 and 2018.

The Regions then have the net profit achieved by the company across 2015, 2016, 2017 and 2018.

For example, Product A achieved an 8.70% net profit in Region 3 for FY 2016. In that same year the net profit we achieved in Region 3 for the business was 4.60%.

What I'm trying to do is show a comparative of the four regions for Product A in each financial year in a column chart.

I then want to overlay the overall business net profit for these four regions as a line graph. I don't want four separate lines on the chart - I just want a single line with data points for each year.

I'm actually beginning to wonder if this is possible as I've tried all I can think of and can't show the business net profit as a single line.

If people can let me know if it is possible that would be great. If it is possible then some tips as to how to go about it would be fantastic.

Hi MrShorty,

Thanks for the reply.

The difficulty for me is the line part of the chart.

Rows 4 to 7 represent the product net profit in each region. Rows 9 to 12 represent the business (the aggregate) net profit for all products in each region.

Therefore, 12.20% is the net profit for Product A in Region 1 during FY2015. 5.4% is the business net profit in Region 1 for the same period. I can get a line graph over the top of the columns but I have not been able to manage a single line to represent rows 9 to 12. I don't want four lines on the graph over the top of the clustered groups of 4 columns. I want to have four data points or markers showing what the net profit was in the business (rows 9 to 12)for the four regions in each financial year. The idea is to have this as a single line which travels across the whole graph from FY2015 to FY2018.

As I say, I'm beginning to wonder if this is possible.

I suspect it's probably not possible to accomplish what I'm trying to do.

I have FYxxxx across the horizontal axis, percentage up the vertical axis and four data series in the column chart (Region 1 shown in row 4, Region 2 shown in row 5, Region 3 shown in row 6 and Region 4 shown in row 7).

The data series for Region 1 shows 12.20% in FY2015. Next to this column I have a data series column for Region 2 showing 8.40%, a column for Region 3 showing 8.90% and a column for Region 4 showing 9.20%. These columns are clustered together above FY2015 on the horizontal axis. Over FY2016 I have Region 1 series showing 11.80%, a column for Region 2 showing 8.30%, a column for Region 3 showing 8.70% and a column for Region 4 showing 9.40% - again, all clustered together above FY2016 on the horizontal axis. The data series for Region 1 then shows 11.70% for FY2017, a column for Region 2 showing 7.50%, a column for Region 3 showing 9.10% and so on until I have four clusters of columns representing Region 1 through to Region 4 across the four financial years.

That part is fairly simple.


The second set of data for Regions 1 through to 4 which is found in rows 9 through to 12 give the net profit figures achieved by the business in those four regions across the four financial years.

What I'm trying to do is show these percentages as a line on the graph.

Therefore, the line would start at 5.40% (as measured by the vertical axis) in the first column which is Region 1 in FY2015. This line would then go to 4.70% in the second column for FY2015 which is Region 2. It would then go to 5.30% on the third column (Region 3) and then 4.90% in the fourth column for Region 4. The line would then continue on to the first column in the second cluster of columns at 5.70% as measured by the vertical axis. The line would travel on to 5.20% for the second column of this cluster above FY2016 on the horizontal axis, then to 4.60% in the third column and then to 5.20% in the column representing Region 4 for FY2016. This line would then continue across the graph to the first column above FY2017 to a point 6.00% which is the net profit the business achieved in Region 1 during FY2017. The line would continue to 5.10% in the second column over FY2017 which is the net profit achieved by the business in Region 2 during FY2017, continue on to a data point of 4.90% in the third column over FY2017 (net profit achieved in Region 3 for FY2017) and 5.30% in the fourth column above FY2017. The line would remain unbroken, traveling on across the chart to a data point of 6.80% in the first column of the fourth cluster of columns sitting above FY2018. It would then continue to 5.10% in the second column above FY2018, then to 5.10% in the third column and finishing at 5.70% in the fourth column representing the net profit achieved by the business in Region 4 during FY2018.

Getting this information to display as a single line on the graph is what is causing me a great deal of trouble.

Hope that makes it clearer as to what I'm trying to do and, if not possible, just let me know.


Thanks.

Originally Posted by MrShorty

Here's the sample file.

Because we are building a combination column + scatter (not line), we need a column of values for the scatter chart series to use for X values. This allows us to control the horizontal position of the data points. After changing the 5th data series to scatter, we use the Select Data dialog to reference the G9:G24 data as the X values data for the scatter chart.

Wow! I've really learnt something. I'll have to work with it to understand it but that's fantastic.

Thanks for your help - sensational.