Pivot Table Calculated Fields

[OllieKP]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Team / Category[/TD]
[TD="align: center"]Under 10[/TD]
[TD="align: center"]10 - 20[/TD]
[TD="align: center"]20+[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cat 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cat 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cat 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cat 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cat 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

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