Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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(%)).
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


2 REPLIES 2
123abc
Community Champion
Community Champion

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
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.