Results 1 to 2 of 2

Thread: Pivot Table Calculated Fields
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Pivot Table Calculated Fields

    My source data is in Power Pivot, pulled directly via SQL, and I have measures that calculate over 20 days, 10 - 20 days or under 10 days. These are based on a calculated column that categorises each line accordingly (from a Task age column).

    I want to create a pivot table that shows max task age and a count of under 10, 10-20 and 20+ records. The issue I have is if I use the measures in the values section of my pivot I can get the front-end look I want, but when I double-click a record, e.g 20+ for Cat 1 it gives me the data for all the age ranges for Cat 1, not just those over 20 days.

    If I use the calculated column (Task category) values in the columns area of the pivot I get the look I want, plus clicking a record gives me the filtered data, but I can't include the max task age as it tries to categorise it under the columns field.

    Any guidance on how to make measures only present the relevant source data when in a pivot table, or how to make a measure not be sub-categorised by a column area in the pivot would be greatly appreciated.

    Team / Category Under 10 10 - 20 20+ Total
    Team 1
    Cat 1
    Cat 2
    Cat 3
    Cat 4
    Cat 5

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Excel 2003, Australia
    Post Thanks / Like
    7 Post(s)
    3 Thread(s)

    Default Re: Pivot Table Calculated Fields


    One way with a pivot table - any pivot table, doesn't need power pivot - is instead of the calculated field functionality via the normal worksheet interface to create the field within the dataset from the database.

    Such as, the current dataset is defined by "SELECT * FROM database_table"

    Instead use "SELECT *, IIF(Task_Age < 10, "Under 10 days", IIF(Task_Age < 20, "10-20 days", "20+ days")) AS TaskAge FROM database_table"

    Some ways this can be set up starting either with the pivot table wizard ALT-D-P and choosing external data source at the first step, or, ALT-D-N-N and follow the wizard. Either way, at the last step take the option to edit in MS Query & via the SQL button edit the SQL to add the field to the dataset.

    regards, Fazza
    Last edited by Fazza; Jul 17th, 2019 at 02:04 AM.
    To receive a better answer, put more work into asking the question.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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