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.
