# Category effect on total pay gap

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

Hello
I changed the Gap Effect measure and get the same result for the two matrix tables
hop e this helps

kind regards
roger