Mirroring Total Function from Tableau to QuickSight

[Xclipse]

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.

2. **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.

3. **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')

2. Create RevenueTotalOver Calculated Field:

Pseudocode (Syntax may vary)

RevenueTotalOver = sumOver({RevenueTotal}, [], PRE_AGG)

3. 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:

Functions by category - Amazon QuickSight

Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you wan…

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)