How do I use aggregation as filter in this dax measure?

Question

I have a table in PowerPivot with 4 columns: YearMonth, Location, PersonID, EndDate. I want to calculate turnover rate (employees that left/tot number of employees) and I have found the correct calculation, but I want to exclude all the locations that have less than say 100 Employees. I want this to be defined in the measure so I can use slicers both on Location and YearMonth.

Here are my measures so far:

TotEmployees:=DISTINCTCOUNT([PersonID])

Leaving:=(CALCULATE(SUMMARIZE(TABLE1;[PersonID];[EndDate]));Table1[EndDate]]>0)

Turnover:=Table1[Leaving]/Table1[TotEmployees]

What do I need to add to my measures to filter the table to exclude all locations with less than 100 employees?

Thanks in advance!

Dan Dan · Accepted Answer · 2019-02-18T11:51:11

I would change the [Turnover] measure into:

CALCULATE(
    DIVIDE([Leaving], [TotEmployees]),
    FILTER(VALUES(Table1[Location]), [TotEmployees] >= 100)
)

As a side note, please don't put the table name in front of a measure name, when referencing measures, and always put the table name in front of the column name, when referencing columns.

How do I use aggregation as filter in this dax measure?

1 Answers