Average count in each month

[Anonymous]

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.

[FreemanZ]

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]

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]

hi @Anonymous 

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

[Anonymous]

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

And this the expected result is in red box.

[FreemanZ · Accepted solution]

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]

Yes, it worked!

Thank you a lot!