Measure for a 'basic' calculation

[Dayna]

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

[vojtechsima · Accepted 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.

[Dayna]

Thank you @vojtechsima for all your help 🙂 

[vojtechsima · Accepted 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.

[Dayna]

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

[vojtechsima · Accepted solution]

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.