Forum Discussion

Kimbornhoft's avatar
Kimbornhoft
Helper I
9 years ago

Incorrect calculated measure total

Hi,   I have a new measure    Total Shipped Value = (sum(REV_HistoryReport[Total Li Valu Amt]) /sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled]))   which works correctly ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Ya, that won't math correctly.  If math was a verb.

 

1  2

2  3

3  4

 

(1+2+3) * (2 + 3 + 4) = 6 * 9 = 54.

(1*2) + (2*3) + (3*4) = 2 + 6 + 12 = 20.

 

You want the latter, but are doing the fomer.

 

I would think...

 

Total Shipped Value = sumx(REV_HistoryReport, REV_HistoryReport[Total Li Valu Amt]) / REV_HistoryReport[Ord Bill Unit Qty] * OpenOrders[Qty Filled])

 

But... I'm skeptical that is going to handle OpenOrders correctly.  How are those tables related?

Kimbornhoft's avatar
Kimbornhoft
Helper I
9 years ago

I've tried this measure but it includes this error message

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Okay, so those tables are not (directly) related.  I would call them independent fact tables.

     

     

    I had a stray paren somewhere, I would think this:

    Total Shipped Value := SUMX (
        REV_HistoryReport,
        REV_HistoryReport[Total Li Valu Amt] / REV_HistoryReport[Ord Bill Unit Qty]) * OpenOrders[Qty Filled]

    So, take the avg $ amt (total amt / unit quantity) ... and multiple by the qty of filled orders?   That reads ok.

  • Kimbornhoft's avatar
    Kimbornhoft
    Helper I
    9 years ago

    Hello,

     

    Thanks so much for replying. This is the error message that I get when I use that formula.

     

    A single value for column 'Qty Filled' in table 'OpenOrders' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.