Calculation for many to many relationship data

Question & Answer
,
1

image

I want to calculation distinct count of employees for departments A only, B only, C only, AB, AC, BC and ABC
Help me with suitable calculation.
Thanks

2

Hi @Koteswar

To calculate the distinct count of employees for each combination of departments (A, B, C, AB, AC, BC, and ABC) , you can create a calculated field to identify the department combinations each employee is associated with.

Example: (Syntax may vary - replace the fields from your dataset)

A Count - distinct_countIf({Employee ID}, Department = 'A' AND NOT contains(Department, 'B') AND NOT contains(Department, 'C'))

B Count - distinct_countIf({Employee ID}, Department = 'B' AND NOT contains(Department, 'A') AND NOT contains(Department, 'C'))

C Count - distinct_countIf({Employee ID}, Department = 'C' AND NOT contains(Department, 'A') AND NOT contains(Department, 'B'))

AB Count - distinct_countIf({Employee ID}, contains(Department, 'A') OR contains(Department, 'B') AND NOT contains(Department, 'C'))

AC Count - distinct_countIf({Employee ID}, contains(Department, 'A') OR contains(Department, 'C') AND NOT contains(Department, 'B'))

BC Count - distinct_countIf({Employee ID}, contains(Department, 'B') OR contains(Department, 'C') AND NOT contains(Department, 'A'))

ABC Count - distinct_countIf({Employee ID}, contains(Department, 'A') OR contains(Department, 'B') OR contains(Department, 'C'))
3

Hi @Koteswar,
It’s been awhile since we last heard from you, did you have any additional questions regarding your post?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

4

Hi @Koteswar,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!