# Transposing values to be rows



## masplin (Apr 27, 2012)

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 (Apr 29, 2012)

Drag Values into row labels.


----------



## powerpivotpro (Apr 29, 2012)

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

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


----------



## masplin (Apr 30, 2012)

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


----------



## ruve1k (Apr 30, 2012)

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 (Apr 30, 2012)

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


----------

