Forum Discussion

kylerwimbush's avatar
kylerwimbush
Icon for Helper I rankHelper I
3 years ago
Solved

Get Top 10 Employees based On Total Score

I have a table with employee names called DTM Info, and the column is called Full Name. I have a measure called Total Score which is just every score added together. I want to create a measure to show the Top 10 Employees with the highest scores but I am having difficulty using the TOPN function because it still returns every single employee name with their score, not just the top 10. Could anyone steer me in the right direction? 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    HI,

    Thank you for your message, and please check the below screenshot and the attached pbix file.

    I amended to have a measure in the formula.

     

    Show top5 only V2: = 
CALCULATE (
    [Score total:],
    KEEPFILTERS (
        WINDOW (
            1,
            ABS,
            5,
            ABS,
            ALL ( 'Name'[Full Name] ),
            ORDERBY ( [Score total:], DESC )
        )
    )
)

     

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, and how your expected outcome looks like, but I tried to create a sample pbix file like below.

    Please check the below picure and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

    Show top5 only: = 
CALCULATE (
    SUM( Score_fact[Score]),
    KEEPFILTERS (
        WINDOW (
            1,
            ABS,
            5,
            ABS,
            ALL ( 'Name'[Full Name] ),
            ORDERBY ( CALCULATE( SUM( Score_fact[Score]) ), DESC )
        )
    )
)

     

    • kylerwimbush's avatar
      kylerwimbush
      Icon for Helper I rankHelper I
      3 years ago

      The Score is stored as a measure called [Total Score] so is there a way you can work that around to work for a measure instead of a column? 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        HI,

        Thank you for your message, and please check the below screenshot and the attached pbix file.

        I amended to have a measure in the formula.

         

        Show top5 only V2: = 
CALCULATE (
    [Score total:],
    KEEPFILTERS (
        WINDOW (
            1,
            ABS,
            5,
            ABS,
            ALL ( 'Name'[Full Name] ),
            ORDERBY ( [Score total:], DESC )
        )
    )
)

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    This pattern should work

    =calculate([Total score],Top(5,all('DTM Info'[Full name]),[Total score]),values('DTM Info'[Full name]))

    Hope this helps.