Pivot Table Row Labels

[aje35]

Data: Hourly Pricing 1-24 since April 2009 *Sample Shown Below*)

Current Pivot Table

The pivot table's Row Labels go by "Year"-> "Month"-> "Day" and I want it to go "Year"-> "Month"-> "Day"-> "Hour"

Below is a link to another thread where I was trying to achieve the above goal by transposing the hourly data but I received no response to this so I am assuming it is too difficult.

http://www.mrexcel.com/forum/showthread.php?mode=hybrid&t=503003

Ultimately I want to have my Row Labels "Year"-> "Month"-> "Day"-> "Hour", my Report Filter "Day of Week" -> "Hour of Day" and then i just want the Data Values to display the averages of the specified information.

If I am not being clear ask any questions so I can elaborate.

This is going to be a very important part of a research project I am doing for school so any responses would be appreciated.

 

[DonkeyOte]

You will need to transpose your dataset - for a demo see: http://datapigtechnologies.com/blog/index.php/transposing-a-dataset-with-a-pivottable/

 

[aje35]

This tutorial is exactly what I am looking for. I am using Excel 2010 and I can not get my PivotTable to give Column Grand Totals. I went in to "PivotTable Options" -> "Totals & Fitlers" and I checked off "Show grand totals for columns" but it does not put a grand total column in. The "Show grand totals rows" works however. If you have any clue to how to troubleshoot this that would be great.

Thanks for the helpful link!

Sancho Panza

 

[DonkeyOte]

If your layout is as you describe in your first post and is now based off the transposed data set then why would you have Column Grand Totals ?
ie all your analysis takes place by Row doesn't it ? you have one Data Field and no Column Labels

 

[aje35]

Well from that tutorial it was my understanding that you need to double-click the column grand total in order to transpose the data. Under Step 6 of the tutorial you gave me seems to be doing that. If you require any sort of screenshot or clarification just let me know. My dilemma right now is having Column Grand Totals in order to complete Step 6.

Cheers

 

[DonkeyOte]

I just tested in XL2010 and if you've followed steps 1 to 5 the Grand Total Column should appear by default at Step 6.

 

[aje35]

You have both column and row subtotals. In the tutorial they double-click on the intersection of both subtotals.

 

[DonkeyOte]

Yes.

 

[aje35]

Well it is possible that my data set is being strange in the PivotTable because I have both column (bottom) and row (right) grand totals checked off but only column grand totals are showing up. Normally I would insert a calculated field and do totals that way however it does not work with this tutorial.