Filter Data Based On Matrix Values

[wesleygeddes]

I have a dataset that looks like the following: 

Projected Utilization: 

EmployeeID	WorkStatus	Date	FTE%
55	Firm	4/1/22	.50
55	Table 1	4/1/22	.50
55	Table 2	4/1/22	.25
55	Table 3	4/1/22	.1
55	Firm	5/1/22	.75
55	Table 1	5/1/22	.10
55	Table 3	5/1/22	.1

Assigned Utilization

EmployeeID	Assigned FTE
55	1
60	.75

You can filter based on WorkStatus (whether that work is available like Firm, or Potentially available like Table 1/2/3). I also am using a calculated measure to determine how far over/under an employee is allocated based on their projected FTE vs their Assigned FTE. 

So, if I wanted to look at WorkStatus Firm, and Table 1, my Matrix table would look like the following:  

EmployeeID	4/1/22	5/1/22
55	0.00	-0.15

If I wanted to include Firm, Table 1/2/3 I would see: 

EmployeeID	4/1/22	5/1/22
55	0.35	-0.05

I need to be able to filter employees who are over/under allocated. 

In this example, Employee 55 would show up when we select "Under Allocated" (in both instances of our Matrix). Employee 55 would only show up in the second example when I selected "Over Allocated." 

I can create a calculated measure that determines whether an employee, on a specific month, is over/under allocated. But I can't figure out how I would filter an employee using a Slicer. 

It's my understanding that we can't use a filter in a slicer, except I did find a Youtube video that walks me through a work-around, but it didn't solve my problem. The over/under allocated value was getting aggregated at the employee level, not employee/date level. So if the SUM of your projected FTE over the two months was greater than your assigned FTE then you would be classified as "Over." Same thing for "Under." 

Any thoughts/help would be greatly appreciated!

Update: I made a mistake on the "Assigned Utilization" table. There used to be a 55 - Table B row which has since been removed. 

[Anonymous]

Hi @wesleygeddes ,

Why there would be a value like "table3" in the Assigned Utilization [Assigned FTE]?

Are you want something like this?

Measure = IF(sum('Projected Utilization'[FTE%])<SUM('Assigned Utilization'[Assigned FTE]),"Under","Over")

Best Regards,

Jay

[wesleygeddes]

I have a measure that is almost identical to this one and it does work when I place that measure inside of the matrix. But if I wanted to filter the matrix to only show me people who are "Under" or "Over" (meaning in one of the months being displayed, they are "under") then I am unable too.