Forum Discussion

ronaldbalza2023's avatar
ronaldbalza2023
Continued Contributor
2 years ago
Solved

Incorrect Percentage - DAX

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!

 
Need Help
Show and Tell
  • twofingertyper's avatar
    twofingertyper
    2 years ago

    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)

     

6 Replies

  • twofingertyper's avatar
    twofingertyper
    Helper III
    2 years ago

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

    • ronaldbalza2023's avatar
      ronaldbalza2023
      Continued Contributor
      2 years ago

      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's avatar
        twofingertyper
        Helper III
        2 years ago

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

        By using the ALL on both tables.

  • twofingertyper's avatar
    twofingertyper
    Helper III
    2 years ago

    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!