SUMX of two different table

[Anonymous]

Good evening everyone

I have a question regarding SUMX. I have sales data that contain the Quantity of Sales in the Sales Table and the net price of items in the Dimension Product table. I want to calculate the total price, which is the Quantity*Net Price. Both table already related through ProductID

How to do that?


Thank you

[Ashish_Mathur · Accepted solution]

Hi,

In the Sales Table, you may write a calculated column formula (RELAED() function) to bring over the price from the Price table.

Net price = related(Prices[Price])

In the Sales tab, you may write another calculated column formula to calculated the Revenue

Rev = Sales[Net price]*Sales[Quantity]

Now write a measure called Revenue and drag that to your visual

Revenue = sum(Sales[Rev])

Hope this helps.

[Anonymous]

Unfortunately, the Net Price calculated column result using RELATED is the sum of price, not price of a single item

[Ashish_Mathur]

Cannot be.  RELATED() does not add.  It's like the VLOOKUP() of MS Excel.

[Anonymous]

Sorry my bad, Your solution works just fine. Thank you

[Ashish_Mathur]

You are welcome.

[littlemojopuppy]

Hi @Anonymous try something like this...

SUMX(
	[Sales],
	Quantity * RELATED(Product[Price])
)

Without seeing your data model, I doubt the table and field names are correct, but the pattern should work.  Hope this helps!  🙂