Forum Discussion
Weird Rounding Error
Hi all, wanting to perform a very, very simple comparison with the following:
In the "Overuse" column the top record is showing 716 but that's not correct. The DAX is:
= ([Usage (KG)] - [Req (KG)]) * [Act. (EUR)]. The top then, (12,544 - 12,392) * 4.744 = 721.
Can I ask how on earth this isn't working please? The [Act. (EUR)] calculation is:
=VAR weightedPrice = SUMX(fUsage,fUsage[req_qty] * fUsage[meat_price])
VAR reqTotal = sum(fUsage[req_qty])
RETURN
ROUND(DIVIDE(weightedPrice,reqTotal),3)
The raw data is:
| order_number | production | req_qty | usage_qty | date_of_production | p_code | meat_code | standard_price | meat_price |
| 31056389 | 908 | 802 | 810 | 04/12/2023 | P10000 | RM0023 | 4.98 | 4.605 |
| 31056695 | 460 | 406 | 405 | 07/12/2023 | P10000 | RM0023 | 4.98 | 4.605 |
| 31056987 | 919 | 812 | 818 | 11/12/2023 | P10000 | RM0023 | 4.98 | 4.605 |
| 31057316 | 885 | 782 | 780 | 14/12/2023 | P10000 | RM0023 | 4.98 | 4.605 |
| 31057638 | 919 | 812 | 831 | 18/12/2023 | P10000 | RM0023 | 4.98 | 4.605 |
| 31057735 | 454 | 401 | 400 | 19/12/2023 | P10000 | RM0023 | 4.98 | 4.605 |
| 31058261 | 460 | 406 | 405 | 27/12/2023 | P10000 | RM0023 | 4.98 | 4.605 |
| 31058871 | 448 | 396 | 405 | 03/01/2024 | P10000 | 10082633 | 4.98 | 4.98 |
| 31059168 | 675 | 596 | 599.5 | 06/01/2024 | P10000 | RM0023 | 4.98 | 4.605 |
| 31059328 | 1317 | 1163 | 1170 | 08/01/2024 | P10000 | 10082633 | 4.98 | 4.98 |
| 31059962 | 851 | 752 | 760 | 13/01/2024 | P10000 | RM0023 | 4.98 | 4.605 |
| 31060099 | 851 | 752 | 765 | 15/01/2024 | P10000 | 10082633 | 4.98 | 4.98 |
| 31060690 | 1362 | 1203 | 1200 | 20/01/2024 | P10000 | RM0023 | 4.98 | 4.605 |
| 31061129 | 919 | 812 | 810 | 24/01/2024 | P10000 | RM0023 | 4.98 | 4.605 |
| 31061403 | 1322 | 1168 | 1200 | 27/01/2024 | P10000 | 10082633 | 4.98 | 4.98 |
| 31061882 | 1280 | 1131 | 1185 | 31/01/2024 | P10000 | 10082633 | 4.98 | 4.98 |
Thank you so much
12,544 is not the real value. It's a rounded display value. That's what I tried to guide you to with my previous answer.
The real sum is 12543.50.
Subtracting 12392 from 12543.50 equals 151.5. This could be rounded to 151 or 152 . Multiply those values by 4.98 and you get close to 752 and 757. So that explains what you're seeing. And, as before, the Overuse measure is being displayed as a whole number so it's being rounded for display purposes.
--
So what to do about it? First, look at the display format of each measure. Click on the measure in the data pane (on the right) and the MeasureTools ribbon appears. Look at the Format section. Powerbi makes assumptions here with data type (and decimal places, if applicable). You can over-ride these choices. Remember, this is for display. The 'real' values are held behind the scenes.
In your case, the display values have been rounded for display and that's leading to confusion.
3 Replies
There's plenty of scope for rounding issues due to the number of different measures involved.
Item 1 for investigation : Usage (kg) in the visual = 12544.
but there's a 599.5 value in the data so something's been rounded there.
Hi HotChilli thank you for response. So to avoid this what can I best do? The DAX I use merely contains the calculations, but in the visual display I am choosing "whole number", so I am not rounding anything up, but something is definitely amiss and I simply cannot find it.
The only DAX I am using for this calculation is 3 measures:
[Total Usage]
=SUM(fUsage[usage_qty])
[REQ (KG)]
=SUM(fUsage[req_qty])
ACTUAL COST [Act. (EUR)] - (This returns weighted average)
=VAR weightedPrice = SUMX(fUsage,fUsage[req_qty] * fUsage[meat_price])
VAR reqTotal = sum(fUsage[req_qty])RETURN
ROUND(DIVIDE(weightedPrice,reqTotal),3)
OVERUSE (EUR)
=([Total Usage]-[Req (KG)])*[Act. (EUR)]
Interestingly, I also get the same issue when using the standard price which is a fixed value...
(12,544-12,392) * 4.980 = 757, not the 752 shown.
Any help would be greatly appreciated, I have spent many days on this and cannot find a solution.
12,544 is not the real value. It's a rounded display value. That's what I tried to guide you to with my previous answer.
The real sum is 12543.50.
Subtracting 12392 from 12543.50 equals 151.5. This could be rounded to 151 or 152 . Multiply those values by 4.98 and you get close to 752 and 757. So that explains what you're seeing. And, as before, the Overuse measure is being displayed as a whole number so it's being rounded for display purposes.
--
So what to do about it? First, look at the display format of each measure. Click on the measure in the data pane (on the right) and the MeasureTools ribbon appears. Look at the Format section. Powerbi makes assumptions here with data type (and decimal places, if applicable). You can over-ride these choices. Remember, this is for display. The 'real' values are held behind the scenes.
In your case, the display values have been rounded for display and that's leading to confusion.
