Conditional Column (Variable)

[sirlanceohlott]

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.

[parry2k]

@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.

[sirlanceohlott]

@parry2k , I appreciate your help this morning as well as this afternoon.

I only took a screenshot of the specific columns within that fact table, but I trust your judgement on a different approach.

I'll send you a message separately with the information needed for you to review. 

[parry2k · Accepted solution]

@sirlanceohlott solution attached and I hope that is what you are looking for.