Calculated Fields as filter

[cmcreynolds]

Hello, I have a calculated field that counts the number of courses each student takes. (My data is set up such that each course a student takes is listed on a separate row). The field works just fine, but now I'd like to look at those who have fewer than 6 courses - in other words, use my [coursecount] as a filter - but it blocks me from using it as a filter (greyed out).

Any workaround thoughts?

I am using Excel 2013 (can't find a version of PowerPivot)

Thank you in advance.

 

[cmcreynolds]

Sorry, I'm horrible in the afternoons...

"use my [coursecount] as a filter in the pivot table...."

 

[Gingertrees]

You are using somebody else's workbook, I take it?

 

[cmcreynolds]

I'm pulling data from our client - so, yes? My calculated field is just "counting" but when I try using it as a filter in the pivot table, everything is greyed out.

 

[Matt Allington]

Tree are 2 options.
1. Use a filter in your pivot table. Click the pivot table row filter, and then filter on Values <6
2. Create a calculated column that replicates the measure and use that as a filter.

 

[cmcreynolds]

Matt - Therein lies the issue - the pivot table won't allow me to use a calculated field as a filter - it grey out the option and when I click and drag it to the filter pane, a message pops up saying I cannot do that. And as for #2 - since I'm using a Count by student number, I can't conceive of how to convert it to a calculated column vs a field.

 

[Matt Allington]

Sorry, I didn't explain that well enough.
1. Click on the drop down button at the top of ROWS on your Pivot Table
2. Select Value Filters
3. Select Greater Than (or what ever you need)

Then apply the filter you want.

 

[cmcreynolds]

Oh, I see - in the table itself not in the field list. Thank you, very much!