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.
Our data and analytics expertise shows that applying the right calculation layer and level-aware aggregations in the correct order is the key to maximising data value and delivering trustworthy visualisations. Here's how you can build high-performing Amazon QuickSight dashboards.
#Types of Calculations in QuickSight
QuickSight calculations can be created at either the dataset or the analysis layer.
- 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.
Misusing these layers leads to duplicate logic, performance issues, or mismatched totals.
Learn how QuickSight processes filters and calculations
Very important question: What is the evaluation order of filters and calculations in QuickSight?
Amazon QuickSight applies filters and calculations in a fixed three-step order: PRE_FILTER, PRE_AGG, and POST_AGG_FILTER, each affecting your metrics differently.
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)
- 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)
- 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)
- 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.
Implement Level-Aware Aggregations for advanced control
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.
Avoid these common QuickSight mistakes
- 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.
- 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.
- 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.
- 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.
- 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.
#Build accurate dashboards with Amazon QuickSight and Armakuni
As an AWS Premier Partner with the Data & Analytics Consulting Competency, we bring deep experience in building scalable, insight-driven solutions on QuickSight. We've supported organisations across industries to move from inconsistent dashboards to trusted, well-structured reporting layers. At Armakuni, we help teams apply these principles with clarity and consistency, so their dashboards not only perform well, but also reflect the right data every time.
