What If Parameters for Gender Balance in Company?

[ErMley]

Hi all, 

Looking for some support in ideating a solution to this problem. I have demographic data to work with which shows the following fields:

1. Employee ID

2. Gender (options: Male, Female, Other [Non-Binary and Self-Identifying])

3. Hire Date

4. Termination Date

I would like to use a what-if parameter similar to this video: https://www.youtube.com/watch?v=QXNRseW0R7E which would allow a dashboard user to view the following DAX measures and parameters:

1. Current Number of Females and % of Population (I have these DAX Measures already)

2. A what-if parameter for 'Target Population' of women as a percentage (e.g. 30% of population to be women)

3. A what-if parameter for 'Target Year' which would allow the user to select the year at which the target population would be achieved

4. A line chart showing the growth/recruitment needed for women to meet that target 

Ideally, I would also like to have a what-if parameter for the male and 'other' populations to take into account population growth rates in other areas which may impact the total population proportion of women but I cannot seem to get this to work esp. in a line chart format. Does anyone have any ideas on approaches to resolve this? 

[Anonymous · Accepted solution]

Hi @ErMley 

Since I don't know what your data looks like, I tested it using simple data and here is my solution, hope it helps.
If this doesn't work for you, please share some sample data and explain it so we can understand your problem better.

My test data like this:

Create a calendar table with the following DAX:

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),
    "Year", YEAR([Date])
)

Select New Parameter in the Modeling tab:

Create the following measures:

TotalPopulation = 
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Hire Date] <= MAX('DateTable'[Date]) &&
        (ISBLANK('Table'[Termination Date]) || 'Table'[Termination Date] > MAX('DateTable'[Date]))
    )
)

FemalePopulation = 
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Gender] = "Female" &&
        'Table'[Hire Date] <= MAX('DateTable'[Date]) &&
        (ISBLANK('Table'[Termination Date]) || 'Table'[Termination Date] > MAX('DateTable'[Date]))
    )
)

FemaleTargetPopulation = [TotalPopulation] * 'Female Target Percentage'[Female Target Percentage Value]  / 100

FemaleNeedIncreased = [FemaleTargetPopulation] - [FemalePopulation]

Repeat the above to create parameters and measures for Male and Other.

Create a Line Chart with the Calendar table “Year” field as the X-axis and the measures of the number of people to be increased as the Y-axis:

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...