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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

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!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!