Forum Discussion
Incorrect calculated measure total
Unfortunately, formula still isn't returning the correct values. I'm not sure where to go next.
Hi
I'm going back to the beginning. Unfortunately, the suggestions above have not worked. All of the values in this table are correct except for the new measure field. It should be $2,260,502. The new measure is
Total Shipped Value = (sum(REV_HistoryReport[Total Li Valu Amt]) /sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled]))
It seems to be related to the math that happens when a qty filled column is 0.
Maybe already answered above, but double checking here... your Total Shipped Value... is that a measure or a calculated column?
It is a measure.
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?
Here are the relationships
Is there anything else you need to understand the relationships. Thanks for your help. I'm a novice at Power BI so your help is very much appreciated.
We thought it would work to create a relationship between the open orders and the Rev_HistoryReport based on the MMMID but we get this error message.
I've tried this measure but it includes this error message
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.
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.