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?
@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.
Did I answer your question? Mark my post as a solution.
Proud to be a Datanaut! Appreciate your Kudos🙂 Feel free to email me with any of your BI needs.