cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

Add column to a table based on a calculation

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?

1 ACCEPTED SOLUTION
Super User

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[GL number] =Transactions[GL number] && activity[Activity] ="Activity 2"),activity[percent])

&& 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

2 REPLIES 2
Super User

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[GL number] =Transactions[GL number] && activity[Activity] ="Activity 2"),activity[percent])

&& 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

Helper IV

@amitchandak thanks a million!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.