Incorrect Percentage - DAX

DAX DAX Calculations
1

Hi everyone,

I’m hoping to get some assistance with a DAX measure I created to calculate the percentage of employees in the higher salary range. The overall percentage seems accurate, but when I add the department field (_EmployeeDetail[Department] ) to my visualization, the percentage gets divided incorrectly across departments.

Here’s the current DAX measure:

% SalaryRangeHigh = 
VAR TotalActiveEmployees =
  CALCULATE(
    COUNTROWS(_EmployeeDetail),
    _EmployeeDetail[EmploymentStatus] = "Active",
    NOT ISBLANK(_EmployeeSalary[ Salary ])
  )
VAR ActiveEmployeesHigherRange =
  CALCULATE(
    COUNTROWS(_EmployeeDetail),
    _EmployeeDetail[EmploymentStatus] = "Active",
    NOT ISBLANK(_EmployeeSalary[ Salary ]),
    FILTER(
      _EmployeeSalary,
      _EmployeeSalary[ Salary ] >= _EmployeeSalary[SalaryRangeHigh]
    )
  )
RETURN 
  DIVIDE(ActiveEmployeesHigherRange, TotalActiveEmployees, 0)

Here’s the sample pbix file.
SampleData.pbix (72.5 KB)

Thanks in advance for your help!

2

Check out this solution from data mentor.

I think it should help on this

3

Hi @SamMcKay

In the reference article reference , it states " ALLEXCEPT` keeps all contexts in the data except for the ones specified "
I think that , wrt calculate , ALLEXCEPT is perhaps acting like a calculate modifier to remove the filters except the one specified.

This is evident from the visual , when I added extra filters (of-course it not what the original qn ask , but to clarify ,when I add extra filters , its ignoring them . . so likely the statement " ALLEXCEPT` keeps all contexts in the data except for the ones specified " As specified in the referenced article may not be correct.

Kindly correct me if misunderstanding.
Thanks for same.

image
image1392×536 69.9 KB

1 Like
4

The documentation is clear:

When used as a modifier in [CALCULATE] or [CALCULATETABLE], ALLEXCEPT removes the filters from the expanded table specified in the first argument, keeping only the filters in the columns specified in the following arguments.

The data mentor thread is totally wrong, a fine example of how careful one must be with gen AI results.

1 Like