SUMX returning blank results

[katrina890]

Hiya

I have a Unit Price col from one table and a Qty in Stock col from another table.

I want to multiply the 2 together to get a total.

I have tried SUMX('Purchase Order Receipts', [QTY_IN_STORE] * (SUM('Purchase Orders'[Unit Price])))

This produces wrong totals.

I have tried value = SUM('Purchase Orders'[Unit Price])  then SUMX('Purchase Order Receipts', [QTY_IN_STORE] * [value])

This produces no totals - just a blank col.

This works for another calculation SUMX('Purchase Order Receipts', [QTY_IN_STORE] * 'Purchasing Budget'[Inv Value])

Cant understad why it works for one set and not another.

Thanks

[Stachu]

can you post sample data and syntax for [QTY_IN_STORE]

[katrina890]

PO Spent = SUMX('Purchase Order Receipts', [QTY_IN_STORE] * [value])

Sytax for [QTY_IN_STORE]

select distinct p.order_no, p.contract, p.part_no, p.vendor_no, p.state, p.arrival_date, i.qty_in_store,
(AC.ACCOUNTING_YEAR * 100) + AC.ACCOUNTING_PERIOD AS "Arrival Period"
from PURCHASE_RECEIPT_NEW p
left join RECEIPT_INVENTORY_LOCATION i on i.order_no = p.order_no and i.part_no = p.part_no
LEFT JOIN ACCOUNTING_PERIOD AC ON p.arrival_date BETWEEN AC.DATE_FROM AND AC.DATE_UNTIL AND AC.YEAR_END_PERIOD_DB = 'ORDINARY'

Thanks

[Stachu]

I should have been more specific - can you post examples (e.g. top 10 rows) from tables
'Purchase Order Receipts'
'Purchase Orders'
and explain the join between them?

I think the syntax you originally posted 

SUMX('Purchase Order Receipts', [QTY_IN_STORE] * (SUM('Purchase Orders'[Unit Price])))

is giving incorrect results, because there are multiple Prices referenced to single order. What the granularity at which the multiplication should be calculated?