Category effect on total [ay gap (UABLE TO REPLY)

[HufferD]

@djpicton,

I was unable to reply to your post Category effect on total pay gap so I’ll pick it up here.

@djpicton

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.

The problem is related to the filter context, particularly how it interacts with your GAP Effect measure.

In DAX, the filter context is often determined by the dimensions used in your visualizations, and different measures may react differently to these contexts. In your original GAP Effect measure, the use of functions like ALL and ALLSELECTED doesn’t isolate the effect of each department on the overall gender pay gap, which is why you weren’t getting the results you wanted.

Use an iterator to calculate the gap for each department.

Below is a revised version of your GAP Effect measure:

GAP Effect Iterative = 
VAR CurrentDept = SELECTEDVALUE(Employees[Changed Type.DeptKey])
VAR AllDepts = VALUES(Employees[Changed Type.DeptKey])

VAR OverallGap = 
    CALCULATE(
        [Gap],
        ALL(Employees)
    )

VAR DeptEffect = 
    SUMX(
        AllDepts,
        IF(
            Employees[Changed Type.DeptKey] = CurrentDept,
            OverallGap - CALCULATE([Gap], ALL(Employees), Employees[Changed Type.DeptKey] <> CurrentDept),
            BLANK()
        )
    )

RETURN
    DeptEffect

It’s designed to work at the department level, so it won’t return a value for the overall total in a table visual. If you wanted one, just modify it to include a condition that checks if it’s being evaluated at the total level.

[djpicton]

That worked perfectly, thank you so much

[AlexisOlson]

Your original measure was pretty close.

You can make it work by swapping in Department where you had Employees:

GAP Effect = 
VAR totalGap =
    CALCULATE ( [Gap], ALLSELECTED ( Department ) )
VAR gapWithout =
    CALCULATE ( [Gap], EXCEPT ( ALLSELECTED ( Department ), VALUES ( Department ) ) )
RETURN
    totalgap - gapWithout

This is because ALLSELECTED was clearing the filter context on the Employees fact table in your measure but the Department filter context was unchanged since filtering doesn’t automatically propagate across relationship “upstream”, only “downstream”.

Note: This modification makes the Employee Table version not work, but that’s probably not the setup you want.