Calculate remaining percentage

Question & Answer
,
1

I would like to calculate a remaining percentage that makes up a 100%.

Our data consists of value A that represents a number of events completed and value B which represents the number of events that are expected to be completed. I have created a calculated field which calculates the percentage of events completed, however I need to present the percentage of events not completed i.e. 100% - (Value A/Value B).

I have tried 1 - (Value A/Value B), however it is giving me numbers like the below.

image

Thanks for the help!

2

@Ivy
Can you please clarify if the first column in the image is Value A and second one is Value B? Can you also please share the calculated field you used for the percentage of events completed.

if you have computed percent of events computed, then you can use
1- percent of events completed unless there is no third category.

3

image

Value A is exception_count which is the first column and Value B is maximum_exceptions which is the second column. If I calculate the actual percentage of Value A it is no problem, and calculates it correctly, but calculating the remaining percentage gives me a result like in the initial photo.

Hope this makes sense.

Thanks for the help.

4

This might work at the most granular level. However, if you using this in table or any other visual where aggregation at higher level happens, please use aggregation in the formula like below:

1- (sum(exception_count)/ sum(maximum_exceptions)).

Please let me know if this fixes the problem for you. Also, screen shot of the field well of the visual will help

5

Hi, @Ivy. We hope Vetri’s solution worked for you. Let us know if this is resolved. (And if it is, we’d love it if you could click/tap “Solution" under his answer.) Thanks! And, welcome to the QuickSight Community. :slight_smile:

6

Unfortunately, it didn’t fix it. The percentages all come up as 95.45% which is impossible.

These are the field wells

image
image1270Ă—379 21.5 KB

7

This is the result.

image
image964Ă—592 19.6 KB

8

Hi @Ivy

My guess is that this might be due to the Column that you have added to the pivot as its doing another group by based on the columns and impacting the calc of the %. If i do a plain example, with no columns - percentages are calculated accurately with the formula you are using.

image
image540Ă—610 9.59 KB

Can you please try removing the column and seeing its impact?

thanks!
Ramon

1 Like
9

Hi, @Ivy. We hope Ramon’s solution worked for you. Let us know if this is resolved. (And if it is, we’d love it if you could mark his answer as a “Solution.”) Thanks!

10

Hi, @Ivy . Did @Ramon_Lopez solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!