TOP 10 from a table RRS feed

  • Question

  • Hi,

    I have a table in my PowerPivot model with fields say "Student Name" and "Marks", which has millions of rows.Requirement is to pull only top 10 student wrt Marks.

    I know i can connect the PP table in pivot in the tab and then sort & filter for top 5, but with this i pull entire table and then it filters, which consumes memory.

    Is it possible to use CubeSet or something and have only top 5 student pulled in the tab.


    Rahul Kumar

    Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/

    Monday, December 21, 2015 5:12 AM


  • When you do this in an Excel pivot table, Excel will indeed compute the results for all rows. The trick is to create a measure that will only return results for the top 10.

    Start with a measure that calculates the marks by student. As you cannot use a column directly, it has to be aggregated. I would assume the average mark is a good candidate for this:


    The TOPN function returns the top rows, based on a measure. In our case, this will be [AvgMark]:


    When you use [AvgMarkTop10] in a pivot table with Student Name as row labels, for each row the context is changed from the selected student to the top 10 students. In other words, every row will show the average marks for the top 10. This is obviously not what you want. The last step needed is to reapply the original context as a filter, with the VALUES function. By doing this, the context will become the selected student if this student is in the top 10, and empty if the student is not in the top 10:

    MarksTop10Only:=CALCULATE([AvgMark],TOPN(10, Student, [AvgMark]),VALUES(Student[Student Name]))

    • Proposed as answer by Charlie Liao Tuesday, December 22, 2015 7:36 AM
    • Marked as answer by Michael Amadi Thursday, December 31, 2015 9:18 PM
    Monday, December 21, 2015 8:27 AM