Hello, thank you in advance for your assistance.
UPDATE: added sample file below illustrating this issue, though it’s a simplified version of what I describe below.
I would like to have a universal “Pct of all FTEs” measure that would allow me to compare percentages of a selected subset of employees against percentages of all employees on the same table or in the same graphic. Further, I would like this to be sliceable by any attribute from the “Employee Reference” table.
Here’s what I have working so far:
“Generation” is a column from the “Employee Reference” table, which contains all employee information.
“Employees” measure uses CALCULATE to modify an existing measure so that only active (“A”) employees are considered, as follows:
Employees = CALCULATE([Total Employees Ref], ‘Employee Reference’[Active Status] = “A”)
“All Emps (cur context)” modifies the “Employees” measure to remove filters originating outside of the current measure:
All Emps (cur context) =
Then I just divide “Employees” by "All Emps (cur context) to get the “Pct Emps”.
The issue comes when I try to do the same thing for all employees.
I can get the denominator, which will be “All Cur FTEs”, by stripping off all filters and then re-applying just the ones I need for this report page (probably a more elegant way to do this, but it works):
All Cur FTEs =
‘Employee Reference’[Active Status] = “A” &&
‘Employee Reference’[Group] <> “9999” &&
‘Employee Reference’[Group] <> “5555” &&
‘Employee Reference’[Status] = “F”)
But I can’t figure out how to make this measure respect the “Generation” attribute and any other attribute that I’d slice by in a table or graphic. I know from a different report that the breakdown is as follows:
It feels like KEEPFILTERS could be used here, but I just can’t get anything working. Again, I would like to show the number and percent of “All Cur FTEs” that fall within each of the demographic attributes I have, in this case “Generation”.
Thanks for any insight.