Incorrect Percentage - DAX

[ronaldbalza2023]

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 

Thanks in advance for your help!

[twofingertyper]

Sorry, had just read the chapter in the guide to DAX and it sounded similar to an example they provided. The filter you provided within the CALCULATE function would still be applied, it just removes any additional filters that may be impacting on the measure. 

Apologies if you knew all this already, I am slowly learning things and find it a lot easier currently to work with examples/data rather than just the code, but hopefully someone will be along that knows more than me and can assist. 

(sorry, just noticed you uploaded the example, will take a look!)

Good luck with it! 

[twofingertyper]

If you removed the filters as part of the calculation; so adding in - I think - ALL (_EmployeeDetail) after the COUNTROWS, does that help? 

[ronaldbalza2023]

Hi @twofingertyper, thanks for taking the time on this, appreciate it. That doesn't help as the main measure was to filter all the employees in the higher salary range.

[twofingertyper]

Do you have the figures you want? As I can get these:

By using the ALL on both tables.

[ronaldbalza2023]

Hi @twofingertyper , on point. How did you achieved it? Thanks 🙂

[twofingertyper · Accepted solution]

@ronaldbalza2023 - as mentioned, just adding in ALL. so your measure became:

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