Calculation involving multiple table relationships

[msingh2019]

I am facing a problem in trying to perform a calculation involving multiple tables. This is the scenario - 

- Table [SKU Master] contains SKU and Absolute Weight of a product

- Table [Consignment Cost] contains Consignment ID and Consignment Cost - stores cost for each consignment

- Table [Consignment SKU] contains Consignment ID, SKU and Quantity (which SKU was shipped in which Consignment and number of units shipped)

- Table [Sales Tx] is the fact table where I need to calculate the Transportation Cost (for a combination of SKU and Consignment)

In the [Consignment SKU] table I calculate the Transportation Cost based on the weight ratio (absolute weight of the SKU / absolute weight for each SKU in the same consignment * quantity ). I did this using a calculated column - 

Weight Ratio Per SKU = 'Consignment SKU'[Absolute Weight]/SUMX(FILTER('Consignment SKU','Consignment SKU'[Consignment ID]=EARLIER('Consignment SKU'[Consignment ID])),'Consignment SKU'[Absolute Weight]*'Consignment SKU'[Quantity])

I then multiply the weight ratio by the consignment cost to arrive at the Transportation Cost at the Consignment SKU level. Next, I need the Transportation Cost in the [Sales Tx] table based on unique combination of Consignment ID + SKU. The problem I am facing is Power BI is not allowing me to create a direct relationship between [Sales Tx] and [Consignment SKU] (via a ConsignmentSKU calculated column that I created) because of relationships that I already have.

The table relationships look like this - 

[Consignment SKU] >> [SKU Master] via SKU to retrieve the Absolute Weight

[Consignment SKU] >> [Consignment Cost] via Consignment ID to retrieve the Consignment Cost

[Sales Tx] >> [Consignment SKU] via ConsignmentSKU which Power BI is not allowing to build an active relationship.

Link to PBIX file is here - https://1drv.ms/u/s!ArkttS02M595hD2tUPGa_pxKc1Ib?e=y5eUkH

Maybe I am missing something straightforward here. Thanks a lot for your help.

[v-diye-msft]

Hi @msingh2019 

You can use the function USERELATIONSHIP  to enable the inactive relationship.

[kentyler]

Looking at you power bi file, it looks like  you already have a relationship between Consignment SKU and Sales Tx on the Consignment SKU column... so it makes it confusing to understand your problem.

"[Sales Tx] >> [Consignment SKU] via ConsignmentSKU which Power BI is not allowing to build an active relationship."

[msingh2019]

If you look at my pbix file you will notice that the relationship between Sales Tx and Consignment SKU (via the ConsignmentSKU column) is not active. I tried making the relationship active but Power BI is not allowing this direct relationship because that will introduce ambiguity between SKU Master and Sales Tx. 

The reason why I need the relationship between Consignment SKU and SKU Master is I need the Absolute Weight in Consignment SKU. Is there any way I can pull the Absolute Weight into Sales Tx then indirectly looking up from there into Consignment SKU? Note that the Absolute Weight is at SKU level. If that works then problem is solved.

[kentyler]

It looks like the table labeled "Sales Tx" is actually about orders.

You have a 1 to many relationship between the Consignment SKU table and the Sales Tx(Orders) table.

Is it that one Consignment SKU go in many Orders ?

[msingh2019]

Yes, one Consignment SKU goes into many orders. The Sales Tx table represents customer orders and how each order is fulfilled (which SKU from which Consignment). The Quantity column in the Consignment SKU table defines how many units of one SKU in a particular Consignment. I hope this makes sense.