Mirroring Total Function from Tableau to QuickSight

Hello there,

I have opened a TT internally, but in tandem decided to ask the community. I am trying to migrate a calculation from Tableau to QuickSight and avoid leveraging SQL to get the result rather use the Analysis level calculation. I am attempting to mirror this in SQL so far I have tried multiple iterations of sumOver, maxOver, minOver, etc. The below part is the calculation needed to work to receive the denominator in my larger calculation. I essentially need to mirror the values for the rows in every column in my pivots so far working with QS and others we have not yielded the correct result.

Tableau
total(sum(case [Display Name Adj] when ‘Revenue’ then [Amt Usd (copy)] else 0 end))

Hi @pagekevi, to replicate the Tableau calculation in Amazon QuickSight 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 QuickSight:

  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 QuickSight 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 QuickSight Community!

Also, check out the list of QuickSight 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 - QuickSight Arena)

Hey @Xclipse ,
sumOver({RevenueTotal}, , PRE_AGG) is not allowing it to go through an throws an error for the PRE_AGG or PRE_FILTER. Feel free to slack me username is my @.

Please try the syntax from here: sumOver - Amazon QuickSight

yea I have been working with that and its not functioning as expected have an internal TT and call with calc team here in 30 mins.

Great! Please help the community out by providing the solution from support. Thanks.

1 Like

Found a resolution to this issue.

I needed to assign a proper partition on the columns we are using in the vis in the calculation. {Column_selected_for_pivot} will need the curly brackets but this is your column that you are selecting and aggregating from in the UI.

Revenue Base:
ifelse({display_name} = ‘Revenue’, {Amt Usd (copy)}, 0)

tot_rev_agg:
sumOver(sum({Revenue Base}), [{Column_selected_for_pivot}], POST_AGG_FILTER)

var % final:
sum({Amt Usd (copy)}) / tot_rev_agg

1 Like