Mastering Calculations, Filters, and Aggregations in Amazon QuickSight

Creating effective visualizations in Amazon QuickSight requires a deep understanding of how calculations are defined, displayed, and interact with filters and data. This knowledge is essential for building high-performance dashboards that maximize the value of your data.

Types of Calculations in QuickSight

QuickSight supports calculations at two levels:

• Analysis Layer: Calculations are evaluated every time the analysis or dashboard is loaded.

• Dataset Layer: Row-level calculations are precomputed and stored, especially for SPICE datasets, making them reusable across multiple analyses. While centralizing aggregate calculations at the dataset layer can standardize metrics, they do not offer performance benefits since they are always recalculated in the context of the visual.

Understanding the order of evaluation for filters, calculations, and aggregations is crucial to avoid errors and achieve the desired outcomes.

Evaluation Order in QuickSight

Let's explore this concept using a sales dataset with fields like Segment, Industry, Sales, and Profit. We'll demonstrate how calculations behave using additive and non-additive measures.

1. PRE_FILTER Level (Global View - Before Filters)

• What happens here?

• Calculations and filters at this level are evaluated before any analysis or visual-specific filters are applied.

• This means the results of calculations are based on the entire dataset, without considering any filtering or interaction.

2. PRE_AGG Level (Analysis Filters Applied)

• What happens here?

• Calculations and filters at this level are evaluated after the analysis-level filters are applied.

• However, Top/Bottom N filters or visual-specific filters are not yet applied at this stage.

• The results are based on the filtered dataset up to this point.

3. Display Level (POST_AGG_FILTER) (Final Visual Output)

• What happens here?

• Calculations and filters at this level are evaluated after all filters, including Top/Bottom N filters and visual-specific filters.

• It includes visual-specific aggregations, table calculations, subtotals, and totals.

How to Think About It

• PRE_FILTER:
  Global metrics that ignore any filters (entire dataset).

• PRE_AGG:
  Respects analysis filters but ignores visual-specific or Top/Bottom N filters.

• Display Level (POST_AGG_FILTER):
  Fully respects all filters, rankings, and visual aggregations.

Additive Measures

These are metrics that can be summed or aggregated across all dimensions without losing meaning. For example, Sales and Profit are additive because their totals are meaningful when summed across rows or groups.

Example: Cost Calculation

• Row Cost: {Sales} - {Profit}

• Aggregate Cost: sum({Sales}) - sum({Profit})

When we create a table with Segment, Profit, Sales, Row Cost, and Aggregate Cost, both Row and Aggregate calculations produce the same results since Cost is additive.

Best Practice: Use row-level calculations and include them in the dataset layer for precomputation during SPICE dataset refresh.

Non-Additive Measures

These metrics cannot be directly summed across dimensions as their aggregation depends on context. For example, Profit Margin (Profit ÷ Sales) is non-additive because it requires aggregation (e.g., total profit ÷ total sales) rather than summing individual margins.

Example: Profit Margin Calculation

• Row Profit Margin (Incorrect): {Profit} / {Sales}

• Aggregate Profit Margin (Correct): sum({Profit}) / sum({Sales})

Adding these to the table alongside Segment reveals that the Row Profit Margin is incorrect because it aggregates row-level calculations (SUM) at higher levels, distorting the results. The Aggregate Profit Margin calculates sums for Profit and Sales first and then performs the division, ensuring accurate results.

Best Practice: For non-additive KPIs, build calculations using aggregated measures.

Level-Aware Aggregations

QuickSight provides Level-Aware Aggregations (LAAs) for advanced calculations, enabling control over how filters and aggregations interact.

Real World use case of Level-Aware Aggregations:

1. PRE_FILTER (Global Calculations)

• Use Case: You want to calculate the overall sales for the entire company, regardless of any filters applied to the dashboard.

• Example Scenario:

• A sales manager wants to compare the total company sales against filtered sales for specific regions or products.

• Calculation: sumOver(Sales, [], PRE_FILTER) ensures the total sales are always calculated for the full dataset.

• Outcome: The value remains constant, even if users filter the dashboard by region or product.

2. PRE_AGG (Filtered Aggregations)

• Use Case: You want to calculate total sales for the selected regions, considering the filters applied at the dashboard level but ignoring Top/Bottom N filters.

• Example Scenario:

• A regional manager wants to analyze sales for the selected region (e.g., East Coast) but does not need ranking filters applied yet.

• Calculation: sumOver(Sales, [], PRE_AGG) respects filters like “Region = East Coast” but ignores ranking filters such as “Top 5 Products by Sales.”

• Outcome: The value adjusts based on dashboard filters but is not affected by visual-specific filters.

3. POST_AGG_FILTER (Visual-Specific Calculations)

• Use Case: You want to calculate sales for the Top 5 products by revenue.

• Example Scenario:

• A product manager wants to see total sales only for the top-performing products within the filtered category.

• Calculation: sumOver(sum(Sales), [], POST_AGG_FILTER) considers all filters, including Top/Bottom N filters applied to the visual.

• Outcome: The value reflects only the top 5 products based on the ranking filter.

Example: Overall Sales Calculations

We create three calculated fields:

• Overall Sales [PRE_FILTER]: sumOver(Sales, [], PRE_FILTER)

• Overall Sales [PRE_AGG]: sumOver(Sales, [], PRE_AGG)

• Overall Sales [POSTAGGFILTER]: sumOver(sum(Sales), [], POST_AGG_FILTER)

Adding these calculations to a table alongside Industry and Sales produces the total sales across the entire dataset.

Initially, all three calculated fields display the total overall sales for the entire dataset. This matches the total of the Sales column in the table view.

Next, we apply an Industry filter to exclude the "Misc" category.

The PRE_FILTER calculation remains unchanged, as it is evaluated before analysis-level filters are applied.

The PRE_AGG, POST_AGG_FILTER, and the table total for the Sales column adjust to reflect the new total, excluding the "Misc" category.

We then apply a Top/Bottom filter to display only the top 5 industries by sales.

‍

Both PRE_FILTER and PRE_AGG calculations remain unaffected, as they are evaluated before the Top/Bottom filter is applied.

The POST_AGG_FILTER and the table total for the Sales column update to include only the sales from the top 5 industries.

Common Mistakes in QuickSight

1. Incorrect Use of Row-Level Calculations for Non-Additive Measures: Applying row-level calculations to non-additive measures like Profit Margin can lead to incorrect results. Always use aggregated calculations for such measures.

2. Ignoring Evaluation Order: Misunderstanding the order in which filters, calculations, and aggregations are applied can result in unexpected outcomes. Ensure you are aware of how QuickSight processes these elements.

3. Overloading SPICE Capacity: SPICE has limits on data capacity. Overloading it with too many calculations or large datasets can degrade performance. Optimize your datasets and calculations to stay within SPICE limits.

4. Not Leveraging Level-Aware Aggregations: Failing to use LAAs can limit the flexibility and accuracy of your dashboards. Understand and apply LAAs to control how filters and aggregations interact.

5. Neglecting Data Refresh Schedules: SPICE datasets need regular refreshing to stay up-to-date. Neglecting this can lead to outdated insights. Set up a refresh schedule that aligns with your data update frequency.

Key Takeaways

• Row vs. Aggregate Calculations: Use row-level calculations for additive measures and precompute them in SPICE datasets. For non-additive measures, rely on aggregated calculations for accurate results.

• Level-Aware Aggregations: Understand how filters and aggregations interact (PREFILTER, PREAGG, POSTAGGFILTER) to design dynamic and accurate dashboards.

Best Practices

• Leverage dataset-layer calculations to standardize reusable metrics.

• Use analysis-layer calculations for dynamic, on-the-fly metrics.

By mastering these concepts and avoiding common mistakes, you can build efficient, impactful dashboards that deliver accurate insights. Explore these techniques in your next QuickSight project to unlock the full potential of your data.

Happy Visualizing! 🚀