Get Top 10 Employees based On Total Score

[kylerwimbush]

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? 

[Ashish_Mathur]

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.

[Jihwan_Kim]

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]

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 · Accepted solution]

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 )
        )
    )
)

[kylerwimbush]

That works, but it shows everyone elses names (not top 5) afterwards with a weird value. That doesn't matter if there is a way to limit the rows produced to 5. Is there a way that can happen? 

[Jihwan_Kim]

Hi,

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

I am not sure if I understood your question correctly, but try to deselect unnecessary measures from the visualization.