Mirroring Total Function from Tableau to Quick Sight

Q&A
, ,
2

Hi @pagekevi, to replicate the Tableau calculation in Amazon Quick Sight at the Analysis level, you can use a combination of sumIf and window functions to achieve the desired result.

If the goal is to calculate the total sum of the “Amt Usd (copy)” where the “Display Name Adj” is ‘Revenue’ and then use that value as a denominator in your larger calculation.

Here’s an approach in Quick Sight:

  1. **Create a Calculated Field for Revenue Amount:**First, create a calculated field to filter and sum the “Amt Usd (copy)” where “Display Name Adj” is ‘Revenue’.

Pseudocode (Syntax may vary)

sumIf({Amt Usd (copy)}, {Display Name Adj} = 'Revenue')

Name this calculated field RevenueTotal.

  1. **Create a Window Function to Mirror the Values:**Use a window function to apply the calculated RevenueTotal value across all rows. This will ensure that each row in your dataset has access to the total revenue amount.

Pseudocode (Syntax may vary)

sumOver({RevenueTotal}, [], PRE_AGG)

Name this calculated field RevenueTotalOver.

  1. **Use the Calculated Field in Your Larger Calculation:**Now, you can use RevenueTotalOver as the denominator in your larger calculation.For example, if you want to create a calculated field for a percentage calculation, you can use:

Pseudocode (Syntax may vary)

{Amt Usd (copy)} / {RevenueTotalOver}

Name this calculated field PercentageOfRevenue.

Putting It All Together:

  1. Create RevenueTotal Calculated Field:

Pseudocode (Syntax may vary)

RevenueTotal = sumIf({Amt Usd (copy)}, {Display Name Adj} = 'Revenue')
  1. Create RevenueTotalOver Calculated Field:

Pseudocode (Syntax may vary)

RevenueTotalOver = sumOver({RevenueTotal}, [], PRE_AGG)
  1. Create Your Larger Calculation Using RevenueTotalOver:

Pseudocode (Syntax may vary)

PercentageOfRevenue = {Amt Usd (copy)} / {RevenueTotalOver}

Explanation:

  • sumIf: Filters and sums the “Amt Usd (copy)” where “Display Name Adj” is ‘Revenue’.
  • sumOver: Applies the RevenueTotal value across all rows to mirror the values.
  • Percentage Calculation: Uses the mirrored value RevenueTotalOver as the denominator in your percentage calculation.

By following these steps, you should be able to replicate the Tableau calculation in Amazon Quick Sight without needing to resort to SQL.

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 Quick Sight Community!

Also, check out the list of Quick Sight Functions and this forum:

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 - Quick Sight Arena)