Top and Bottom 5

[Anonymous]

Hi Everyone, 

I know its a very simple query but I don;t know why I am not gettting the correct results through filter.

Top5 Conditions

I have student ID along with distance travelled in a race. I want to filter the students who have covered more than 100m in race also want the top5 studeny by distance cover (higher the distance would be first) along with I have given a rating so for Top category rating should also be 5. So two conditions here for Top 5 - Distance is more than 100m, Rating would be 5.

Bottom 5 Condition

Rating would be 1 and distance covered would be more than 100m again. (I dont want to consider 0 in bottom rating)

Below is sample data

Student Number	Distance Travelled	Rating
962457	102	5
943307	78	4
1002451	160	2
1278653	183	0
1889208	162	3
1953800	123	0
2139973	51	5
846809	32	0
961360	163	4
1855446	151	5
762847	178	4
1148625	123	1
2007506	171	0
1753921	178	0
2182136	54	4
2018665	55	0
1918456	181	3
1312026	53	3
513357	180	5
782507	107	5
253767	139	4
148666	197	2
1355566	55	0
415342	144	2
681480	90	4
1758550	68	5
2174231	41	3
1551268	159	3
824355	79	5
128139	181	5

I used a matrix where I have student name in first column, distance covered in second column, rating in third column. I filtered Rating as 5, Distance is greater than 100 but when I am filtering Top5 by distance it is showing blank.

Refer below snapshot

Kindly help.

Regards

Uphar

[Anonymous]

Hi,

I am able to produce the results by using the filter with Calculate

Top 5 = CALCULATE(SUM(table[Distance]),FILTER(Table,table[Star Rating]=5)) and then used filter for more than 100km

Thanks for your help.

Regards

Uphar

[sturlaws]

Hi @Anonymous 

I am not able to reproduce your result with the sample data you have provided:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.