Add column to a table based on a calculation

[lekkerbek]

I have the following tables:

Mapping

GL number	GL name	Mapping	Type
1	GL name 1	Housing	P&L
2	GL name 2	General costs	P&L

Share ratio based on activity

Administration	GL number	GL name	Activity	Percentage
Administration 1	1	GL name 1	Activity 1	60%
Administration 1	1	GL name 1	Activity 2	30%
Administration 1	1	GL name 1	Activity 3	10%

Transactions

GL number	GL name	Administration	Amount
1	GL name 1	Administration 1	100.000

There is a relation between mapping and transactions (both).

There is a relation between mapping and share ratio (both)

I would like to add columns to the transaction table to show the costs per activity. It would have to look like this:

Transactions (new)

GL number	GL name	Administration	Amount	Activity 1	Activity 2	Activity 3
1	GL name 1	1	100.000	60.000	30.000	10.000

Can somebody please help me with the code to accomplish this as my efforts are not returning values. Could this also be done using a measure?

Thanks is advance for your help.

[amitchandak · Accepted solution]

You can new columns like this in the transaction table. Assuming the second table is there

Activity 1 = Transactions[Amount] * maxx(filter(activity,activity[Administration] =Transactions[Administration] 
					&& activity[GL number] =Transactions[GL number] && activity[Activity] ="Activity 1"),activity[percent])
					
	
	Activity 2 = Transactions[Amount] * maxx(filter(activity,activity[Administration] =Transactions[Administration] 
					&& activity[GL number] =Transactions[GL number] && activity[Activity] ="Activity 2"),activity[percent])
					
	
	Activity 3 = Transactions[Amount] * maxx(filter(activity,activity[Administration] =Transactions[Administration] 
					&& activity[GL number] =Transactions[GL number] && activity[Activity] ="Activity 3"),activity[percent])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[lekkerbek]

@amitchandak thanks a million!