Many to many relationship multiplying

[Adlu]

Hi Guys,

I'm building a timesheet summary. In the "Projects" column, I've got a category named "ALL" which means it was time for all projects. The problem is, that manager want to split that category into existing projects.

e.g. Team member John charged:

- 10 hours on project X

- 10 hours on project Y

- 10 hours on project Z

- 10 hours on project ALL - manager wants that 10 "all" hours, to be split into X, Y, and Z on a constant ratio - 0,2 for X; 0,4 for Y; 0,4 for Z

Final outcome should be like

- 10 hours on project X + 0,2 * 10 = 12

- 10 hours on project Y + 0,4 * 10 = 14

- 10 hours on project Z + 0,4 * 10 = 14

Please note that different team members will have different ratios.

As of now, I prepared such mapping for ratios at the top, connected with data inputs at the bottom. Connected through many2many relationship.

However, i've got mind fog and formulas (sumx, calculate) doesn't work. Would you please give me advice on how to handle that correctly?

[v-janeyg-msft]

@Adlu  

Any updates?
If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

[parry2k]

@Adlu it will be easier if you share pbix or sample data in an excel sheet with the expected output, it is hard to work with the images.

[Adlu]

hi @parry2k I uploaded mentioned data tables below.

Team member	Project	Ratio	Project_ADJUSTED	FUND	TeamMember&Project
John Smith	All	0,2	X	Fund I	John SmithAll
John Smith	All	0,4	Y	Fund II	John SmithAll
John Smith	All	0,4	Z	Fund III	John SmithAll
John Smith	X	1	X	Fund I	John SmithX
John Smith	Y	1	Y	Fund II	John SmithY
John Smith	Z	1	Z	Fund III	John SmithZ
Team Member	Date	Project	Recurring/Non recurring	Hours		TeamMember&Project
John Smith	2021 September 27.09. - 03.10.	X	Recurrent	12		John SmithX
John Smith	2021 September 27.09. - 03.10.	Y	Non-recurrent	4		John SmithY
John Smith	2021 September 27.09. - 03.10.	Z	Recurrent	4		John SmithZ
John Smith	2021 September 27.09. - 03.10.	All	Recurrent	6		John SmithAll
John Smith	2021 September 27.09. - 03.10.	X	Non-recurrent	5		John SmithX
John Smith	2021 September 27.09. - 03.10.	Y	Recurrent	5		John SmithY
John Smith	2021 September 27.09. - 03.10.	Z	Recurrent	4		John SmithZ
John Smith	2021 September 20.09. - 26.09.	All	Recurrent	40		John SmithAll
John Smith	2021 September 13.09. - 19.09.	X	Recurrent	10		John SmithX
John Smith	2021 September 13.09. - 19.09.	Y	Non-recurrent	5		John SmithY
John Smith	2021 September 13.09. - 19.09.	Z	Non-recurrent	16		John SmithZ
John Smith	2021 September 06.09. - 12.09.	All	Non-recurrent	10		John SmithAll
John Smith	2021 September 06.09. - 12.09.	X	Recurrent	15		John SmithX
John Smith	2021 August 30.08. - 05.09.	Y	Non-recurrent	8		John SmithY
John Smith	2021 August 30.08. - 05.09.	Z	Recurrent	7		John SmithZ
John Smith	2021 August 30.08. - 05.09.	All	Recurrent	15		John SmithAll

[v-janeyg-msft]

Hi, @Adlu 

Which two fields are related in these two tables? Project and Project?
Please tell me how to distinguish all as x, y, z?  What should the final output look like in visual? Can you display it?

Best Regards,

Community Support Team _ Janey