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
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
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:
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]
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.
Now, create your Measure 2 with the following formula:
Measure 2 = SUMX(Sales, Sales[Quantity] * RELATED(Product[Composition Weight]))
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.
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.
Hi 123abc, thanks for your reply.
I've created the following:
Measure 1 (calculated column)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
13 | |
9 |
User | Count |
---|---|
60 | |
23 | |
22 | |
19 | |
13 |