Split of the amount based on a key

[KatkaS]

Dears

I have a simple table as below and I would need to calculate (create new calculated column?)

when Amount for Cost centers A, B, C remain as is, but Amount for cost center D (Service Common) is split between Cost centers A, B, C by 1/3.

Could you please help?

Thank you very much!

Period	Cost center	Service	Amount	New amount
March	A	Purchasing	xx	xx + 1/3 of CC D
March	B	Accounting	xx	xx + 1/3 of CC D
March	C	Controlling	xx	xx + 1/3 of CC D
March	D	Common	xx	-

[v-luwang-msft]

HI @KatkaS ,

You could try the following dax to create a new column:

amount2 = 
var test3=CALCULATE(VALUES('Table'[Amount])/3,FILTER(ALL('Table'),'Table'[Cost center]="D"))  
var TEST2= IF('Table'[Cost center]="A"||'Table'[Cost center]="B"||'Table'[Cost center]="C",'Table'[Amount]+test3,'Table'[Amount])  
RETURN TEST2

And final :

Wish it is helfpul for you!

Best Regards

Lucien

[amitchandak]

@KatkaS , Try a column like

new column =
var _1 = sumx(filter(Table, Table[Cost] = "D" && [Period] =earlier([Period]) && [Service] = "Common"),[Amount])
return
if(Table[Cost] in {A,B,C}, _1/3, Blank())