View RSS Feed

The ExcelGuru (Ken Puls) Blog

Creating a Spacer Column in a PowerPivot PivotTable

Rating: 11 votes, 5.00 average.
I was amused to read Mike Alexander's blog post on Add Column Spacing In A PivotTable this morning, as I did that exact thing yesterday while working through a PowerPivot solution. At least, I did the same thing, but I did it using DAX. And in this case, DAX is even easier to use that the old method, taking one less step.

Keep in mind I'm using Excel 2013 for this, but it should be the same in 2010.

First I created a new Calculated Field (Measure in 2010):
Name:  calfield.jpg
Views: 7458
Size:  50.4 KB

As you can see, I called it "Spacer", and the formula is simply =BLANK()

From there it gets added to the PivotTable and already shows up as blank values. (No longer necessary to make the custom number format that Mike showed us with an old-school PivotTable).

At that point, I just went to one of the fields on the worksheet that said "Spacer", and entered a single space into the cell, just like Mike did. (Unfortunately, you can't name the measure/calculated field with a single space in the setup, or we wouldn't even have to do that!)

End result (with the blank colum selected so you can see it):

Name:  pivot.jpg
Views: 4588
Size:  59.6 KB

Submit "Creating a Spacer Column in a PowerPivot PivotTable" to Digg Submit "Creating a Spacer Column in a PowerPivot PivotTable" to del.icio.us Submit "Creating a Spacer Column in a PowerPivot PivotTable" to StumbleUpon Submit "Creating a Spacer Column in a PowerPivot PivotTable" to Google Submit "Creating a Spacer Column in a PowerPivot PivotTable" to Twitter Submit "Creating a Spacer Column in a PowerPivot PivotTable" to Facebook Submit "Creating a Spacer Column in a PowerPivot PivotTable" to LinkedIn

Categories
PowerPivot , Excel , PivotTables

Comments