Forum Discussion

faustusxanthis's avatar
faustusxanthis
Frequent Visitor
3 years ago
Solved

Calculate Monthly Average Cases per Resource

Good day, everyone!

 

I have already tried researching multiple boards and have also searched online but I can't seem to get the right formula on this one. 

I have a dataset like this: 

 

What I want to do is to calculate average monthly cases per resource. Such that it will look like this (sample numbers only): 

 

I can't seem to find the right measure to do the calculation. I have tried AVERAGEX(VALUES([Closed Month], COUNT([Case ID])) but what it's displaying is the total/sum of all cases per resource.

 

I appreciate any help. 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi faustusxanthis 
    If i understand you correctly please modify your dax formula to :

    Test Average = divide( SUMX(VALUES('Table'[Resource]), COUNT([Case id])),DISTINCTCOUNT('Table'[Closed Month]))

     

    Link to a sample file 

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

3 Replies

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

    Hi faustusxanthis 
    If i understand you correctly please modify your dax formula to :

    Test Average = divide( SUMX(VALUES('Table'[Resource]), COUNT([Case id])),DISTINCTCOUNT('Table'[Closed Month]))

     

    Link to a sample file 

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