remove double counting of projects


I have a query on how i can remove double counting of Projects.

Case artifacts:
T1 Teams, which is a hierarchical list, so for example Team A.1 & Team A.2 rolls up to Team A, like wise Team B.1 rolls up to Team B; all rolling up to total teams.
M1 Members: Flat member list.
P1 Projects: a Numbered list of projects.
C1 Business Type: A flat list of different types of businesses involved. Example Manufacturing, Sales, Service.

Each member is assigned with a team and a project. However members from different teams can be assigned to same project. Example, members from Team A.1 and Team B.1 can be working on the same project. Teams can work on multiple projects as well.

Now if i need a pie chart based on C1 business type; where when i select the team, like Team A.1: - i need to get the number of projects in each business type.

I have managed to get the numbers using a sum logic.

Using a simple count line item in a P1 Project module. (CON1 Count )

And in a module dimensioned by T1 & C1; using SUM function, formula goes CON1.Count[SUM: C1 Business Type]

Issue here is the project count doubles when different teams work on the same project, is there a way to overcome this ?



  • Hi @Sachinsourav02 ,

    Firstly, if you look at all teams level, you might still find double counting of projects, because the requirement here is to draw a pie chart for each team (like A1) and business type. So if members assigned to teams A1 and B1 are working on same project, then you should find that project counted for respective business type, when you select either A1 or B1.
    Also, I took an assumtion that business type is an attribute of project. In my proposal below, I took liberty to add properties to project list, but I recommend transitioning them to a SYS module.

    Suppose I have 4 projects in #P1 list, which are tagged to different business types:

    And my member assignment input module goes as below:

    I created a staging module (by teams and projects) to first identify number of assignments between teams and projects:

    My final module can give the desired output, of number of projects by business type for each team member:

    I tried few combinations to see this working. Please let me know if it doesn't meet your need (appreciate if you can share any scenario where it fails)



  • TristanS
    edited June 2023

  • Hi @AjayM

    Thanks for your suggestion. Just one correction Members can be assigned to multiple teams as well. So at a project level i'm allowed to tag multiple members from different teams, thats a problem in your proposed solution.