Transfer amount from one object to another

[felixG]

Hello,

I would like to transfer the amount of one of the objects to another.

For example, I have the following four tables:

1) import

object	amount
A1	50
A2	200
A3	250
A4	300
A5	200

2) matrix

object (send)	group
A1	1
A3	2

3) groups

group	object	allocation
1	A2	100%
2	A4	50%
2	A5	50%

4) master table with all objects (unique values) > This table is related with 1) by the object.

object	title
A1	...
A2	...
A3	...
A4	...
A5	....

The result that Im looking for is to be done with measurement and not with calculated columns.

The result should look like this:

object	amount (sum)	group	amount (send)	amount (receive)	allocation
A1	50	1	50		(50-50=) 0
A2	200			(100%*50=) 50	(200+50=) 250
A3	250	2	250		(250-250=) 0
A4	300			(50%*250=) 125	(300+125=) 425
A5	200			(50%*250=) 125	(300+125=) 425

Can anyone help please? Thanks for your time!

[felixG]

- Closed -

My tested Solution:

+ Merging and appending tables in query

+ Creating a new import table, includes new calculated columns of amount

+ Creating Measures based on new import table

Best Regards,

Felix

[v-jayw-msft]

Hi @felixG ,

Can you provide more information?

1# How do we group these row and why A2, A4, A5 is blank group.

2# How do we get the "send" value and why A2, A4, A5 is blank value.

3# Can you explain the formula of "receive"?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[felixG]

Hi @v-jayw-msft ,

thanks for your reply.

#1 each object can be only a sender or a receiver of amount. the groups (group table) define which object acts as a sender (object A1 & A3). depending on the groups, the costs are then distributed to the recipients. i.e. if the object is part of group 2, the costs will spread across A4 and A5 depending on the given percentages in allocation of table "groups".

A2, A4, A5 is blank group cause they dont act as a sending object.

#2 the send value is given in table 1) import. its the sum of the amount. i.e. A1 is sending 50 to group 1 (= A2 = 100%).

A2, A4, A5 is blank value cause they dont act as a sending object.

#3 the formula of "receive" is the result of the tables before. i try to explain it in formula:

> first, check if the object belongs to a group (table matrix)

> if the object is part of a group, find the recipients of the costs (table groups)

> allocate the sum total of amount (table import) by the given percentage in table groups to the given object in table groups

> The total of A3 is 250. A3 is part of group 2

> group 2 divides the costs into the objects A4 and A5 (50 percent each)

> A3_new = 250-250; A4_new = 50%*250; A5_new = 50%*250

Please ask if you need more information.

Best Regards,

felix