I am stumped by a question on a potential employer's Excel test. The objective is to create a graph showing each particular product's percentage of total month's sales, for a 12 month period. The data set is thousands of lines, each showing sales for each month for a particular product in a specific region (sample below). I started by creating a pivot table of the data set, that totaled sales for each product, by month. This was fairly straightforward. The trouble came when I wanted to create the graph with the trend line showing the percentage of net sales for the month that each product contributed. The instructions were to create one chart with trend lines showing each Product's performance over time, with vertical axis labeled as percentage of total sales, and the horizontal axis showing months (e.g. Month 1, 2, 3…etc). The lines are to be stacked and color coded with a legend identifying each line and the Product it represents. I tried adding a calculated field to the pivot table, but wasn't able to get the percentage of total sales. The original data set is in the format of the example below. Any ideas/help is greatly appreciated. - Thanks.
<tbody>
</tbody>
| Product | Region | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 |
| Prod 1 | NE | 456 | 678 | etc.. | |||||||||
| Prod 1 | NW | 567 | 453 | etc.. | |||||||||
| Prod 2 | NE | 77 | 56 | etc.. | |||||||||
| Prod 2 | SE | 565 | 345 | etc.. | |||||||||
| Prod 2 | SW | 567 | 356 | etc.. | |||||||||
| Prod 3 | NW | 567 | 36 | etc.. | |||||||||
| Prod 3 | SW | 89 | 356 | etc.. | |||||||||
| Prod 4 | NE | 456 | 34 | etc.. | |||||||||
| Prod 4 | MW | 67 | 34 | etc.. |
<tbody>
</tbody>
