How to build a calculation to calculate =(Column A+Column B)/Column B

[llan]

Hi, I just want to add a calculated field to get the outcome of an excel formulation: = (Column A + Column B)/Column B. Can you please help?

I am a new user of quicksight and have no coding background. I am struggled to build calculated field. Is there any site that captures how to convert excel formulation to quicksight calculation? I have tried to use function selection and learn more and Amazon Q Build Calculation function. Nothing works for even very simple excel calculation. Would really appreciate an excel formula converter to quicksight calculation.

[Brett]

Hi @llan,
When building calculated fields in QuickSight, it’s more about utilizing field names instead of columns and making sure to utilize the correct function. Here’s a great article that lists out the QuickSight functions by category:

Functions by category - Amazon QuickSight

In regards to the calculation you’re trying to perform, I would suggest utilizing a sum or sumOver function depending on how you’d like to aggregate.

Additionally, here’s one more article that really helped me understand different use cases on using level aware calculations:

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

Let us know if you have any additional questions

[llan]

Thank you. Tried SUM/SUMOVER, it does not work for me. I am sure I may have missed something.

Could you please provide exact calculation based on the image below? So I can use it to create column D in Quicksight as a calculated field. Thanks.

[Brett]

Hi @llan,
What is the name of your field that has the ‘sales person’ values? You’ll want to make sure and aggregate your calculations by that field. It may look something like:

(sum({FY 25 Revenue}, [{Sales Person Field}])-sum({FY24 Revenue}, [{Sales Person Field}]))/sum({FY24 Revenue}, [{Sales Person Field}])

Then you can change the ‘show as’ section to ‘Percent’.

Let me know if you have any additional questions