Hi all,
I am working on a report looking at gender pay gaps and calculating this at a total company level and department level. I am trying to write a measure that with calculate the effect a department has on the overall company pay gap i.e. the difference between the total company pay gap and the pay gap if the current department was excluded.
I am getting the correct results using the following measure, but only when the department field comes from the fact table.
Gap =
VAR _avgMale =
CALCULATE ( AVERAGE ( Employees[Salary] ), Employees[gender] = "m" )
VAR _avgFemale =
CALCULATE ( AVERAGE ( Employees[Salary] ), Employees[gender] = "f" )
RETURN
DIVIDE ( ( _avgMale - _avgFemale ), _avgMale )
GAP Effect =
VAR totalGap =
CALCULATE ( [Gap], ALLSELECTED ( Employees ) )
VAR gapWithout =
CALCULATE ( [Gap], EXCEPT ( ALLSELECTED ( Employees ), VALUES ( Employees ) ) )
RETURN
totalGap - gapWithout
In reality the department name will sit in a dimension table, but when I set up a visual this way, the measure returns the total company gap for each department.
I feel that the answer is just staring me in the face, but things are never simple with ALLSELECTED.
Any ideas would be hugely appreciated. Example file uploaded
All selected Example.pbix (34.2 KB)
Many thanks,
David
