Transposing values to be rows

[masplin]

I have a pivot table where months are in the row labels and 20 values (mostly measures) in values. Looks fine with months on the side and a column for each value across the top. Like this

<table width="422" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:4010;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:2986;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3626;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:2261; width:40pt" span="2" width="53"> <col style="mso-width-source:userset;mso-width-alt:2858;width:50pt" width="67"> </colgroup><tbody><tr style="mso-height-source:userset;height:25.5pt" height="34"> <td class="xl159" style="height:25.5pt;width:71pt; font-size:10.0pt;color:white;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:"Arial Narrow";border-top:none; border-right:none;border-bottom:.5pt solid #DCE6F1;border-left:none; background:#366092;mso-pattern:#366092 none" height="34" width="94">Transaction Month</td> <td class="xl160" style="width:53pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="70">Net Funds Added</td> <td class="xl160" style="width:64pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="85">% Commission VAT-ex</td> <td class="xl160" style="width:40pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="53">Visits VAT-ex</td> <td class="xl160" style="width:40pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="53">Av Price VAT-ex</td> <td class="xl160" style="width:50pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="67">Charge VAT-ex</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl165" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">April 2012</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£19,642.6</td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">11.6 %</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">580</td> <td class="xl164" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">£6.6</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£3,852.4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl165" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">March 2012</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£25,399.9</td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">11.7 %</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">665</td> <td class="xl164" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">£6.8</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£4,520.2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl165" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">February 2012</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£19,849.0</td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">13.3 %</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">441</td> <td class="xl164" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">£6.9</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£3,060.9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl165" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">January 2012</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£18,065.1</td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">13.4 %</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">380</td> <td class="xl164" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">£7.0</td> <td class="xl161" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">£2,676.1</td> </tr> </tbody></table>

However I really want the dates along the top and the values on the side! If I move the month to column labels I get a massive thin table with each month having 20 columns for each value followed by the next month and 20 more columns. Like this (apologise for poor formatting)

<table style="width: 507px; height: 113px;" border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="141" bgcolor="#FFFFFF">April 2012 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="105" bgcolor="#FFFFFF">
</td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">
</td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="79.5" bgcolor="#FFFFFF">
</td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="79.5" bgcolor="#FFFFFF">
</td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="100.5" bgcolor="#FFFFFF">
</td></tr> <tr><td rowspan="1" colspan="1" align="left" height="102" valign="bottom" width="141" bgcolor="#FFFFFF">Net Funds Added </td><td rowspan="1" colspan="1" align="left" height="102" valign="bottom" width="105" bgcolor="#FFFFFF">% Commission VAT-ex </td><td rowspan="1" colspan="1" align="left" height="102" valign="bottom" width="127.5" bgcolor="#FFFFFF">Visits VAT-ex </td><td rowspan="1" colspan="1" align="left" height="102" valign="bottom" width="79.5" bgcolor="#FFFFFF">Av Price VAT-ex </td><td rowspan="1" colspan="1" align="left" height="102" valign="bottom" width="79.5" bgcolor="#FFFFFF">Charge VAT-ex </td><td rowspan="1" colspan="1" align="left" height="102" valign="bottom" width="100.5" bgcolor="#FFFFFF">Commission VAT-ex </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="141" bgcolor="#FFFFFF">£19,642.6 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="105" bgcolor="#FFFFFF">11.6 % </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">580 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="79.5" bgcolor="#FFFFFF">£6.6 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="79.5" bgcolor="#FFFFFF">£3,852.4 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="100.5" bgcolor="#FFFFFF">£446.9 </td></tr> </tbody></table>
but would like it like this. Is it possible please?

<table border="1" bordercolor="#999999" cellspacing="0"><tbody><tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">
</td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF">Feb-11 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF">Mar-11 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Net Funds Added </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> £ 444 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 2,607 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 6,014 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">% Commission VAT-EX </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF">0.0% </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF">12.4% </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF">12.1% </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Visits VAT-Ex </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> 43 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> 102 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Av Price VAT-Ex </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> £ - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 8.1 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 8.0 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Charge VAT-Ex </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> £ - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 348.3 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 816.0 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Commission VAT-EX </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> £ - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 43.2 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 98.5 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Retainer Revenue VAT-Ex </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> £ - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ - </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">% Commission VAT-Inc </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF">0.0% </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF">14.8% </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF">15.0% </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Visits VAT-Inc </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> 90 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> 320 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Av Price VAT-Inc </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> £ - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 7.3 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 5.5 </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="375" bgcolor="#FFFFFF">Charge VAT-Inc </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="91.5" bgcolor="#FFFFFF"> £ - </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 660.3 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="96" bgcolor="#FFFFFF"> £ 1,759.6 </td></tr> </tbody></table>

Thansk any advice.

 

[ruve1k]

Drag Values into row labels.

 

[powerpivotpro]

...which requires the other field list. See:

http://www.powerpivotpro.com/2010/06/powerpivot-field-list-and-excel-field-list-at-same-time/

 

[masplin]

Ah it is was the Sigma Values bit I was missing. Perfect knew it must be simple thanks a lot.

 

[ruve1k]

Rob, in v2 it can be done natively in the PowerPivot field list -- the "other" field list is not required.
http://blogs.msdn.com/b/analysisservices/archive/2011/07/15/adding-values-to-rows-and-columns.aspx

 

[powerpivotpro]

Very nice. Can't wait for our servers to come up to v2 next month.