Forum Discussion
Helper IIncorrect calculated measure total
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])))
Helper IThis 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 |
I'm not totally sure which table we are talking about, but... errr, uhh... if we are talking about double rows in REV_HistoryReport, maybe inside the CALCULATE divide by COUNTROWS(REV_HistoryReport) ?
- Kimbornhoft
I have used this string,
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])))
but I'm not sure where to incorporate the count rows Could you help with this?
- Kimbornhoft
You were right in assuming it is the REV history report.