Subtract from Sum

Question & Answer
,
1

Hi, my team calculates transactions totals via QuickSight. however we have listed “exits” that would need to be subtracted from the automated sum populated by the dashboard. Is there a way to do this without having to create two visuals?

2

Hi @dherasme, welcome to the QuickSight Community. You maybe able to handle this situation in QuickSight without creating two visuals by using calculated fields. Here’s how you may be able to do it:

  1. Create a Calculated Field for Exits: First, you’ll need to create a calculated field that represents the exits. Assuming you have a column in your dataset that marks a transaction as an “exit,” you can use a calculated field to sum up these exits.
  • Go to your dataset.
  • Click on Add and then Add Calculated Field.
  • Enter a name for your calculated field, such as Total Exits.
  • Use a formula to sum the exits, for example:

Pseudocode (Syntax may vary)

sumIf(transaction_amount, transaction_type = 'exit')
  1. Create a Calculated Field for Net Transactions: Next, create another calculated field to represent the net transaction amount by subtracting the exits from the total transactions.
  • Go to your dataset.
  • Click on Add and then Add Calculated Field.
  • Enter a name for your calculated field, such as Net Transactions.
  • Use a formula to calculate the net amount, for example:

Pseudocode (Syntax may vary)

sum(transaction_amount) - sumIf(transaction_amount, transaction_type = 'exit')

Alternatively, if you already have a calculated field for total exits, you can use:

Pseudocode (Syntax may vary)

sum(transaction_amount) - {Total Exits}
  1. Add the Net Transactions Field to Your Visual: Finally, add this Net Transactions field to your visual.
  • Go to your analysis.
  • Select the visual where you want to display the net transactions.
  • Add the Net Transactions calculated field to the appropriate section of your visual (e.g., as the value in a KPI, as a measure in a bar chart, etc.).

If these steps do not resolve your issue, try using custom SQL or pre-processing at the data source.

Also, try using the Amazon Q Assistant on the functions documentation page.

Amazon Q

Build calculations with Generative BI

Another approach is using Generative BI, you can use natural language prompts to create calculated fields within QuickSight, as shown in the following image. For more information about calculated fields in analyses, see Adding calculated fields.
Amazon Q Build Calculation

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)

1 Like