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:

     

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

hi Anonymous 

try to plot a visual with month column and a measure like:

measure =
DIVIDE(
   COUNTROWS(
        FILTER(
           data, 
           data[Month]<=MAX(data[Month])
        )
    ),
   MAX(data[Month]) 
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you FreemanZ 

     

    I tried it with my real data, and this result is still not correct.

    (The data type for month column is text right now)

     

    The result from Power BI - 

    I think it is because the number in each month is divided by the number of month instead of the sum number.

     

     

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

      hi Anonymous 

      could you update your sample dataset to better reflect your case?

      • 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.