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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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,335
Messages
5,486,233
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top