Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |