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

 

[GraH]

In the raw data add a column with a formula =score >=80
Use this field in the pivot.
Or using a Dax measure within a data model (PowerPivot), like Score80Plus=SUMX(Range,if([Score]>=80,1,0))

Book1
	A	B	C	D	E	F	G	H
1	Name	Subject	Score
2	Jack	Maths	84
3	Fred	Chem	73
4	Pete	English	89			Row Labels	Score80Plus
5	Harry	French	88			Chem	2
6	Mary	Maths	60			English	1
7	Sally	Chem	62			French	3
8	Don	English	79			Maths	2
9	Ken	French	91			Grand Total	8
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

 

[Tigerexcel]

Thanks Sandy for the clarification.
Thanks Gra, even adding the extra field it's still a bit of work to count the scores over 80. Just a limitation of PTs I guess.

 

[sandy666]

Thanks Sandy for the clarification.

You are welcome

 

[GraH]

Thanks Gra, even adding the extra field it's still a bit of work to count the scores over 80. Just a limitation of PTs I guess.

I don't agree, it is a single measure in DAX, which is made to do heavy calculation. The work takes like 5 seconds and only once. New data? Simply refresh the pivot. Certainly when you store the data inside a table.

 

[Tigerexcel]

I don't agree, it is a single measure in DAX, which is made to do heavy calculation. The work takes like 5 seconds and only once. New data? Simply refresh the pivot. Certainly when you store the data inside a table.

But it's yet another thing to learn ie DAX. I do understand your point though, once setup it does allow for greater scope in calculating. Looking back at my original post I could have done it with a COUNTIF function but I like the notion of being able to set up 1 structure like a PT and doing all the analyses. I will explore DAX further.