PowerBI getting results by grouping in the related table

Question

I have 3 tables:

1. contains team code, position name and number of people
2. contains team code and team name
3. contains list of people with their names, team code, position name and various other information like marital situation, etc

I have connected table 1 with table 2 and table 3 with table 1 by team code (one to many). Now I need to create a table, which would contain team code, position name and 3 calculated columns ('Single', 'Married', 'Divorced') with an amount of people, which have such words in their 'marital situation' column.

Tried some DAX code using sumx, but it looks that in gives only a number of people in that team.

How can I achieve such a grouping and calculation?

Answer 1

I added each of the following calculated columns to the Teams Table.

Single = 

VAR thisTeam = Teams[Team Code]

RETURN CALCULATE(COUNTROWS(ALLSELECTED(People))
    , People[Marital Situation] = "Single"
    , People[Team Code] = thisTeam
) + 0

Married = 

VAR thisTeam = Teams[Team Code]

RETURN CALCULATE(COUNTROWS(ALLSELECTED(People))
    , People[Marital Situation] = "Married"
    , People[Team Code] = thisTeam
) + 0

Divorced = 

VAR thisTeam = Teams[Team Code]

RETURN CALCULATE(COUNTROWS(ALLSELECTED(People))
    , People[Marital Situation] = "Divorced"
    , People[Team Code] = thisTeam
) + 0

Output:

---

Mock data used:

Table 1: Team Positions

Team Code	Position Name	Number of People
T001	Developer	5
T002	Designer	3
T003	Tester	4
T004	Manager	2

Table 2: Teams

Team Code	Team Name
T001	Alpha Team
T002	Beta Team
T003	Gamma Team
T004	Delta Team

Table 3: People

Person Name	Team Code	Position Name	Marital Situation
Alice	T001	Developer	Single
Bob	T001	Developer	Married
Charlie	T002	Designer	Divorced
Dave	T003	Tester	Married
Eva	T003	Tester	Single
Frank	T004	Manager	Divorced
Grace	T002	Designer	Single
Helen	T004	Manager	Married