Iterate value with unrelated values by each row

[Czempijan]

Hi,

I have 2 unrelated tables. Let's call the yellow one below with amount to be split and the second one customer table. I woudl like to add data from yellow one to blue one and split amount from yellow one (given value for each BA) based on revenue ratio but only for each BA from customers table as shown below:

Does anyone know how to do it??? Thank you in advance.

[Greg_Deckler]

Sample data as text please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Czempijan]

and here comes second table:

Date	Date EDW	Customer	Rev	COS	BA	Order	Customer pther name
04-05-20	202007	abradab	300	22	PQER	1	aaa
04-05-20	202007	abradab	22	33	PQER	1	aa
04-05-20	202007	abradab	33	21	PQER	2	aa
04-05-20	202007	abradab	1	33	PQER	3	sss
04-05-20	202007	abradab	1111	3444	PQER	3	ss
04-05-20	202007	abradab	2222	7	PQER	4	d
04-05-20	202007	bladarab	3444	7	PQER	5	ddd
04-05-20	202007	bladarab	45465	7	PQER	5	dd
04-05-20	202007	bladarab	68687	44	PQER	7	dd
04-05-20	202007	bladarab	7975	5	PQER	765	d
04-05-20	202007	bladarab	245565	757	PQER	8798	zet

[sanimesa]

@Czempijan  In the first table you still have duplicate rows (key values). Unless there is a uniquely identifying characteristic, it is hard to see how you will pick the 700 number. Is there an order number that links the two tables? 

[Czempijan]

so the correct yellow table as follows:

Date	Date EDW	RTM	Type	BA	Amount
04-05-20	202007	Direct	Hedge	PQTR	100
04-05-20	202007	Direct	Hedge	PQEA	200
04-05-20	202007	Direct	Hedge	PQEB	300
04-05-20	202007	Direct	Hedge	PWEC	400
04-05-20	202007	Direct	Hedge	PQED	500
04-05-20	202007	Direct	Hedge	PQEF	600
04-05-20	202007	Direct	Hedge	PQER	700
04-05-20	202007	Direct	Hedge	PIUY	800
04-05-20	202008	Direct	Hedge	PQTR	900
04-05-20	202008	Direct	Hedge	PQEA	1000
04-05-20	202008	Direct	Hedge	PQEB	1100
04-05-20	202008	Direct	Hedge	PWEC	1200
04-05-20	202008	Direct	Hedge	PQED	1300
04-05-20	202008	Direct	Hedge	PQEF	1400
04-05-20	202008	Direct	Hedge	PQER	1500
04-05-20	202008	Direct	Hedge	PIUY	1600
04-05-20	202009	Direct	Hedge	PQTR	1700
04-05-20	202009	Direct	Hedge	PQEA	1800
04-05-20	202009	Direct	Hedge	PQEB	1900
04-05-20	202009	Direct	Hedge	PWEC	2000
04-05-20	202009	Direct	Hedge	PQED	2100
04-05-20	202009	Direct	Hedge	PQEF	2200
04-05-20	202009	Direct	Hedge	PQER	2300
04-05-20	202009	Direct	Hedge	PIUY	2400

[Czempijan]

No there is no link between the tables. 700 is for one BA PQER ( I think I have made mistake while pasting sample data as I put 100 againts PQER not 700):

04-05-20

202007

Direct

Hedge

PQER

700

[Czempijan]

Text attached I hope this is what you have expected?

Date	Date EDW	RTM	Type	BA	Amount
04-05-20	202007	Direct	Hedge	PQER	100
04-05-20	202007	Direct	Hedge	PQEA	200
04-05-20	202007	Direct	Hedge	PQEB	300
04-05-20	202007	Direct	Hedge	PWEC	400
04-05-20	202007	Direct	Hedge	PQED	500
04-05-20	202007	Direct	Hedge	PQEF	600
04-05-20	202007	Direct	Hedge	PQTR	700
04-05-20	202007	Direct	Hedge	PIUY	800
04-05-20	202008	Direct	Hedge	PQER	900
04-05-20	202008	Direct	Hedge	PQEA	1000
04-05-20	202008	Direct	Hedge	PQEB	1100
04-05-20	202008	Direct	Hedge	PWEC	1200
04-05-20	202008	Direct	Hedge	PQED	1300
04-05-20	202008	Direct	Hedge	PQEF	1400
04-05-20	202008	Direct	Hedge	PQTR	1500
04-05-20	202008	Direct	Hedge	PIUY	1600
04-05-20	202009	Direct	Hedge	PQER	1700
04-05-20	202009	Direct	Hedge	PQEA	1800
04-05-20	202009	Direct	Hedge	PQEB	1900
04-05-20	202009	Direct	Hedge	PWEC	2000
04-05-20	202009	Direct	Hedge	PQED	2100
04-05-20	202009	Direct	Hedge	PQEF	2200
04-05-20	202009	Direct	Hedge	PQTR	2300
04-05-20	202009	Direct	Hedge	PIUY	2400

[sanimesa]

@Czempijan  Your example is not very clear but it looks like you could benefit from adding a dimension table for unique BAs and a calendar table. Then you will build relationships from the dimension tables to the two fact tables. Finally you will be able to build aggrergate measures for totals and ratios. 

[Czempijan]

It will not work as I will not be able to filter by customer in both directions and this is what I am looking for.