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 see using Matrix visualization is in the red box 

from the second month and so on, the formula in my excel looks like this =AVERAGE($E$2:F2) 

F2 will change based on month, (G2 in march / H2 in april so on and so forth)

 

I've tried the averagex 

 

AVERAGEX (

VALUES ('HR Report 2023'[Month]),
CALCULATE ( COUNT ( 'HR Report 2023'[Employee ID]))
 

, however it only retuned the average in total (6), but I would like to see the average in each month.

And the result from the average will be the denominator to calculate the attrition.

 

Please help me.

*Sending appreciation in advance* and thank you a lot.

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:

     

6 Replies

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