Forum Discussion

lekkerbek's avatar
lekkerbek
Helper IV
6 years ago
Solved

Add column to a table based on a calculation

I have the following tables:

 

Mapping

GL numberGL nameMappingType
1GL name 1HousingP&L
2GL name 2General costsP&L

 

Share ratio based on activity

AdministrationGL numberGL nameActivityPercentage
Administration 11GL name 1Activity 160%
Administration 11GL name 1Activity 230%
Administration 11GL name 1Activity 310%

 

Transactions

GL numberGL nameAdministrationAmount
1GL name 1Administration 1100.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 numberGL nameAdministrationAmountActivity 1Activity 2Activity 3
1GL name 11100.00060.00030.00010.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.

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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

2 Replies