Calculate if employee matches several requirements

[Nipius]

Hi all!

I've been cracking my head over this for the past two days, without any luck. Hope you could help me out 🙂

Incumbent Employee ID	Successor Employee ID	Successor Gender	Successor Readiness	Balanced (ignoring readiness)	Balanced (only looking at Ready Now)
001	901	Female	Ready Now	No	Yes
001	902	Female	Ready Now	No	Yes
001	903	Male	Ready Now	No	Yes
001	904	Male	Ready Later	No	Yes
001	905	Male	Ready Later	No	Yes
002	701	Female	Ready Now	Yes	Yes
002	702	Male	Ready Now	Yes	Yes
002	703	Female	Ready Later	Yes	Yes

The idea is that I have a file formatted like in the above table. It has incumbent employee ID's and then shows the gender and readiness of the possible successors. What I'd like to accomplish is to add two columns:

1. Showing the gender distribution as balanced (50% or above) or not, regardless of the readiness of the successors
2. Showing the gender distribution as balanced (50% or above) or not , only including the ready now successors.

Hope one of you could help me out. Any help is appreciated 🙂

[v-lid-msft · Accepted solution]

 Hi @Nipius ,

We can create two calculated columns to meet your requirement:

Balanced (ignoring readiness) = 
VAR t =
    FILTER (
        'Table',
        [Incumbent Employee ID] = EARLIER ( [Incumbent Employee ID] )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t, [Successor Gender] = "Female" ) )
            / COUNTROWS ( t ) >= 0.5,
        TRUE (),
        FALSE ()
    )

Balanced (only looking at Ready Now) = 
VAR t =
    FILTER (
        'Table',
        [Incumbent Employee ID] = EARLIER ( [Incumbent Employee ID] )
            && [Successor Readiness] = "Ready Now"
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t, [Successor Gender] = "Female" ) )
            / COUNTROWS ( t ) >= 0.5,
        TRUE (),
        FALSE ()
    )

Best regards,

[Nipius]

Thanks a ton, @v-lid-msft! Exactly what I was looking for. I'm happy to see I did use the right logic, but I wasn't able to translate this into the right code yet. Really appreciated 🙂

[v-lid-msft]

Hi @Nipius ,

Have you successfully translated the code in my previous post into the right code you want?

Best regards,