DAX | Average of Count IF > 0

[AndrewKent]

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

How do I adapt this to claculate Average instead?

Andy

[Anonymous]

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),
   [HeadCount]

)

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

        )
     )
      

[AndrewKent · Accepted solution]

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[WL5 Head Of],
            tbl_output_colleagues[Functional Area],
            tbl_output_colleagues[Business Area],
            tbl_output_colleagues[Tenure Type],
            tbl_output_colleagues[Business 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[WL5 Head Of],
            tbl_output_colleagues[Functional Area],
            tbl_output_colleagues[Business Area],
            tbl_output_colleagues[Tenure Type],
            tbl_output_colleagues[Business 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[WL5 Head Of],
            tbl_output_colleagues[Functional Area],
            tbl_output_colleagues[Business Area],
            tbl_output_colleagues[Tenure Type],
            tbl_output_colleagues[Business 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[WL5 Head Of],
            tbl_output_colleagues[Functional Area],
            tbl_output_colleagues[Business Area],
            tbl_output_colleagues[Tenure Type],
            tbl_output_colleagues[Business Type]
        ),
        [12MR Headcount] > 0
    )
)

• 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