Pivot table filter

[Tigerexcel]

I need to work out how many scores of 80 or more have been obtained by Course. In the sample data, Chem would have 2, English would have 1 etc. I can only do this through PTs if I filter the original data for scores 80 or more, copy to new sheet and then do a count. I'm thinking there must be a way within the original data setup with a PT that will allow for filtering. Tried Report filters and Calculated fields but couldn't get the desired output.

PT filter.xlsx
	A	B	C	D	E	F
1	Name	Subject	Score		Score	(All)
2	Jack	Maths	84
3	Fred	Chem	73		Row Labels	Count of Score
4	Pete	English	89		Chem	4
5	Harry	French	88		English	3
6	Mary	Maths	60		French	3
7	Sally	Chem	62		Maths	4
8	Don	English	79		Grand Total	14
9	Ken	French	91
10	Eddie	Maths	70
11	Sara	Chem	92
12	Joel	English	75
13	Tom	French	88
14	Jake	Maths	94
15	Noel	Chem	95
Sheet1

 

[sandy666]

maybe

or you can use Power Query then Pivot Table, up to you

 

[sandy666]

with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each [Score] >= 80)
in
    Filter

 

[Tigerexcel]

Hi Sandy, thanks for your input. Your post no. 2 is what I tried but in the actual data there are hundreds of entries over 80 and I couldn't find a shortcut to select multiple entries.
Your PQ option is nice but I am trying to upskill someone who is comfortable with PTs, if possible I'd like to avoid introducing PQ until COVID 19 is under control until I can teach them more efficiently than in a remote environment.
If there's no easy way in PTs, it does make me wonder how people would do such an analysis before tools like PQ were available.

 

[sandy666]

You can use vba to create PT or choose a filter but I can't help with this

 

[Tigerexcel]

You can use vba to create PT or choose a filter but I can't help with this

No problem, I don't really want to go down the VBA path but I guess it does show a limitation of PTs, they are very powerful but you sometimes need a workaround to get the desired result.

 

[sandy666]

You can also use extended Power Query with additional one-cell table to define your/user "filter"

   Filter = Table.SelectRows(Source, each [Score] >= defined_filter)

 

[sandy666]

example

Filter		Subject	Count of Score
90		Chem	2
		French	1
		Maths	1
		Grand Total	4

 

[Tigerexcel]

You can also use extended Power Query with additional one-cell table to define your/user "filter"

   Filter = Table.SelectRows(Source, each [Score] >= defined_filter)

Thanks Sandy, what do you mean by extended PQ?

 

[sandy666]

let
    def = Excel.CurrentWorkbook(){[Name="Table2"]}[Content][Filter]{0},
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each [Score] >= def)
in
    Filter

Filter		Subject	Count of Score
95		Chem	1
		Grand Total	1