Weird Rounding Error

[shanestocks]

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

[HotChilli · Accepted 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.

[HotChilli]

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.

[shanestocks]

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.