Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

getting a column value based on the score

hi ,

 i have matrix below  where each case owner has score based on the total number of case 

i would like to get the case owner who has max score for the current month and last month , to find score am using 

score = (CALCULATE(DIVIDE([Cases Resolved Within TAT] , [Total Case])))
 
i have current month total case and current month resolved case within tat , now i need the user name to be displayed in a card who has the max score 
General Comment
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , sorry small modification

     

    top OWNER THIS MONTH = CALCULATE(max(incidents[owninguser.fullname]),TOPN(1,allselected(incidents[owninguser.fullname]),[current month score],DESC),VALUES(incidents[owninguser.fullname]))

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try measures like

     

    last MTD score = CALCULATE([score],DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    top OWNER THIS MONTH = CALCULATE([score],TOPN(1,allselected(Table[case owner]),[score],DESC),VALUES(Table[case owner]))

     

    top OWNER lAST MONTH CALCULATE([score LMTD],TOPN(1,allselected(Table[case owner]),[score LMTD],DESC),VALUES(Table[case owner]))

     

    Use date table and time intelligence for last two measure

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hi, its giving me some value not i expected 

       

      i used 

      current month score = DIVIDE([Current month resolved case], [Current month Total case]) then 
      i used 
      top OWNER THIS MONTH = CALCULATE([current month score],TOPN(1,allselected(incidents[owninguser.fullname]),[current month score],DESC),VALUES(incidents[owninguser.fullname]))
       
      its giving me some number , i actually need top scorer owning user column value 
      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous , sorry small modification

         

        top OWNER THIS MONTH = CALCULATE(max(incidents[owninguser.fullname]),TOPN(1,allselected(incidents[owninguser.fullname]),[current month score],DESC),VALUES(incidents[owninguser.fullname]))