Trying to create a chart with main YtY comparision and subsections

**Nikopol** · 06-29-2020, 04:23 AM

Hi,

perhaps you could help me out with this thing - I generally dont work in xls too much so lots of things I have forgotten. Anyway, I have dataset that include main YtY comparision, but also data that show sales numbers for each sales rep that in total = total year data. Basically data in collumn are January, February, March etc. while in row its 2019 Year data, 2019 Sales rep A data, 2019 Sales rep B data...., 2020 Year data, 2020 Sales rep A data, 2020 Sales rep B data etc.

Im trying to create a Line Chart where one would have option to select YtY view and also sales rep YtY view based on them but cant figure out how to do it.

Any tips? Thanks

**AliGW** · 06-29-2020, 04:24 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

**Nikopol** · 06-29-2020, 04:35 AM

Thanks,

example spreadsheet attached

**AliGW** · 06-29-2020, 04:45 AM

The data provided dos not fully match the description and there is no manual mock-up showing what you are trying to do. Try again, please.

**Nikopol** · 06-29-2020, 06:23 AM

Hi,

posted correction with graph showing YtY comparision with totals.

What I would like to get towards is have option in Axis between YtY (Total) and YtY (Sales Rep) where receiver could choose between these values and see separate chart comparisions between total and specific sales reps

Thank you

**MrShorty** · 06-29-2020, 11:36 AM

What I see in both sheets are pivot tables. Your file does not include the actual source data for these pivots (assuming this is not the desired input arrangement). If you build a nice database and a nice pivot table (or more if you want), then the kinds of things you are talking about should become simple pivot table operations. You can filter by sales rep or filter by year. Because the pivot chart is tightly linked to the pivot table, as you change the pivot table, the chart readily follows.

On the off chance that you intended this as your input data, I would point you to some discussion about building a good database source for pivot tables.
https://www.excelforum.com/excel-new...ta-layout.html
https://www.excelforum.com/tips-and-...for-excel.html
https://trumpexcel.com/source-data-for-pivot-table/

A tutorial for how to use pivot tables: https://trumpexcel.com/source-data-for-pivot-table/

With a good source data design, then Harok2 is a simple pivot table with period for the row labels, year for column labels, and sum of whatever these numbers are in the values field. Harok2 is essentially the same pivot table, but adding sales rep as an additional column label. Once you have the pivot table(s) set up, then you can use filter the columns by year and/or by sales rep to compare different subsets of the pivot data.

Is that something you are comfortable trying?