Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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)
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!
If you removed the filters as part of the calculation; so adding in - I think - ALL (_EmployeeDetail) after the COUNTROWS, does that help?
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.
Do you have the figures you want? As I can get these:
By using the ALL on both tables.
@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)
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
112 | |
96 | |
94 | |
64 | |
58 |