Forum Discussion
Incorrect calculated measure total
Hi Kimbornhoft
Ideally you can use the new DIVIDE DAX function, which will allow you to set a figure when there is a divide by zero error.
Total Shipped Value =
DIVIDE (
SUM ( REV_HistoryReport[Total Li Valu Amt] ),
SUM ( REV_HistoryReport[Ord Bill Unit Qty] ) * SUM ( OpenOrders[Qty Filled] ),
0
)This will return a zero if there are no values.
Thank you for replying, unfortunately, this formula returned all lines as $0.
The "0" value appears in the multiplication side of equation if that makes a difference.
GilbertQ is correct in that using DIVIDE( ) is a good idea to avoid divide by zero errors. But in your case, you are just doing something "mathematically wrong".
Quantity Price SubTotal
2 3 6
3 4 12
Here the total is 18 (6+12), it is not sum(quant) * sum(price) = 5 * 12 = 60.
I think you have something similiar going on? Think we will need to see some sample data.
You might try:Total Shipped Value = SUMX(REV_HistoryReport, CALCULATE(sum(REV_HistoryReport[Total Li Valu Amt])/sum(REV_HistoryReport[Ord Bill Unit Qty]) * sum(OpenOrders[Qty Filled])))
This solves the issue 99% of the time. Here is an exception: the correct total is $59,051 but Power BI is doubling that total when an item appears muliple times to $118,102.
3131BE 1/10/2017 $29,526 189 3131BE 1/10/2017 $29,526 189
