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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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,462
Messages
5,487,024
Members
407,576
Latest member
aapnarritesh

This Week's Hot Topics

Top