cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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"))

 

How do I adapt this to claculate Average instead?

 

Andy

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
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[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

View solution in original post

2 REPLIES 2
Highlighted
Memorable Member
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),
   [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

        )
     )
      

Highlighted
Helper I
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[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

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors