Revenue in matrix

[greVia]

Hello,

I need to have a matrix with total revenue measure as Revenue =Unit_Price*Quantity the problem is that these fields are in two differente table.

My db is:

Orders	ordersRow	OrdersShipped
OrderNumber(1)	OrderNumber(m)	Index(m)
Customer Name	Index(1)	Quantity_Shipped
WBS	Part_number	Shipping_Date
	Quantity
	TotalValue
	UnitPrice (useless)

My idea was to use this measure in Matrix: 

revenue= SUM(OrdersRows[TotalValue])/SUM(OrdersRows[Quantity])*SUM(Ordershipped[Quantity_Shipped])

it is right (it should)  with row but it is totally wrong with subtotal and total.

Could you please help me?

many thanks

[amitchandak · Accepted solution]

@greVia , Multiplication should done at line or you need have common group like order no

revenue= divide(SUM(OrdersRows[TotalValue]),Sumx(values(Order[Order]),calculate(SUM(OrdersRows[Quantity])*SUM(Ordershipped[Quantity_Shipped]))))

refer by the blog for a common level between two tables

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[amitchandak · Accepted solution]

@greVia , Multiplication should done at line or you need have common group like order no

revenue= divide(SUM(OrdersRows[TotalValue]),Sumx(values(Order[Order]),calculate(SUM(OrdersRows[Quantity])*SUM(Ordershipped[Quantity_Shipped]))))

refer by the blog for a common level between two tables

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[greVia · Accepted solution]

I solved the problemi with 

SUMX(Ordershipped,Ordershipped[Quantity]*RELATED(OrdersRows[UnitPrice]))

thank  your reply i found the solution many thanks

[greVia]

I read part of your blog it is so interesting, thanks.  

But I have some doubts:

1) Multiplication should done at line" It means that I should create a column? I didn't understand

2)What is common group (I am looking for on internet but I am not finding), if I use this common group then I can use your formula?

Sorry for questions and thanks