Hello! I would like to ask for help regarding the calculated field I built. Here is the formula:
round(round({rate} / 365, 10) * {amount}, 5)
Basically, my output needs to be in 5 decimal places so I can compare it with another file. However, here is the output I’m receiving:
The calculated field output keeps rounding off to 4 decimal places instead of 5. Is there a way to fix this? Thank you!
Welcome to the Quicksight Community!
I wonder if the use of two round functions is leading to this error. Have you tried testing out on a more broken-down scale? I would suggest running the inner calculation separately, then once you’ve confirmed the values are accurate, attach a round to that to avoid rounding an already rounded number.
Thank you.
Hello there! Thank you for the recommendation. I tried to apply your suggestion by breaking down the calculated field into three parts and testing each step separately.
raw_rate: {rate} / 365
raw_product: {raw_rate} * {amount}
final_calculcated_product: round({raw_product}, 5)
But so far here is the result:
I do think the problem now lies on the round function, as it wasn’t able to commit to the rounding off to 5 decimals. Cause it keeps going with 4. Is there another way we can work around on this?
Is there any help available for this? I know visual formatting is an option, but I can’t utilize it because I need to calculate the variance between the calculated and expected outputs for the reconciliation.
Hi @androidllaight,
Thank you for providing the additional information and screenshot, I see what you mean by the odd rounding that’s taking place through calculation. I tried re-creating a similar instance however I was not encountering outcomes that were this off. Side note, since it keeps adjusting the decimal to the 4th space when you set to 5, have you tested out if you use ‘6’ as the setting in the calc. field instead just to see what the outcome is?
But doubling back to your note about assigning the decimals through formatting, could you expand on why that would not be a viable option for your case? If you use ‘raw_product’ and set the formatting decimal to 5, does it not show the correct rounding either?
Hi @androidllaight — the behavior you’re seeing is actually documented and is a result of QuickSight’s default decimal handling. Per the AWS docs:
“The decimal data type supports values with up to four decimal places to the right of the decimal point. Values that have a higher scale than this are truncated to the fourth decimal place… when these values are displayed in data preparation or analyses, and when these values are imported into QuickSight.”
This is why your round(x, 5) keeps showing 4. The Decimal-fixed type is truncating before your formula’s result can display.
I would change the data type of the affected fields to Decimal-float instead of Decimal-fixed (assuming it isn’t the current config). The float type supports higher precision and isn’t subject to the 4-place truncation.
You can navigate to Edit Data Source > Affect Field > Data Type > Decimal (Float) to change this.
Alternative workaround if you can’t switch types for some reason: there’s a known trick of multiplying by ^1 to force a Double output type, e.g., (sum(cost^1) / sum(cost2^1)) * 10000. This is documented in this re:Post answer. Hacky, but worth a try if the above solution doesn’t give you what you need.
Once the field type is correct, your original round({rate} / 365 * {amount}, 5) should give you the 5-decimal precision you need for reconciliation.
