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!