Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Average count in each month

Hi,   I tried to calculate the average of number of employee in each month. Here's the example of my data, there is no duplicate of employee id in each month.   The result i would like to ...
average
average count
average employee
DAX
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

    I overlooked the filter context, try like:

     

    measure = 
DIVIDE(
   COUNTROWS(
        FILTER(
           ALL(data), 
           data[Month]<=MAX(data[Month])
        )
    ),
   MAX(data[Month]) 
)

     

    tried with your original dataset, it worked like:

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Sure, my data looks like this, I have around 15k+ employees in each month.

 

And this the expected result is in red box.

 

 

 

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi Anonymous 

I overlooked the filter context, try like:

 

measure = 
DIVIDE(
   COUNTROWS(
        FILTER(
           ALL(data), 
           data[Month]<=MAX(data[Month])
        )
    ),
   MAX(data[Month]) 
)

 

tried with your original dataset, it worked like:

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Yes, it worked!

    Thank you a lot!