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.
<tbody>
</tbody>
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 |
<tbody>
</tbody>