Lookup Value or Related in Measure

[pociners]

Hi everyone its me again..

I wanna ask something easy and simple :D
I have two table, the first one is sales table:

The second one is the dim product:

I created a relationship between sales table and dim product through "Dim Product" Column.
After that I created a calculation sum of quantity:

[B]sum qty[/B]:=SUM([Quantity])

and I want to create another calculation like this:

[B]sum gross[/B]:=[sum qty]*LOOKUPVALUE(DIM_PRODUCT[PRICE],DIM_PRODUCT[DIM PRODUCT],SALES[PRODUCT]

but it doesnt work at all,

Actually The simplest way is create another column in sales table that calculate quantity*(lookupvalue or related function to get price), but i dont want to do that.
i want to do that calculation in measure, is that possible?

Well the logic is very easy, i just want to calculate Gross sales(Qty*Price) from each product.
Can you help me?
Sorry for my bad english

Thanks,

 

[Matt Allington]

Actually The simplest way is create another column in sales table that calculate quantity*(lookupvalue or related function to get price), but i dont want to do that.

Excellent - you are right to keep away from this.

Sorry for my bad english

Your English is very good and you question is very well explained.

You need to iterate over the dim table and then do the calc at every step

Formula 1 =SUMX(Dim_Product, Dim_Product[Dim Price] * calculate(Sum(Sales[Qty])))

I could have also written this, but I didn't deliberately.
Formula 2=SUMX(Dim_Product, Dim_Product[Dim Price] * [sum Qty])

formulas 1 and 2 are semantically identical and hence both give the same correct result. You might think that Formula 2 is equivalent to formula 3, but that would be incorrect.

Formula 3=SUMX(Dim_Product, Dim_Product[Dim Price] * Sum(Sales[Qty]))

A problem can arrive in your learning if you write formula 2 and you think you are writing formula 3 - then one day you do write formula 3 and it doesn't work. Then you get really confused.

I have a blog post today on another topic that covers context transition and filter propagation. If you read it, it should help you understand how this formula works. Many to Many Relationships in DAX Explained - Excelerator BI

SUMX is an iterator just like FILTER() is.

 

[pociners]

Excellent - you are right to keep away from this.



Your English is very good and you question is very well explained.

You need to iterate over the dim table and then do the calc at every step

Formula 1 =SUMX(Dim_Product, Dim_Product[Dim Price] * calculate(Sum(Sales[Qty])))

I could have also written this, but I didn't deliberately.
Formula 2=SUMX(Dim_Product, Dim_Product[Dim Price] * [sum Qty])

formulas 1 and 2 are semantically identical and hence both give the same correct result. You might think that Formula 2 is equivalent to formula 3, but that would be incorrect.

Formula 3=SUMX(Dim_Product, Dim_Product[Dim Price] * Sum(Sales[Qty]))

A problem can arrive in your learning if you write formula 2 and you think you are writing formula 3 - then one day you do write formula 3 and it doesn't work. Then you get really confused.

I have a blog post today on another topic that covers context transition and filter propagation. If you read it, it should help you understand how this formula works. Many to Many Relationships in DAX Explained - Excelerator BI

SUMX is an iterator just like FILTER() is.

Hey Matt, you are so greeaaaaaattt!!!!!!
Thanks for your explaination..