Pivot Table column as data

[Vachaun22]

This may be a simple question, but after digging and googling for a few hours, I've come up with nothing.

Once you create a pivot table, is it possible to create a column that simply shows the data from the original data source?

Some explanation:

We have a spreadsheet showing fuel purchases. On the sheet, you have driver name, fuel card number, gallons purchased, invoice amount, fuel location and various other pieces of data.

What I'm looking for is perhaps to create a pivot table that would be grouped first by fuel location, then by driver. Then I would like the data to have summed information on invoice amount, and gallons purchased. However, I would also like to show the fuel card number as a column in the data, so it should simply have the data copied over from the spreadsheet in the appropriate row with no actions being taken on it.

Is it possible to do this? I haven't found a way yet.

Thanks for the help.

 

[Gavona]

You have Fuel loacationn and driver as row fields. Now add Fuel Card Number asanother row field.

I assume that your 2 data fields are showing on a single row? If not but you would like this then drag the 'Data' heading to above the word 'Total'.

Gavin

 

[Vachaun22]

I'm not understanding what you are saying?

When I put fields in the rows, then it makes a new grouping. This isn't what I want. I simply want the data to appear as a column, but I haven't found a way to show data simply as the data. It either needs to be manipulated by a function, or each value in the column becomes it's own column header which neither situation is what I want.

So it would look something like this

Fuel Location '' Group header
Driver Name Card Number Gallons
Driver Name Card Number Gallons
Driver Name Card Number Gallons
Sub Total SUM(Gallons)


But what I'm getting is

Fuel Location '' Group header
Driver Name '' Group header
Card Number Gallons
Driver name
Card Number Gallons
Driver name
Card Number Gallons
Sub Total SUM(Gallons)


However, after thinking about this, the first way isn't possible and after thinking about the data it makes sense. There's no way to guarantee that in this case that driver and card number would be 1 to 1 relationship.

I don't believe it can be done the way I want. Thanks anyway for the suggestion.

 

[Gavona]

What you want is easily possible.

Sounds like you need, for each row field, to ensure that
Field Settings, Layout is set to Tabular form (rather than Outline)

Also to set Field settings, SubTotals to None


Gavin

 

[Vachaun22]

That did the trick. Thanks.

I had the individual pieces and was playing around with those settings, but never combined them to get the correct result. Perhaps a few more hours might have gotten me to where I needed to be.


Thanks again for the help.