Formula for Gender % that returns 0%/100% when sliced by respective gender

[stbrooks13]

I have a formula in my dashboard that shows the % of hires by gender that is working properly until the dashboard is sliced by the respective gender. Please see formulas and results below. I need the results to show 0% and 100% respectively when the dashboard is sliced by gender.

Female = CALCULATE(COUNTA(Activity[Offer Accepted]),Activity[Offer Accepted]>0,Activity[Gender]="Female")/CALCULATE(COUNTA(Activity[Offer Accepted]),Activity[Offer Accepted]>0)
Male = CALCULATE(COUNTA(Activity[Offer Accepted]),Activity[Offer Accepted]>0,Activity[Gender]="Male")/CALCULATE(COUNTA(Activity[Offer Accepted]),Activity[Offer Accepted]>0)

Overall Results (accurate)
Female 51%, Male 48%, Not Specified 1%

Results when sliced by Female
Female 100% (accurate), Male 95% (inaccurate - should be 0%), Not Specified 1% (inaccurate - should be 0%)

Results when sliced by Male
Female 105% (inaccurate - should be 0%), Male 100% (accurate), Not Specified 1% (inaccurate - should be 0%)

 

[Ozeroth]

Hi stbrooks13

Good question!
The reason your Male/Female measures are not behaving as expected is that CALCULATE's filter arguments overwrite existing filters.
So if you are filtered on Gender=Male, then the Female measure returns (Female Count / Male Count) rather than ( 0 / Male Count)

To get the behaviour you want, you can add VALUES ( Activity[Gender] ) as a filter argument to CALCULATE, which will then be intersected with the other filter argument (either Gender=Female or Gender=Male).

Female =
CALCULATE (
    COUNTA ( Activity[Offer Accepted] ),
    Activity[Offer Accepted] > 0,
    Activity[Gender] = "Female",
    [COLOR=#ff0000][B]VALUES ( Acivity[Gender] )[/B][/COLOR]
)
    / CALCULATE ( COUNTA ( Activity[Offer Accepted] ), Activity[Offer Accepted] > 0 )

Male =
CALCULATE (
    COUNTA ( Activity[Offer Accepted] ),
    Activity[Offer Accepted] > 0,
    Activity[Gender] = "Male",
[COLOR=#ff0000][B]    VALUES ( Activity[Gender] )[/B][/COLOR]
)
    / CALCULATE ( COUNTA ( Activity[Offer Accepted] ), Activity[Offer Accepted] > 0 )

See this article for some discussion of this very issue:
SQLBI - Marco Russo : How CALCULATE works in DAX

Regards
Owen

 

[stbrooks13]

This works perfectly, thank you!