Forum Discussion

Jaweed's avatar
Jaweed
Icon for Helper III rankHelper III
6 years ago
Solved

TOP N in DAX

Hello friends

I would like to display the count of TOP 3 count of Staff. I cannot get it right.

My table is as below

John

John

John

John

Rob

Rob

Rob

Rob

Bill

Bill

Alex

Sam

Dough

 

I would like a table visual to show top 3 count of staff - Only these 3 to be displayed

Name       Count

John         4

Rob          4

Bill            2

 

Appreciate some help. Thanks.

 

 

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Jaweed 

    add measure

    r = rankx(ALL('Table'[Name]);calculate(COUNT('Table'[Name]));;;Skip)

    Next, in the visual filter pane set "r is less then 4"

    do not hesitate to kudo useful posts and mark solutions as solution
    Linkedin

17 Replies

      • littlemojopuppy's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        6 years ago

        This is going to be pseudocode but it should be pretty close

        TOPN(3, SUMMARIZE(Table, Table[Name], "NameCount", COUNT(Table[Name])), NameCount)

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Jaweed 

    add measure

    r = rankx(ALL('Table'[Name]);calculate(COUNT('Table'[Name]));;;Skip)

    Next, in the visual filter pane set "r is less then 4"

    do not hesitate to kudo useful posts and mark solutions as solution
    Linkedin

      • Jaweed's avatar
        Jaweed
        Icon for Helper III rankHelper III
        6 years ago

        The "ALL" in the DAX formula in filtering causes visual not to display correct records.

    • Jaweed's avatar
      Jaweed
      Icon for Helper III rankHelper III
      6 years ago

      If the records are filtered on a weekno ( on each row), how can we adapt the rankr.

      Ie

      Name          weekno 

      Bob               5

      Bob               5

      Bob              8

      Bob              4

      Tim               5

      Tim               5

      Tim              5

      Tim              5

      Bill               5

       

      Top 2  filtered by weekno ( as per a slicer  say weekno between 4 and 5). Output is for Top 2, we should get

      Name   Count of Name

      Tim       4

      Bob      3

      Only these 2 records should appear.

       

      Thanks to help in when filtering is added. I am figuring how to mahe first a virtual table and have the Top 2 on it, but cannot get it to work.

       

       

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Jaweed 

        my solution works good

        do not hesitate to kudo useful posts and mark solutions as solution
        Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Jaweed ,

    HYG - 

    TOP =
    Var Rnk= RANKX(ALL(Sample1[Name]),CALCULATE(COUNT(Sample1[Name])),,DESC,Skip)
    RETURN
    IF(Rnk<=3,CALCULATE(COUNT(Sample1[Name])),BLANK())

    Output:

     
    Best Regards,
    Mail2inba4

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.