How do I get row headers (in my data) into column headers (in my pivot table)?

[smart analyst]

Hi all,

I'm trying to figure out two things here:

1.) How do I get my row headers (from data) into specialized, newly created header fields (in my pivot table)?

2.) How do I then go on to create a calculated field from these two new specialized headers in the pivot?


Simple example below:

The yellow section highlights my first question - how do I get separate row headers as unique field items in columns in my pivot table

The green section highlights my second question - how do I get get a calculated field in my pivot table (in this case variance) with those newly created unique fields?


Thank you!

 

[mart37]

Put TYPE in column section.

 

[smart analyst]

Put TYPE in column section.

Hey Mart,

I understand this part - however, I need to be able to calculate the items in the type field, which means that I would have to keep them in values, if I remember correctly. So in order to get a calculated variance between both row types, how would I go about doing that?

 

[rhombus4]

Department in rows
Type in columns, Type in values as count
Insert calculated field -
formula = budget - actual

Difficult to be more detailed as typing this on phone

 

[smart analyst]

Department in rows
Type in columns, Type in values as count
Insert calculated field -
formula = budget - actual

Difficult to be more detailed as typing this on phone

Hi rhombus,

When I try this method, I don't have "budget" and "actual" to choose from when I try to calculate field. Is there a way I can have these row headers appear as individual fields so that I can calculate them in my pivot table? Right now, I only see "type", "department", and "headcount" as fields that can be calculated.

 

[mart37]

Instead of calculated field you can use calculated item.

Put only TYPE in ROWS
Select a item
Make a calculated item (now you can choose BUDGET and ACTUAL)
Move TYPE to COLUMNS
Add the other fields

 

[smart analyst]

Instead of calculated field you can use calculated item.

Put only TYPE in ROWS
Select a item
Make a calculated item (now you can choose BUDGET and ACTUAL)
Move TYPE to COLUMNS
Add the other fields

Thank you, Mart!

This accomplished what I needed to do.

Although, I am curious - if I wanted to get "actual", "budget" and "variance" as separate field items (sitting in the values portion of the pivot table), how would I do that?

There's a report that I see that was able to do something like that, and I'm very curious how I would do it.