Keeping Blank Columns when applying a slicer to a table from PowerPivot

katecunn

New Member
Joined
Nov 19, 2014
Messages
2
Hi,

I have searched everywhere and cannot seem to find an answer to this issue.

I have a couple of pivot tables built from a powerpivot query of a database on one of our servers.

These tables are showing similar information so i have applied a couple of slicers, such as owner and business divsion.

What i need to have is that all the coloums and rows stay there when i select certain slicers as these values are used in a lookup query to populate an overview table for key end users.

However no matter what i try when i choose a slicer it will only show the rows and coloums that have data in them, I am more concerned with the coloums then the rows remaining.

When i chose field settings -> layout & print, " Show Items withn no data" is greyed out

I have tried to create a calculated field at the powerpivot level to show 'IF(ISBlank)' because they are not blank untill i apply the slicers within my workbook.

I really hope someone can help me.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
For clarity...

There are 2 places you do power pivot formulas, Calculated Columns (on the actual power pivot tables), and Calculated Fields (called Measures by old timers). The latter is always dynamic and only evaluated when executed.

To me it sounds like you are talking about a calculated COLUMN above, not a field?
 
Upvote 0
Yes I just want to keep the exact amount of columns across in the pivot table no matter what slicers are applied.

Thanks
 
Upvote 0
If you are using a measure (not a calc column), then your technique of testing IF (ISBLANK(), 0, ...) should certainly work.
 
Upvote 0
Out of interest, what exactly does this mean:
these values are used in a lookup query to populate an overview table for key end users
 
Upvote 0
Another option would be to use CUBE formulas instead of a Pivot for your overview report. This allows you to have a fix structure of your report that still works with slicers.

Just select your pivot and go to Options->OLAP Tools->Convert to formulas
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top