Adding Values to Rows and Columns

 

The release notes for CTP3 references the ability to add values to rows and columns. But what does this really mean? Inspired by quite a few questions on this already I thought I’ll try to provide a little more details on this.

In the RTM version of PowerPivot you can add multiple measures to the values area of a PivotTable, but you are not able to add measures to rows and columns. Let me add some examples to illustrate.

Behavior in PowerPivot RTM

If you add multiple measures, let’s say Sales Amount and Budget Amount - to the values area of the Field List the two measures will show in the values area of the PivotTable as shown below.

clip_image001

Now if you add another field, let’s say Year to the Column Labels the two measures you added previously will be nested within the newly added column field.

In my example, the Sales Amount and Budget Amount will be nested under the columns of Year 2003 and Year 2004. The measures will always show up nested under columns. This measure location cannot be changed meaning that you will not be able to reverse the order and show Year nested under Sales Amount and Budget amount. Nor will you be able to show Sales Amount and Budget Amount on rows. This is the behavior in the RTM version of PowerPivot.

clip_image003clip_image005

New behavior in CTP3 of Denali

The new feature listed in the release notes for CTP3 as the ability to add values to rows and columns will allow you to accommodate these limitations. Going back to the example, in addition to nesting Sales and Budget within Years on columns I will now be able to show my measures in three other locations as follows:

I can show Year nested within the Sales and the Budget Amount on columns:

clip_image007 clip_image009

 

I can add measures to rows nesting Sales and Budget Amount within Product:

clip_image011 clip_image013

 

I can add measures to rows nesting products within Sales and Budget Amount:

clip_image015clip_image017

 

In summary using CTP3 you will be able to show measures on rows in addition to columns and to show fields nested within measures on both rows and columns. An increased flexibility, which will greatly enhance the PivotTable layout

Hope this helps!