Making a pivot table/chart with multiple Z values

Question

I am trying to make a pivot table and chart. The data I am using includes the year in the column field; theme 1, theme 2, theme 3, and theme 4 as both the row labels and the Sum values. I am trying to make a chart that shows the theme categories along the rows axis, and the total for each category for 2012 and 2013 in the columns.

An example of the data is:

Quarter and Year	Quarter	Year	Theme 1	Theme 2	Theme 3	Theme 4
Q1 2012	Q1	2012	O1:Charters/Mandates	O3: Assignment of authority	R9: Risk Oversight
Q1 2012	Q1	2012	O6: Alignment of Activities (between silios)	O1:Charters/Mandates	R9: Risk Oversight
Q1 2012	Q1	2012	A5: Performance Management
Q1 2012	Q1	2012	O1:Charters/Mandates	C3: Control design or lack of control

What I get in the pivot table is:

	Column Labels
	2012				2013				Total Count of Theme 1	Total Count of Theme 2	Total Count of Theme 3	Total Count of Theme 4
Row Labels	Count of Theme 1	Count of Theme 2	Count of Theme 3	Count of Theme 4	Count of Theme 1	Count of Theme 2	Count of Theme 3	Count of Theme 4
A1: Skills & Knowledge	3	3	3	3	2	2	2	2	5	5	5	5
A2: Communication & awareness	9	9	9	9	2	2	2	2	11	11	11	11
A3: Insufficient training	5	5	5	5	1	1	1	1	6	6	6	6
A5: Performance Management	2	2	2	2	1	1	1	1	3	3	3	3
C1: Adequacy of policies, guidelines and procedures	52	52	52	52	16	16	16	16	68	68	68	68
C10: Management review	4	4	4	4	4	4	4	4	8	8	8	8

As you can see, each theme category is not filled, yet the table suggests that all of the themes are even accross the board.

If there is a fix, or a mix up of the values I am using, please let me know.

Thanks

Answer 1

Hi,

Your data is not well suited for creating a Pivot Table.  Instead of having Themes as columns, you should have only column of Theme and all categories under that theme column.  In other words, you need to depivot/denormalise your data.

You may refer to the following link on my website to depivot/denormalise your data - http://www.ashishmathur.com/converting-a-matrix-data-layout-to-a-tabular-layout/

Hope this helps.