Calculations over different tables

[Anonymous]

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(%)).
You can see the product table as follow:
Chocolatecookie - 200 - chocolate - 50
Chocolatecookie - 200 - sugar - 2

....
=> Meaning, 1 chocolatecookie weights 200 gr and contains 50% chocolate and 2% sugar (and ...).

(Both tables are related through Item)

I want to calculate things like total weight per composition, .. and start by calculating this for just 1 'piece'(in the Product table)
Measure 1 = Product[Weight] * Product[Composition] 
=> 200 g * 50% = 100 (chocolate)
=> 200 g * 2% = 4 (sugar)

So far, so good.
Then I one to calculate the amount of chocolade, so I include my sales:
Measure 2 = sum(Product[Measure 1]) * sum(Sales[Quantity])
This get's the job done, I see for every line the correct calculated quantities.

Only the totals (total of column Measure 2) is way to high, and when I make some visuals to see the share of chocolate, sugar, .. I get figures that aren't correct.
I assume that somehow Power BI goes calculating the total of weight * total of composition * total sales ... (which might be logic as I'm using sum function in Measure 2, but I can't fix it with other things (tried product, related, lookupvalue, .. always get an error ..).

(PS: I also include in my measure a divide by 1000, to convert the gram to kilogram).

Many thanks

[123abc]

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]

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