cancel
Showing results for
Did you mean:
Highlighted Helper I

## DAX | Average of Count IF > 0

Hi there,

I have a data source will the name (and status and Department) of everyone in our organisation, for each month, for a couple of years. I am looking to write a DAX measure that will calculate the average headcount over a 12 month period.

The challenge I face is that when I drill down to some lover levels, I want to ensure that the average is caluated based on the month there is headcount and to ignore blanks.

I have got as far as calculating TOTAL headcount for the last 12 months;

`R12 Headcount:=CALCULATE(COUNTA(tbl_data_colleagues[Status]),FILTER(ALLEXCEPT(tbl_data_colleagues,tbl_data_colleagues[WL5 Head Of],tbl_data_colleagues[Functional Area],tbl_data_colleagues[Business Area]),(tbl_data_colleagues[Month ID]>=MAX(tbl_date_matrix[Month ID])-11)&&(tbl_data_colleagues[Month ID]<=MAX(tbl_date_matrix[Month ID]))),FILTER(ALLEXCEPT(tbl_data_colleagues,tbl_data_colleagues[WL5 Head Of],tbl_data_colleagues[Functional Area],tbl_data_colleagues[Business Area]),tbl_data_colleagues[Status]="Active"))`

Andy

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Helper I

## Re: DAX | Average of Count IF > 0

Hi,

I actually managd to adapt a solution from the tutorial I found here. In the end I;

• Worked out the total headcount over a 12 month period:

```12MR Headcount:=CALCULATE (
COUNTA ( tbl_output_colleagues[Status] ),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
(
tbl_output_colleagues[Month ID]
>= MAX ( tbl_date_matrix[Month ID] ) - 11
)
&& ( tbl_output_colleagues[Month ID] <= MAX ( tbl_date_matrix[Month ID] ) )
),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
tbl_output_colleagues[Status] = "Active"
)
)```

• Worked out the number of months in that 12 month period:
```12MR Months:=CALCULATE (
COUNTA ( tbl_date_matrix[Date] ),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
(
tbl_output_colleagues[Month ID]
>= MAX ( tbl_date_matrix[Month ID] ) - 11
)
&& ( tbl_output_colleagues[Month ID] <= MAX ( tbl_date_matrix[Month ID] ) )
),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
)
)```

• Divided one by the other:
`12MR Average Headcount:=DIVIDE ( [12MR Headcount], [12MR Months] )`

There are probably quicker/easier ways to do this (as suggested) however on testing this combination yields the correct results.

Thanks again,

Andy

2 REPLIES 2
Highlighted Memorable Member

## Re: DAX | Average of Count IF > 0

so... just divide by 12 is close, except you might have a month with no headcount, and want to divide by 11 in that case?

Might be a rare use for COUNTX here?

Months = COUNTX(

FILTER(VALUES(tbl_date_matrix[Month ID]), tbl_date_matrix[Month ID]<=MAX(tbl_date_matrix[Month ID]) &&  tbl_date_matrix[Month ID] >=MAX(tbl_date_matrix[Month ID])-11),

)

or maybe something that feels more naturual
Months = CALCULATE(DISTINCTCOUNT(tbl_date_matrix[MonthId]),
FILTER(VALUES(tbl_date_matrix[Month Id]),
tbl_date_matrix[Month ID]<=MAX(tbl_date_matrix[Month ID]) &&
tbl_date_matrix[Month ID]
>=MAX(tbl_date_matrix[Month ID])-11 &&
CALCULATE ( COUNTROWS(tbl_data_colleagues), tbl_data_colleagues[Status]) = "Active") > 0

)
)

Highlighted Helper I

## Re: DAX | Average of Count IF > 0

Hi,

I actually managd to adapt a solution from the tutorial I found here. In the end I;

• Worked out the total headcount over a 12 month period:

```12MR Headcount:=CALCULATE (
COUNTA ( tbl_output_colleagues[Status] ),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
(
tbl_output_colleagues[Month ID]
>= MAX ( tbl_date_matrix[Month ID] ) - 11
)
&& ( tbl_output_colleagues[Month ID] <= MAX ( tbl_date_matrix[Month ID] ) )
),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
tbl_output_colleagues[Status] = "Active"
)
)```

• Worked out the number of months in that 12 month period:
```12MR Months:=CALCULATE (
COUNTA ( tbl_date_matrix[Date] ),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
(
tbl_output_colleagues[Month ID]
>= MAX ( tbl_date_matrix[Month ID] ) - 11
)
&& ( tbl_output_colleagues[Month ID] <= MAX ( tbl_date_matrix[Month ID] ) )
),
FILTER (
ALLEXCEPT (
tbl_output_colleagues,
tbl_output_colleagues[Functional Area],
tbl_output_colleagues[Tenure Type],
),
)
)```

• Divided one by the other:
`12MR Average Headcount:=DIVIDE ( [12MR Headcount], [12MR Months] )`

There are probably quicker/easier ways to do this (as suggested) however on testing this combination yields the correct results.

Thanks again,

Andy  