Forum Discussion

msingh2019's avatar
msingh2019
Icon for Helper II rankHelper II
7 years ago

Calculation involving multiple table relationships

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.
 
 
Maybe I am missing something straightforward here. Thanks a lot for your help.
Need Help
Show and Tell

5 Replies

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    7 years ago

    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's avatar
      msingh2019
      Icon for Helper II rankHelper II
      7 years ago
      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.
    • kentyler's avatar
      kentyler
      Icon for Solution Sage rankSolution Sage
      7 years ago

      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's avatar
        msingh2019
        Icon for Helper II rankHelper II
        7 years ago

        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.