+ Reply to Thread
Results 1 to 2 of 2

slicers for custom calculated field in excel pivot table/pivot chart

  1. #1
    Registered User
    Join Date
    07-13-2021
    Location
    England
    MS-Off Ver
    365
    Posts
    2

    slicers for custom calculated field in excel pivot table/pivot chart

    I have a multiple calculated fields in my excel pivot table.

    The field is in the values part of my pivot table and I sum them. I want to add a slicer to select them or not.
    i.e. I want my slicer to select basically hide and show AA1,AA2,...,AA8 in the chart and table.

    This is normally easy if the field already exists in the table, but because this is a calculated field I can not work out how to do it.
    My data is attached.

    Cheers
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: slicers for custom calculated field in excel pivot table/pivot chart

    It can be done with Power Pivot but I don't think that is possible in your Excel Version
    You need to use Power query to unpivot your original data in order to have AA1, AA2 as records in row. After that you need to create a measure as ratio between values and "n" column and create a Pivot Table from PowerPivot data model.
    The query:
    HTML Code: 
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type datetime}, {"n", Int64.Type}, {"AA1", Int64.Type}, {"AA2", Int64.Type}, {"AA3", Int64.Type}, {"AA4", Int64.Type}, {"AA5", Int64.Type}, {"AA6", Int64.Type}, {"AA7", Int64.Type}, {"AA8", Int64.Type}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Date", "n"}, "Attribute", "Value")
    in
        #"Unpivoted Other Columns"
    and the measure:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Diana Tanase

+ 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. Replies: 2
    Last Post: 07-13-2021, 03:32 AM
  2. [SOLVED] Pivot table - slicers and calculated item donīt work togheter?
    By Masun in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 12-19-2013, 01:56 AM
  3. Replies: 2
    Last Post: 05-24-2013, 03:58 PM
  4. Calculated pivot table field using pivot table data in calculation
    By BrittleStar in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-10-2012, 05:35 PM
  5. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  6. Replies: 1
    Last Post: 04-12-2010, 01:01 PM
  7. Excel 2002 Pivot Table calculated field percentage.
    By ~Dave in forum Excel General
    Replies: 3
    Last Post: 08-03-2005, 02:05 PM

Tags for this Thread

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