Forum Discussion

katrina890's avatar
katrina890
Frequent Visitor
8 years ago

SUMX returning blank results

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_...
Need Help
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
8 years ago

can you post sample data and syntax for [QTY_IN_STORE]

katrina890's avatar
katrina890
Frequent Visitor
8 years ago

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's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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?