Add one rows value to two rows

[JakobSt]

Hello, 

I'm trying to find a solution for the following Problem: I have a table and for each month I'm trying to add 50 % of the value of Cost Center 912 to 900 and 50% to 901. I would like to do this with a new measured column. Is that possible? 

Month	Cost Center	Balance
Jan	900	100
Jan	901	100
Jan	912	200
Feb	900	100
Feb	901	100
Feb	912	100

Month	Cost Center	Balance	SplitBalance
Jan	900	100	200
Jan	901	100	200
Jan	912	200	200
Feb	900	100	150
Feb	901	100	150
Feb	912	100	100

Thank you

[lbendlin]

I would like to do this with a new measured column

Do you want a measure, or a calculated column?

[JakobSt]

Sorry, I meant a calculated column!

[lbendlin]

SplitBalance =

var m = [Month]

return [Balance] + if([Cost Center] in {"900","901"},0.5*sumx(FILTER(Table2,[Month]=m && [Cost Center]="912"),[Balance]))

[JakobSt]

Thank you a lot for that answer! 

What would I have to change if the split was not 50/50 and I had multiple splits to do?

I was thinking about creating another table for the splits and reference that in the DAX formula.

Cost Center	Cost Center A	Split A	Cost Center B	Split B
912	900	0,45	901	0,55
913	905	0,30	906	0,70

[v-sgandrathi · Accepted solution]

Hi @JakobSt,

Since you have multiple splits with different percentages, you should create a separate Splits table listing the source Cost Center, target Cost Centers, and split percentages.
In your calculated column, you would:

Look up the correct split percentage from the Splits table. Find the balance of the source Cost Center for the same Month then multiply the source balance by the split percentage and add that value to the current Balance.

Use functions like LOOKUPVALUE or RELATED to fetch the split percentages.

Use SUMX or CALCULATE with FILTER to get the balance of the original Cost Center (e.g., 912 or 913) for the same month, then calculate the new SplitBalance in your calculated column.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Thank you.

[v-sgandrathi]

Hi @JakobSt,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.