Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
j_ocean
Helper V
Helper V

Fixed Decimal vs Floating Point Precision Problem

I have a column of many dollar values. I have confirmed none go below a penny. When summed, many values suddenly gain up to 30 decimal places. Rounding functions fail to clear the issue.

 

I have solved this problem with adding the "Precision.Decimal" argument to my List.Sum functions in a Group By step for one source where I need to preserve query folding, iaw another community forum post (and on a CSV source by setting the columns to fixed decimal/currency format). This is less than ideal as the Precision.Decimal argument is not visible in the GUI without opening advanced editor, and long story short such an arcane yet easy-to-stumble-into failure mode is going to be a political problem.

 

That said, my question is twofold:

1) Why does Power Query exhibit this behavior in floating point operations in the first place? Wasn't something like this a big scandal with the old Pentium I processors?

2) What do I tell the GUI-only, Power BI-skeptic analysts when they notice this happening, flip their $#!7 and try to go back to Excel?

1 REPLY 1
HotChilli
Super User
Super User

Would you be willing to provide a pbix with the source file(csv?) so we can have a look at this please?

Not interested in the real data if it's private, just something that shows the issue please.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors