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

       

       

       

      • FreemanZ's avatar
        FreemanZ
        Super 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: