PIVOTS: Multiple columns to be shown as separate row labels

[gregz1234]

Hello all,


This certainly feels like a silly question, and I'm certain there's a work around in powerpivot, however for nonPPV/Excel 2010, does anyone know a way to display dollumn values seperately as rows?

I don't believe this is possible, however I would be remiss if I didn't ask.

The current Table sample looks like:

Year	Duty TTL	EV TTL
2009	2	20
2010	600	63
2015	10800	818
Grand Total	11402	901

<colgroup><col><col><col></colgroup><tbody>
</tbody>

When the years (column) are moved to column labels, the Duty and EV lines blow out the table, etc.

I ideally would like the PT to show as:

	2009	2010	2015	Grand Total
Duty TTL	2	600	10800	11402
EV TTL	20	63	818	901
Grand Total	22	663	11618	12303

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

I've been play around with few different ideas and nothing has worked. While certainly it wouldn't be the worst thing leave the table as-is, it would make reporting a lot easier if it could be formatted as below.

Thanks in advance!!

-Greg

 

[SpillerBD]

I'm gonna bet you started by selecting the check boxes in the fields list.
Don't be afraid to just drag the fields to ROW or COLUMN. You may also want to see how switching where the SIGMA is shown. Try moving the Sigma to the rows instead.
Also, make sure your Pivot Table is set up as Tabular instead of Compact. Tabular is preferred about 99% of the time based on other conversations.

 

[gregz1234]

I'm gonna bet you started by selecting the check boxes in the fields list.
Don't be afraid to just drag the fields to ROW or COLUMN. You may also want to see how switching where the SIGMA is shown. Try moving the Sigma to the rows instead.
Also, make sure your Pivot Table is set up as Tabular instead of Compact. Tabular is preferred about 99% of the time based on other conversations.

Hello,

Thank you for the response! By sigma, do you mean values? I just moved the values over to the rows and it appears to have done the trick!

The only issue appears to be that columns are not totaling as a result, even when I manually try to fix in the PT layout. Is there any workaround for that? Otherwise, I could certainly manually add in the column totals manually.

 

[SpillerBD]

You have multiple fields in ROWS?

 

[Oeldere]

you mean like this?

Book1
	A	B	C	D	E
18
19	Year	choise	number
20	2009	Duty TTL	2
21	2010	Duty TTL	600
22	2015	Duty TTL	10800
23	2009	EV TTL	20
24	2010	EV TTL	63
25
26
27
28	Sum Numbers	Year
29	Choise	2009	2010	2015	Endtotaal
30	Duty TTL	2	600	10800	11402
31	EV TTL	20	63		83
32	Endtotaal	22	663	10800	11485
33
Blad6

 

[gregz1234]

you mean like this?

	A	B	C	D	E
18
19	Year	choise	number
20	2009	Duty TTL	2
21	2010	Duty TTL	600
22	2015	Duty TTL	10800
23	2009	EV TTL	20
24	2010	EV TTL	63
25
26
27
28	Sum Numbers	Year
29	Choise	2009	2010	2015	Endtotaal
30	Duty TTL	2	600	10800	11402
31	EV TTL	20	63		83
32	Endtotaal	22	663	10800	11485
33

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Blad6​

Hello,

Endtotaal/Grandtotal on row 32.


(In a different example) Moving just the values to the row labels did the exact trick!

However, subtotals from months aren't available:

	Column Labels
Values	February	March	April	May	Grand Total
Sum of Quantities correct if applicable	0	4	0	3	7
Sum of Relation Declaed Correctly	1	0	2	0	3
Sum of Assist if applicable	0	0	15	3	18
Sum of C/O	0	1	0	2	3
Sum of Tariff number	0	4	2	5	11
Sum of Free Trade Agreement if applicable	0	0	0	0	0
Sum of Manufacturer ID Correct	0	3	7	4	14

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

As mentioned, I can certainly add them manually, however, just wanted to check.

-Greg

 

[SpillerBD]

Subtotals are available for Columns, but not Rows.
You would need to swap your fields Columns for Rows.... and then you're are probably wanting Running Totals too. So drop the value field again and choice the calculation method as Running Total.

 

[gregz1234]

Subtotals are available for Columns, but not Rows.
You would need to swap your fields Columns for Rows.... and then you're are probably wanting Running Totals too. So drop the value field again and choice the calculation method as Running Total.

The problem is, they aren't currently showing for columns (see the last graph posted) when values are in the label columns.

 

[SpillerBD]

The problem is, they aren't currently showing for columns (see the last graph posted) when values are in the label columns.

I actual don't see anything to be subtotaled. The Months are totaled. The rows are totaled by the categories, so what Subtotal is missing?

 

[Oeldere]

The rows are totaled by the categories, so what Subtotal is missing?

Sum of Quantities correct if applicable
Sum of Relation Declaed Correctly


If you want to subtotal the above values:

edit:

Make a new columnn the original data.

Add in this column the new group (e.g. with VLookup).

After that select that group in the pivottable.

Then you wil have subtotal in the columns.

Or the subotal is de-selected in the tab developer => subtototal (1rst option).