Hello, community!
So I have a formula that should compute the total hours for work efforts in an organisation. These efforts are booked in certain accounts, and in these accounts there are certain projects where people book. If a person is assigned to multiple teams, his/her work efforts should be distributed among teams according to % allocation. So on one side we have a total of efforts (in hours) that gets distributed to assigned teams and on the other side, we have just a total of efforts made on a specific day. (imagine a matrix: column day; values: TotalDistrubuted and Total efforts.) I came up with the following formula :
VAR _SUMINTHrs =
CALCULATE (
SUM ( 'FACT_Table1' [TimeHours] ),
SUMMARIZE (
'FACT_Table1',
'FACT_Table1' [ID_acct_project_user_mth] ) )
VAR _CountRows =
COUNTROWS (
DISTINCT ( 'FACT_Table1'[ID_user_team_mth] ) )
VAR _Result =
DIVIDE (
_SUMINTHrs,
_CountRows )
RETURN _Result
The initial problem was if a person is assgined to many teams, his/her total effort per day is duplicated since there are several rows to calculate the team efforts allocation. For eg. a person is assigned to 5 teams, and the total of booked efforts is 7 hours (for one day). This time will be allocated to 5 teams accordingly and person's total booking for this day will be 35 hours. The above formula seemed to solve the problem when I filtred users one by one in a matrix. The problem arises when I filter one team and pick a month. What formula does here is basically taking the total amount of all people in a team and divides it by a unique number of team members. For example: 20 members booked 1 hour per person (lets say today), totaling to 20 hours (per team) and then the formula divides this number by these 20 unique members, which gives me 1 hour for the whole team. Of course, when I just filter by month, the problem is the same.
Does anybody have any suggestions as to how solve this problem? 🙂 Sorry if the discription is vague or lacking info. Due to data compliance, I can't go into much detail..
