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_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

Need Help

3 Replies

  • 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?