Pivot Table Calculated Fields

OllieKP

New Member
Joined
Jul 16, 2019
Messages
1
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 / CategoryUnder 1010 - 2020+Total
Team 1
Cat 1
Cat 2
Cat 3
Cat 4
Cat 5

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
welcome,

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:

Watch MrExcel Video

Forum statistics

Threads
1,102,645
Messages
5,488,077
Members
407,623
Latest member
Deigs

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top