Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calculations over different tables

Hi all,   I'm doing something quite easy but the end result is not what I want. I have a sales table (item - customer - quantity) and a product table (item - weight - composition - composition(%))...
123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

It looks like you're working with Power BI and trying to calculate the total weight per composition based on your sales and product tables. The issue you're facing with Measure 2 likely arises from the way Power BI handles aggregation in calculated measures.

To calculate the total weight per composition correctly, you can follow these steps:

  1. Create a calculated column in your Product table to calculate the individual weight for each composition. This will help avoid aggregation issues when calculating Measure 2.

Composition Weight = Product[Weight] * Product[Composition (%) / 100]

 

  1. Create a relationship between your Sales table and Product table based on the "Item" column so that you can link the sales data to the product data.

  2. Now, create your Measure 2 with the following formula:

Measure 2 = SUMX(Sales, Sales[Quantity] * RELATED(Product[Composition Weight]))

 

  1. This measure calculates the total weight for each composition based on sales quantity and the Composition Weight column you created in step 1. Using SUMX ensures that the calculations are done at a row level and then summed up correctly.

  2. If you want to convert the total weight from grams to kilograms, you can add a division by 1000 as you mentioned:

Measure 2 (in kg) = SUMX(Sales, Sales[Quantity] * RELATED(Product[Composition Weight])) / 1000

 

Now, Measure 2 should provide you with the correct total weight for each composition, and you can use it in your visuals to analyze the share of chocolate, sugar, and other components accurately.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 123abc, thanks for your reply.

    I've created the following:
    Measure 1 (calculated column)

    Composition (weight/type) = Fabric_Data[WeightGByM2] * (Fabric_Data[UsableWidthInCm] / 100) * ( Fabric_Data[Composition (%)] / 100 )
    This gives me the quantity for each component, for 1 meter.
     
    Then I want to create the 2nd formula:
    Weight (composition) = sumx(Purchases_All,Purchases_All[Quantity] * related(Fabric_Data[Composition (weight/type)]))

    This gives me an error: The column 'Fabric_Data[Composition (weight/type)]' either doesn't exist or doesn't have a relationship to any table available in the current context.

    However, this column exists and there is a relation between Item (Fabric_Data) and Item (Purchases_All). The cardinality is 'Many on Many (*.*)', as both tables can contain several times the same item (for Purchases_All I can have orders for the same item (but other customer, date, quantity, ..) For Fabric_Data I have a row for each component, so if the product has 3 components, there will be 3 lines).
     
    Best regards