Rounding Issue

Q&A
,
1

Hello,

I have a calculated field that divides two calculated measures (both created using sumIf()) to produce a percentage value.

For example:

  • Measure A = 6486.15

  • Measure B = 2275471.71

The expected calculation is:

6486.15 / 2275471.71 * 100 = 0.2850463915

However, QuickSight displays 0.280 instead of the expected 0.285 (formatted to three decimal places).

I’ve verified that the underlying values being used in the calculation are correct, so I am trying to understand whether QuickSight is applying rounding or additional aggregation somewhere in the calculation process.

If more information is required, please let me know

Thanks.

2

Hi @williamsonc,

I believe the fix is to make sure the division happens on the aggregated values inside a single calculated field, so the ratio is computed after both sides are summed at the visual’s grain. In other words, structure it as something like sum(ifelse(condition, valueA, 0)) / sum(ifelse(condition, valueB, 0)) * 100 in one field, rather than dividing two separate measures that were each already aggregated.

If your two sumIf measures are pre-aggregated fields and you then divide one by the other, Quick Sight can re-aggregate the intermediate result and shift the number. Collapsing it into one expression where both the numerator and denominator are summed together avoids that. It is also worth guarding the denominator so you don’t get blanks on zero, like ifelse(sum(…B…) = 0, 0, sum(…A…) / sum(…B…) * 100).

Here is the link to the official AWS resource I read through. I would definitely recommend giving it a read too!

Hope this helps and feel free to reply with additional questions or clarifications!

3

Hi @WLS-Cesar ,

Thanks for your response.

I have tried following what you mentioned, following this logic - sum(ifelse(condition, valueA, 0)) / sum(ifelse(condition, valueB, 0)). But it seems that I run into the same issue (made up values including my previous example)

image
image467×159 4.81 KB

I’ve read over the post you shared but I’m unable to see anything else that may help me here. Any help you can give me on this is much appreciated. Thanks!

4

Hi @williamsonc,

Thanks for trying that and sharing the screenshot. I wanted to double check something from your original post, when you said “(formatted to three decimal places)” did you mean you wanted formatted to 3 decimals place OR you already changed it to 3 decimal places through the Format Data tab?

If you haven’t done so already, I would go to your calc field and click on the three dots, hover over Format: 1,234.57, then click More formatting options, and there you can choose how many decimals places you want (set to 3).

image
image394×311 22.6 KB

image
image1202×776 57.8 KB

If you have already done so and the decimals are still not displaying as you want, a few more things that might help narrow this down, could you check what happens if you add Measure A and Measure B as separate columns in a table visual alongside the percentage field? That would confirm whether the values quick sight is actually using in the division match what you expect (6486.15 and 2275471.71), or if the visual’s aggregation grain is changing them. It’s also worth checking whether your dataset is in SPICE or direct query, since SPICE stores numbers as doubles which can occasionally introduce floating point drift on large values.

If the individual measures show the correct values in the table but the division still shows 0.280, that would point to a precision issue in how quick sight evaluates the expression. In that case, try wrapping the result in round() explicitly, like round(sum(…A…) / sum(…B…) * 100, 3), to see if forcing the rounding at the calculation level gives you the correct 0.285.

Hope this helps!

5

Hi,

Specifically for my example I wanted 2dp which should be 0.29 instead of 0.28 as when it is 3dp it should be 0.285 but QuickSight is showing 0.280 when I set it to 3dp

You can see in the example below that I am getting 0.280% when for this one I should be getting 0.286% (7491.899/2616180.169 = 0.0028614910038301). I’ve also tried wrapping the % calculation in round() but this doesn’t give me the expected result. My dataset is in SPICE so perhaps it is because of this? Regardless it may be concerning that the values aren’t being rounded correctly.

Perhaps I am missing something but regardless thank you for your help so far!

image