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.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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?
 

katecunn

New Member
Joined
Nov 19, 2014
Messages
2
Yes I just want to keep the exact amount of columns across in the pivot table no matter what slicers are applied.

Thanks
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
If you are using a measure (not a calc column), then your technique of testing IF (ISBLANK(), 0, ...) should certainly work.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,156
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
 

Tianbas

Board Regular
Joined
Apr 29, 2014
Messages
101
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
 

GDRIII

Board Regular
Joined
May 30, 2014
Messages
88
Word of caution, once you convert to OLAP, you can't undo it.
 

Forum statistics

Threads
1,077,994
Messages
5,337,613
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top