Calculate Monthly Average Cases per Resource

[faustusxanthis]

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. 

[Ritaf1983 · Accepted solution]

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

[faustusxanthis]

Thank you so much @Ritaf1983. It worked!

[Ritaf1983]

Happy to help 😊