none
Filter Top 10

    Question

  • I am working with PowerPivot and I have a pivot table for a PowerPivot table.    I want to be able filter the top 10 courses that employees completed and also filter certain courses (names).  Is there a calculated that can do this?

    Tuesday, April 30, 2013 7:30 PM

Answers

  • Lucas, 

    There are a number of methods you can use for the top 10 but the easiest is always going to be in the Pivot table itself:

    There are some other methods that can be exploited using RANKX(), TOPN() or maybe CUBERANKEDMEMBER(). 

    In terms of filtering for certain courses generally you would again fall back on the Pivot either through a report filter or a slicer. If you wanted to write a measure that was just the completed courses for a certain subject you could go with something like (assuming you have a measure called [Completed] which is a simple count of completions):

    = CALCULATE([Completed],
                  table1[Subject] = "Maths"
               ) 

    Hope this helps.

    Jacob


    Jacob | Please mark helpful posts and answers

    Wednesday, May 01, 2013 1:41 AM
  • Lucas, I'm guessing that you are unselecting the course in the drop down on the column header. 

    What you need is either a report filter or a slicer for the courses - at this point it will only ever return data for those selected and the top 10 thing will work just fine.


    Jacob | Please mark helpful posts and answers

    Wednesday, May 01, 2013 3:15 AM
  • Use a named set.

    Unrecognized Excel MVP (UEM)

    Thursday, May 02, 2013 1:19 AM

All replies

  • Lucas, 

    There are a number of methods you can use for the top 10 but the easiest is always going to be in the Pivot table itself:

    There are some other methods that can be exploited using RANKX(), TOPN() or maybe CUBERANKEDMEMBER(). 

    In terms of filtering for certain courses generally you would again fall back on the Pivot either through a report filter or a slicer. If you wanted to write a measure that was just the completed courses for a certain subject you could go with something like (assuming you have a measure called [Completed] which is a simple count of completions):

    = CALCULATE([Completed],
                  table1[Subject] = "Maths"
               ) 

    Hope this helps.

    Jacob


    Jacob | Please mark helpful posts and answers

    Wednesday, May 01, 2013 1:41 AM
  • Hi Jacob,

    Thanks for you response.  Normally I would filter by Top 10 in the pivot table, but in addition after I filter the Top 10, I want unselect specific courses as well.  Therefore, this would have the pivot table only select the Top 10 that I have selected.  When I unselect courses and then filter by Top 10, the unselected courses become all selected again.  That's my dilemma.  Is there a DAX formula show unselected or NOT function?

    Lucas

    Wednesday, May 01, 2013 1:58 AM
  • Lucas, I'm guessing that you are unselecting the course in the drop down on the column header. 

    What you need is either a report filter or a slicer for the courses - at this point it will only ever return data for those selected and the top 10 thing will work just fine.


    Jacob | Please mark helpful posts and answers

    Wednesday, May 01, 2013 3:15 AM
  • Use a named set.

    Unrecognized Excel MVP (UEM)

    Thursday, May 02, 2013 1:19 AM