Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Apologies, I'm still getting used to the syntax in PowerBI so this may sound like a daft question!
I have two tables, one listing all components with their associated weight, the other specifying the quantity of the item by a product. For example:
Components:
Type | ID | Weight |
TypeA | 123 | 1.2 |
TypeA | 234 | 1.3 |
TypeB | 345 | 1.5 |
TypeC | 456 | 2.1 |
ComponentLevel:
TypeId | Level | Quantity |
123 | Pack | 1 |
234 | Pack | 2 |
345 | Pallet | 1 |
456 | Pallet | 1 |
What I want to do is two things:
1) Provide the weight of the item (i.e. weight * quantity) by item id
Expected Results: | |
TypeId | Total Weight |
123 | 1.2 |
234 | 2.6 |
345 | 1.5 |
456 | 2.1 |
2) Sum of the weights per level (i.e. Pack).
Expected Results | |
Level | Weight |
Pack | 3.8 |
Pallet | 3.6 |
How would I do this using measures, please?
I started with something like this, but I think when it goes a level, it'll be wrong as it'll use the max value across all my items
Pack Level Weight (inc. Quantity) =
calculate(max('Product Routing Components'[Quantity])*max(Components[Weight]),'Product Routing Components'[PackLevel] = "Pack")
if someone could help, or explain how I should go about this, it would be appreciated. I'm new to PowerBI but used QlikView for a while, so the syntax is throwing me off slightly!
Many thanks,
Dayna
Solved! Go to Solution.
Hi, @Dayna,
One measure to rule them all:
TotalWeight =
var VirtualTable = SUMMARIZE(Components, ComponentLevel[Quantity], Components[Weight], ComponentLevel[Level], Components[ID])
var FinalWeight = SUMX(VirtualTable, ComponentLevel[Quantity] * Components[Weight])
return FinalWeight
Given you have an active Active Relationship between TypeID and ID (ComponentLevel, Components), create a virtual table inside measure using SUMMARIZE, then iterate through this new table and you get you result for both your cases.
Hi, @Dayna,
I believe with this setup you can achieve your desired outcome, just bring every needed column to the Summarize and do your calculations.
Hi, @Dayna,
One measure to rule them all:
TotalWeight =
var VirtualTable = SUMMARIZE(Components, ComponentLevel[Quantity], Components[Weight], ComponentLevel[Level], Components[ID])
var FinalWeight = SUMX(VirtualTable, ComponentLevel[Quantity] * Components[Weight])
return FinalWeight
Given you have an active Active Relationship between TypeID and ID (ComponentLevel, Components), create a virtual table inside measure using SUMMARIZE, then iterate through this new table and you get you result for both your cases.
Hi @vojtechsima
Oh that's great, thank you! Summarize looks to be a very interesting function that I should look into more..!
If it wanted to expand this further and do a calculation based on another field from a different table based on the Level, would this be possible?
So a couple of links down the chain:
Where PackLevel = 'Pack', I'd like to multiple this quantity by the field in 'UM - SU - MU' for um_conv. All else returns the normal value.
Is this possible to extend further? I know this table is three links further in my relationship model.
Many thanks for all your help so far.
Kind Regards,
Dayna
Hi, @Dayna,
I believe with this setup you can achieve your desired outcome, just bring every needed column to the Summarize and do your calculations.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |