Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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.

 

 



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.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.