Power query cross allocation

[Anonymous]

I have a table like this:

Department	Other Department	Allocation
A	B	20%
A	C	80%
B	A	50%
B	C	50%
C	A	40%
C	B	60%

How can I calculate the final allocation percentage in power query?

Department	Other Department	Final Allocation
A	B	?%
A	C	?%
B	A	?%
B	C	?%
C	A	?%
C	B	?%

[Anonymous]

Dear Stephen, thx for your reply. The logic is, take A allocation to C for example, firstly A allocate 20% to B, and 80% to C, secondly, B allocate 50% to C, so final A to C is 80%*A + 20%*50%*A = 90%.

[Anonymous]

Hi @Anonymous ,

What's your final allocation?

Please show me your expected results.

Below is your raw data, right?

Below the "Final Allocation" field is your expected result, right?

Please tell me what the corresponding expected result is, and the calculation logic.

Best Regards,

Stephen Tao

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