I currently have a table that looks like this:
Company | 2013 Total | 2013 Gross $'s | 2013 Gross % | 2012 Total | etc..
| | | | |
Obviously this is not a sustainable format for data.
I would like to reformat it (possibly using a pivot table?) to look more like this:
| 2013 | 2012 | 2011 |
Company Name | | | |
Total | | | |
Gross $'s | | | |
Gross % | | | |
Company 2 | | | |
Total | | | |
Gross $'s | | | |
Gross % | | | |
Fairly similar to this example at microsoft.
I tried modifying some of the examples in the above link, by making multiple tables with ID keys to create relationships in a pivot table but I was unable to get the desired outcome.
When trying to create a pivot table using multiple tables I tried with the following:
Table 1 (names) Table 2: (years)
| |
| Company nm | | Year |
| comp 1 | | 2011 |
| comp 2 | | 2012 |
| 2013 |
Table 3 (values)
|
| Company | Year | Total | Gross |
| comp 1 | 2011 | xxx | xxx |
| comp 1 | 2012 | xxx | xxx |
| comp 1 | 2013 | xxx | xxx |
| comp 2 | 2011 | xxx | xxx |
The Name from the Company column was a row, the years were set as columns, and the total/gross from values were set as a value in the pivot table.
The relationships:
- values (company) -> name (company)
- values (year) -> years (year)
If i only use the Totals and not the Gross, it looks similar to what i want: http://prntscr.com/32dsm9 , but if i add the gross field: http://prntscr.com/32dsu8 It adds a bunch of columns. I'm not sure how to make that be a row.