Good afternoon,
I had one column for Office which had 1-4 offices separated by a comma; therefore, I split by comma delimiter to create the necessary splits to do percentages on the number of Offices involved.
Since the maximum amount of Offices involved is 4, I am assigning 25% to each column when I do my calculations.
If there is not more than one office it produced a null which I handled through the creation of conditional columns in PowerQuery for Office 2 - 4.
If one office is involved the conditional columns allow for that office to occupy all 4 rows for full ownership (1) of the JobOrder.
If 2 offices are involved, they each occupy 2 of the 4 rows to receive half ownership (.5) of the JobOrder .
If 4 offices are involved, they each receive 1 of the 4 rows to receive quarter ownership (.25) of the JobOrder.
The only scenario I am having difficulty is when there are 3 offices involved because of my 4th split columns conditional logic that handles all other scenarios above.
I'd be utilizing the Office.1, Office.2.Split, Office.3.Split, and Office.4.Split columns as these handle the bulk of the scenarios, would I just have to handle the 3 office scenario through the original split columns?
Here's the logic for the Office.4.Split as well:
I appreciate and value your input.
Have a great Friday.
1 ACCEPTED SOLUTION
@sirlanceohlott solution attached and I hope that is what you are looking for.
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.
@sirlanceohlott I thnk you are taking different approach, you should split offices in rows and then thru DAX calculate the % based on # of office on a job. If you are not sure how to do this, share sample pbix with data and I will get you the solution.
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.
@sirlanceohlott solution attached and I hope that is what you are looking for.
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.
