Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Slicers and Calculated Items in Pivot Table

763 views
Skip to first unread message

League

unread,
Jun 13, 2013, 11:36:18 AM6/13/13
to

I have a Pivot Table/Pivot Chart with slicers. There are 4 slicers
attached. When one slicer is filtered the other slicers will filter(grey
out) accordingly. But I also have some calculated items in Column Label
field. When 1 of these calculated items is selected, the slicers all
lose their filter link to each other. (unavailable items in other
slicers are no longer greyed out) Non-calculated items don't affect the
slicers this way.

Once the slicers are unlinked I can't get them to link with each other
again.(I have to open an earlier copy)
All the slicer fields are in the Report Filter section of pivot and data
is too large to move these fields to row labels section of pivot.
Pivot source is a MS query connection to an Access database.
Calculated Items are ratios and are calculated to the desired level by
the use of the Report Filters.

This workkbook will be used by management so I need an easy way to
select only items that are still 'available' after 1 or more of the
Report Filters is used. Slicers looked like the answer but any
suggestions would be helpful.




--
League

League

unread,
Jun 13, 2013, 3:07:12 PM6/13/13
to

League;1612272 Wrote:
> Once the slicers are unlinked I can't get them to link with each other
> again.(I have to open an earlier copy)
>

Slicers do relink when calculated items are filtered out.




--
League

League

unread,
Jun 21, 2013, 10:00:24 AM6/21/13
to

I figured out a workaround for my issue.

My calculated items are ratios and I need the ability to roll up to
different levels so I created a second pivot table with just my slicer
fields as report filters and attached slicers. The fields are also
Report Filters in the first pivot. Got the idea from this
site:http://tinyurl.com/opztr5x Slicers are not attached to first pivot.
Then I run a macro that updates the first pivot Report Filters with
values from the second pivot. User selects wanted values in the slicers
then clicks button to run macro. I only want "all" or one value at a
time in each slicer so this works for me. Not as elegant as I would like
but it works.




--
League
0 new messages