Calculating total cost from various tables based on filtered values

[BlueGorilla]

Hi 

I've been trying to solve this for the past coulple of days, but unsuccessfully. I've got the following 3 tables:

- table "Items Dimensions" with Items and their dimentions

- table "Supplier ABC"  for services 1 and 2 for the years 2020 and 2021 where the rates are based on the product of the dimentions (L x W x H) from the 1st table

- table "Supplier XYZ" for service 3 for the years 2020 and 2021 where the rates are based on the item length (from 1st table), as well as origin and destination

What I'm trying to achieve is when I select Item ID from the 1st table, as well as the Origin and Destination from the 3rd table - to get the corresponding rates for services 1, 2 and 3 in 2020 and 2021 and to be able to build a stacked column chart that will show each cost in the two years.

ITEM DIMENSIONS TABLE

Item ID	Length	Width	Height	L x W x H
A	100	10	5	5,000
B	150	15	10	22,500
C	200	20	20	80,000
D	200	20	20	80,000

SUPPLIER ABC

Year	Service	L x W x H From	L x W x H To	Rate
2020	Service 1	0	50,000	$     1,000
2020	Service 1	50,001	100,000	$     2,000
2020	Service 2	0	50,000	$     3,000
2020	Service 2	50,001	100,000	$     3,500
2021	Service 1	0	50,000	$     1,700
2021	Service 1	50,001	100,000	$     2,900
2021	Service 2	0	50,000	$     3,400
2021	Service 2	50,001	100,000	$     5,000

SUPPLIER XYZ

Year	Service	Length From	Length To	Origin	Destination	Rate
2020	Service 3	0	100	Location 1	Location 2	$ 100
2020	Service 3	101	200	Location 1	Location 3	$ 400
2020	Service 3	201	400	Location 1	Location 4	$ 600
2020	Service 3	0	100	Location 2	Location 1	$ 100
2020	Service 3	101	200	Location 2	Location 3	$ 300
2020	Service 3	201	400	Location 2	Location 4	$ 700
2021	Service 3	0	100	Location 1	Location 2	$ 220
2021	Service 3	101	200	Location 1	Location 3	$ 540
2021	Service 3	201	400	Location 1	Location 4	$ 760
2021	Service 3	0	100	Location 2	Location 1	$ 210
2021	Service 3	101	200	Location 2	Location 3	$ 350
2021	Service 3	201	400	Location 2	Location 4	$ 910

Can anyone, please, help with this, as I can't wrap my head around how to solve it...

Many thanks! 

[v-henryk-mstf]

Hi @BlueGorilla ,

As @lbendlin  said, your description does not correlate well with the three tables provided to know the exact inter-table relationships.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Henry

[lbendlin]

There is no obvious way to control the SUPPLIER ABC fact table from the SUPPLIER XYZ fact table.  So your statement:  " when I select Item ID from the 1st table, as well as the Origin and Destination from the 3rd table" is not something that is supported by your data model.  Please reconsider your requirement or provide more details.