+ Reply to Thread
Results 1 to 5 of 5

Only display pivot table items that meet ALL selected slicer criteria?

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    Illinois, USA
    MS-Off Ver
    Office 365
    Posts
    49

    Only display pivot table items that meet ALL selected slicer criteria?

    I've been looking for several hours, and am unsure if this is possible. For a call center report, I am looking for a way to display items in a pivot table only if ALL criteria selected in the slicer are met. For example, we have several incoming lines that we'll call A thru Z - I want to be able to see everyone that takes calls on line A, B and C (or any other combination) combined, but when selecting them in the slicer now I may retrieve employees that have line A but not B or C. Is this possible?

    Thank you in advance for any advice you can provide

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Only display pivot table items that meet ALL selected slicer criteria?

    I'd recommend uploading sample workbook (with desensitized info). See yellow banner at the top.

    Pivot Table behavior will largely depend on underlying data structure (be it Excel range, Data Model etc).

    From your description, I'd guess that you'll need to denormalize your data to change behavior.
    And/or create dimension tables with one to many relationship to the fact table.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    08-16-2016
    Location
    Illinois, USA
    MS-Off Ver
    Office 365
    Posts
    49

    Re: Only display pivot table items that meet ALL selected slicer criteria?

    No problem! I've uploaded a redacted workbook. As a simple example - a data table sheet and a pivot sheet. I've selected skills 1, 3 and 5 as an example. Although there are only 2 specialists with the Skill 5 line, 4 specialists display.

    This is a very small example. This is for a contact center of 300+ employees with approximately 50 different skillsets that employees can be trained to field calls on.

    Thank you again in advance for any help.
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Only display pivot table items that meet ALL selected slicer criteria?

    Ok, so you don't need to restructure your data. But you need creative use of DAX formula.

    Do you have access to PowerPivot? If not, I can't think of ways to do it off top of my head, using Pivot Table.

    If using PowerPivot you will need to add following measures.

    1.
    Please Login or Register  to view this content.
    2.
    Please Login or Register  to view this content.
    3.
    Please Login or Register  to view this content.
    4. Add [Validation] to Values field.

    With Skill 2, 3, & 4 selected. Result will look like below.
    0.JPG

    If you need other values returned in this manner, you'll need to replace 1 in [Validation] with appropriate DAX measure.
    Last edited by CK76; 07-23-2020 at 12:57 PM. Reason: punctuation

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Only display pivot table items that meet ALL selected slicer criteria?

    Oh, just realized I could have avoided use of iterator function COUNTAX().

    You can replace it with COUNTROWS(ALLSELECTED(Table1[Skill]))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Show value of highest slicer selections (multi selected items in slicer
    By saudi_red_neck in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-01-2020, 05:37 AM
  2. Replies: 7
    Last Post: 05-18-2017, 07:58 PM
  3. code that will count the number of items selected in a slicer on a pivot table
    By asmi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2017, 12:57 AM
  4. [SOLVED] Run macro to change pivot table row field when user selected a slicer item
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2016, 09:44 AM
  5. Replies: 2
    Last Post: 02-12-2014, 11:14 PM
  6. [SOLVED] Macro to Update a Cell Based on Number of Items Selected in Pivot Slicer
    By MBeedle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2013, 01:47 PM
  7. Replies: 1
    Last Post: 09-03-2012, 10:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1