% Ethnic Minority per team

[SigmundVoid]

Hello, I'm new to Power BI and am struggling to calculate the % ethnic minority employees per team.

I have 2 tables, one contains a list of all employees, their employee ID and their ethnicity (1 row for each employee) and the other contains a list of jobs, teams and employee ID. These tables are connected by employee ID with a one to many relationship (one employee can have multiple jobs and sit in multiple teams at once). I want to work out the % ethnic minority employees per team. I've created a measure that gives me the overall % of ethnic minority employees, but when I create a visual that includes this measure and the names of the teams, I'm just getting the same figure for every team (the overall % for all teams combined)

Here's the data model.

The employees' teams are in the 'reporting unit' column in the position_structure table. Any help would be appreciated!

[v-rzhou-msft · Accepted solution]

Hi @SigmundVoid ,

Here I create a sample to have a test.

Employees_PK:

Position_Structure:

Data model:

Measure:

Percentage =
VAR _SUMMARIZE =
    SUMMARIZE (
        Position_Structure,
        Position_Structure[Team],
        Position_Structure[Employee ID],
        "Ethnicity",
            CALCULATE (
                MAX ( Employees_PK[Ethnicity] ),
                FILTER ( Employees_PK, Employees_PK[Employee ID] = EARLIER ( [Employee ID] ) )
            )
    )
VAR _CountEthnicity =
    COUNTAX ( FILTER ( _SUMMARIZE, [Ethnicity] <> "E1" ), [Employee ID] )
VAR _CountEmployee =
    COUNTAX ( _SUMMARIZE, [Employee ID] )
RETURN
    DIVIDE ( _CountEthnicity, _CountEmployee )

Result is as below.

Best Regards,
Rico Zhou

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

[rargyle]

I'm fairly new to dax - I've acheived similar by the following steps:

1. create a measure EmployeeCount = CALCULATE(DISTINCTCOUNTNOBLANK(EmployeeField))

2. create a measure to count subset EmployeesInTeam = 

= calculate([EmployeeCount],ALL(Position_Structure[Reporting Unit]))
3. create measure 

% Employee per Team = divide([EmployeeCount],[EmployeesInTeam])

if anyone has a better way, I'm really keen to learn 🙂

thanks

Rory