topic Re: Average count in each month in DAX Commands and Tips

Average count in each month

Anonymous — Thu, 11 May 2023 13:39:38 GMT

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.

Re: Average count in each month

FreemanZ — Thu, 11 May 2023 15:05:36 GMT

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])

)

Re: Average count in each month

Anonymous — Fri, 12 May 2023 02:44:25 GMT

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.

Re: Average count in each month

FreemanZ — Fri, 12 May 2023 02:57:39 GMT

hi Anonymous

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

Re: Average count in each month

Anonymous — Fri, 12 May 2023 03:16:24 GMT

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

And this the expected result is in red box.

Re: Average count in each month

FreemanZ — Fri, 12 May 2023 04:07:31 GMT

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:

Re: Average count in each month

Anonymous — Fri, 12 May 2023 04:11:35 GMT

Yes, it worked!

Thank you a lot!