+ Reply to Thread
Results 1 to 7 of 7

Pivot table - slicers and calculated item donīt work togheter?

  1. #1
    Forum Contributor
    Join Date
    01-11-2013
    Location
    Sweden
    MS-Off Ver
    MS 365
    Posts
    188

    Pivot table - slicers and calculated item donīt work togheter?

    Hi,

    After i made som calculation with the items in my pivot table the slicers dont grey out as they did before. I think thats what i did at least. I cant find a way to make them come back, anyone who knows what to do?

    /Masun

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Pivot table - slicers and calculated item donīt work togheter?

    Can you attach a workbook please.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor
    Join Date
    01-11-2013
    Location
    Sweden
    MS-Off Ver
    MS 365
    Posts
    188

    Re: Pivot table - slicers and calculated item donīt work togheter?

    Ok, here comes a sample...Hope you can help me!
    Attached Files Attached Files

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Pivot table - slicers and calculated item donīt work togheter?

    Now can you explain exactly what the problem is? Everything appears to work as intended in your attachment...

  5. #5
    Forum Contributor
    Join Date
    01-11-2013
    Location
    Sweden
    MS-Off Ver
    MS 365
    Posts
    188

    Re: Pivot table - slicers and calculated item donīt work togheter?

    Ok,

    First of all, thank you for trying to help me. Sorry for not making any sense. My bad.

    Well, if i mark Paris in the slicer named "City" i want just the types that are connected to Paris to be active in the slicer named "Type". I want those who do not have any association with Paris to be "greyed down". For Paris that should be Floors, Garbage, Roofs, Water, WC. Unfortunately they are always highlighted no matter which city i choose in the slicer. Do you understand what i mean?

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Pivot table - slicers and calculated item donīt work togheter?

    Your calculated items prevent excel from separating Types - they are performing calculations on Categories, and multiple Types exist within those categories, so Excel can no longer identify which Types have no data for the filters applied. It's a bit like mixing up apples and oranges in an opaque bag - once you are counting bags, you can no longer separate the apples and the oranges!

    If you remove the calculated items, but carefully group your Categories, you can achieve almost the same end result, but your slicer filters work the way you want. See attachment for an example.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-11-2013
    Location
    Sweden
    MS-Off Ver
    MS 365
    Posts
    188

    Re: Pivot table - slicers and calculated item donīt work togheter?

    Hi,

    Thank you for your answer and your work around.

    /Masun

+ 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. [SOLVED] pivot table formulas for calculated field or calculated item
    By Vicky in forum Excel General
    Replies: 3
    Last Post: 06-06-2006, 12:10 AM
  2. [SOLVED] calculated item does not work in grand total column- Pivot
    By Valerie_NOLA in forum Excel General
    Replies: 0
    Last Post: 02-10-2006, 02:35 PM
  3. [SOLVED] calculated item does not work in grand total column in Pivot
    By Valerie_NOLA in forum Excel General
    Replies: 0
    Last Post: 02-10-2006, 02:35 PM
  4. [SOLVED] calculated item does not work in grand total column in Pivot
    By Valerie_NOLA in forum Excel General
    Replies: 0
    Last Post: 02-10-2006, 02:30 PM
  5. Pivot Table with calculated Item
    By Greg Doucette in forum Excel General
    Replies: 0
    Last Post: 04-12-2005, 02:06 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