Calculate percentages of subset and total, respecting filters

DAX DAX Calculations
1

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:
image

“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) =
CALCULATE([Employees],
ALLSELECTED(‘Employee Reference’)
)

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 =
COUNTROWS(
FILTER(ALL(‘Employee Reference’),
‘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:
image

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.

2

@jscott15,

I definitely get the gist of what you are trying to do here, but to work through the specific issues it would be extremely helpful if you could please post the PBIX file, with any sensitive information masked.

Thanks.

  • Brian
4

Employee percentages sample.pbix (183.8 KB)

@BrianJ,
I’ve attached a sample version of my stated issue. It’s a greatly slimmed down table, but I hope it illustrates what I’m trying to do, and if we solve it here I should be able to translate it to my full model.

Thanks again everyone!

5

@jscott15,

Interesting problem. I played around with it a bit, and ended up with the following two new measures:

Employees UBMS = //UMBS: Unaffected by Marital Slicer
CALCULATE(
    [Employees],
    ALL(data[Marital Status])
)

Pct Emp UMBS = // UMBS: Unaffected by Marital Slicer
    DIVIDE ( [Employees UBMS], [All Cur FTEs], 0 )

Here are the results:

image
image.png904×284 12.1 KB

And with Gender as the evaluation context instead of Generation:

image

Full solution file posted below. I hope this is helpful.

  • Brian

eDNA Forum - Employee percentages sample solution.pbix (195.9 KB)

1 Like
6

Sorry for the delay getting back on here, I appreciate your help on this!