Allocation key for transactions in fact table

[rlcec]

Dear Power BI users,

this is an issue I'm trying to get my head around for a couple of days now. It concerns a Sales Dashboard for which I need to allocate one certain client to two different countries. This is a simplified version of my Fact Table with sales transactions:

Client	InvoiceNumber	Quantity	Amount	Country
A	1	100	2.000	US
A	2	200	3.000	US
B	3	50	800	UK
C	4	80	1.200	US

With an allocation table as follows:

Client	%	Country
A	80%	US
A	20%	UK
B	100%	UK
C	100%	US

This should give me the following result:

Client	InvoiceNumber	Quantity	Amount	Country
A	1	80	1.600	US
A	1	20	400	UK
A	2	160	2.400	US
A	2	40	600	UK
B	3	50	800	UK
C	4	80	1.200	US

Note that there's actually only one client which is allocated to different countries so my allocation table would only have two records.

Thank you very much in advance for any help on this topic. Please let me know if you need any further clarification on the issue.

Warm regards,

Ruben.

[mwegener]

Hi @rlcec 

has your question been answered?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @rlcec ,

You may download my PBIX file from here.
Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @rlcec ,

use PowerQuery with "Merge Queries"

https://www.poweredsolutions.co/2019/01/03/merge-operations-in-power-bi-power-query-part-1-left-oute...

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.