cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adlu
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.

Adlu_2-1633101297934.png

 

 

Adlu_1-1633099593238.png

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
v-janeyg-msft
Community Support
Community Support

@Adlu  

 

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

 

Best Regards,

Community Support Team _ Janey

parry2k
Super User
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.

 

 






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.





Adlu
Frequent Visitor

hi @parry2k I uploaded mentioned data tables below.

 

Team memberProjectRatioProject_ADJUSTEDFUNDTeamMember&Project
John SmithAll0,2XFund IJohn SmithAll
John SmithAll0,4YFund IIJohn SmithAll
John SmithAll0,4ZFund IIIJohn SmithAll
John SmithX1XFund IJohn SmithX
John SmithY1YFund IIJohn SmithY
John SmithZ1ZFund IIIJohn SmithZ
       
       
       
Team MemberDateProjectRecurring/Non recurringHours TeamMember&Project
John Smith2021 September 27.09. - 03.10.XRecurrent12 John SmithX
John Smith2021 September 27.09. - 03.10.YNon-recurrent4 John SmithY
John Smith2021 September 27.09. - 03.10.ZRecurrent4 John SmithZ
John Smith2021 September 27.09. - 03.10.AllRecurrent6 John SmithAll
John Smith2021 September 27.09. - 03.10.XNon-recurrent5 John SmithX
John Smith2021 September 27.09. - 03.10.YRecurrent5 John SmithY
John Smith2021 September 27.09. - 03.10.ZRecurrent4 John SmithZ
John Smith2021 September 20.09. - 26.09.AllRecurrent40 John SmithAll
John Smith2021 September 13.09. - 19.09.XRecurrent10 John SmithX
John Smith2021 September 13.09. - 19.09.YNon-recurrent5 John SmithY
John Smith2021 September 13.09. - 19.09.ZNon-recurrent16 John SmithZ
John Smith2021 September 06.09. - 12.09.AllNon-recurrent10 John SmithAll
John Smith2021 September 06.09. - 12.09.XRecurrent15 John SmithX
John Smith2021 August 30.08. - 05.09.YNon-recurrent8 John SmithY
John Smith2021 August 30.08. - 05.09.ZRecurrent7 John SmithZ
John Smith2021 August 30.08. - 05.09.AllRecurrent15 John SmithAll

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!