cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Many to many relationship multiplying

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?

4 REPLIES 4
Community Support

If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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
Community Support

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