Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating top 3 ranking per year

Dear community,

 

Let me start off by saying that I have seen quite a few posts on this topic, and although I have the feeling that the answers to my struggles is in there is was not able to distill it. So please, be kind to a lonely and rather new DAX user.

 

Anyway, I have attached a small PBIX file containing very similar data as the data I'm trying to work with. The data has employees, with a name and unique ID, and a table with worklogs, showing when an employee had worked on particular issues. I've created a measure to count the hours worked on a particular issue, that actually denotes the sick-hours. What I would like to do is create a matrix that shows per year ( or per month, the idea should be similar ) the top 3 sick employees. 

 

Like this ..

 But, as can be seen below ... I didn't get any further than creating a measure to determine the ranking. I have tried a couple a things, but never got close to the structure as shown above. 

 

 

This is a screenshot of the data structure I'm using.

A solution that I'm using now is to create a view in a DB that contains this ranking ... but I know it should be possible using only DAX, I'm just really struggling with it.

 

Hope to hear from you soon.

 

kind regards,

 

Vincent

 

 

Need Help

6 Replies

  • Anand24's avatar
    Anand24
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,
    Can you try to create below calculated measure and restrict data on it 

    Total SickHours = SUM(worklogs[SickHours])

Top N = CALCULATE ([Total SickHours], KEEPFILTERS(TOPN (3, ALL (TableName[Year]), [Total SickHours], DESC)))

     

     

    Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

    Proud To Be a Super User !!!
    LinkedIn
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey Anand24 ,

       

      Thank you for the quick reply. I've added the measure as you suggested, but I don't understand what to do with it next I'm afraid ....

  • Anand24's avatar
    Anand24
    Icon for Super User rankSuper User
    4 years ago

    Anonymous ,

    Take this measure into visual's filter and restrict data. If measure creates filtering issue, please create calculated column and then restrict.

     

    Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

    Proud To Be a Super User !!!
    LinkedIn
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I'm almost feeling stupid for asking ... but I'm still not following you ...

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     so it seems that this is not as trivial as it might seem .... In the meantime I have added a table that is almost in the structure that I would like to display ... being

     

    But still, the matrix is kicking me in every thinkable way. Anybody any ideas?

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Please create a new table:

     

    RankTable = {1,2,3}

     

    Then create measure:

     

    TOP3 = 
CONCATENATEX (
    FILTER (
        VALUES ( employees[name] ),
        [Ranking] = SELECTEDVALUE ( RankTable[Value] )
    ),
    [name],
    "; "
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.