SUMMARIZE TABLE TOP 10 with time ranges

[Niiru1]

I have a large table for 50 school students attendance per hour

School Name	Hours Range	Date	Value
School 1	09.00 - 10.00	14/09/2021	23
School 1	10.00 - 11.00	14/09/2021	11
School 1	11.00 - 12.00	14/09/2021	16
School 1	09.00 - 10.00	15/09/2021	20
School 1	10.00 - 11.00	15/09/2021	10
School 2	09.00 - 10.00	14/09/2021	7
School 2	10.00 - 11.00	14/09/2021	3
School 2	11.00 - 12.00	14/09/2021	11
School 2	09.00 - 10.00	15/09/2021	6

My aim is to PARTITION / get the TOP 10 schools by SUM into a SUMMARIZED table with all hour ranges or the ability to create a table I can create relationship to

Top 10 School	Hour Ranges	Dates	Summed Values
School 47			150
School 23			140
School 12			130
School 26			120
School 6			110
School 38			100
School 1			80
School 2			27

I have been able to get a SUMMARIZED table of the top 10 summed values with this DAX but I'm having trouble getting all the hour ranges and all the dates.

Measure 'DescendingTable' = 

VAR A = SUMMARIZE('Table','Table'[School Name],
                   "TotalAttendance",SUM('Table'[Value]),"Values",[SUM of values])
VAR B = TOPN(10,A,[TotalAttendance],DESC)
VAR C = ADDCOLUMNS(B,"Rank",RANKX(b,[TotalAttendance],,DESC,Dense))
//VAR D = ADDCOLUMNS(C,"Hours",VALUES('Table'[Hours Range]))

RETURN
c

Thanks in advance

[v-angzheng-msft]

Hi, @Niiru1 

Yes, you can't get it by that measure. Please check your measure. In variable A, you have summarized the school name, but you have not summarized the hour range and date. Variable A returns a unique table containing all schools. In variable D, you want to return all hour ranges and dates. The number of rows in the calculation table (10 rows?) obtained by the previous variable determines that the returned table cannot contain all hour ranges and dates.
So if you want to return the hour range and date, then you need to modify the variable A, and you need to decide which hour and which date you want to return, because a school has different time ranges and dates.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Can you post a depiction of the expected output including the "all hour ranges" please? If you can add more schools to the sample table (there are only two) it would also help (as opposed to having to type them ourselves without knowing the nature of the data)