Calculations in AWS QuickSight
When building visualizations, is important to understand how calculations are defined, how their results are displayed and how they will react when applying some filters or interacting with the rest of the data. This will help us to build better visualizations and get the value of our data in the best possible way.
In QuickSight, calculations can be defined in the analysis layer or dataset layer.
When defined in the analysis layer, the calculations are always evaluated on analysis/dashboard load.
If pushed onto the dataset layer, row-level calculations will be precomputed and results will be stored (i.e. if the dataset is in SPICE mode).
Pushing aggregate calculations to the dataset layer helps to centrally store the calculation, making it available for ready use for all dataset users. However, this doesn’t yield any additional performance benefits as aggregate calculations are always evaluated in the context of the visual it is used in.
Understanding the order of evaluation of filters, calculations and aggregations are important.
We will explore this sequence further with some examples. We will use a sales dataset that contains information about orders. We will focus on Segment, Industry, Sales and Profit fields and we will build a few measures to illustrate the examples.
We will use additive measures, measures that can be summarized or aggregated over all the dimensions, and non-additive measures, measures that can’t be summarised or aggregated over all the dimensions.
Cost is Sales minus Profit and it is an additive measure. We will create two calculated fields for Cost (Row and Aggregate) and compare the results.
Row Cost: {Sales}-{Profit}
Aggregate Cost: sum({Sales})-sum({Profit})We will create a table (Row vs Aggregate Calcs) to compare the calculations, adding the fields Segment, Profit, Sales, Row Cost and Aggregate Cost.
Since Cost is additive, both Row and Aggregate level calculations give the same result. In such cases, use the Row level version and include it in the dataset layer to have the row level calculation done ahead of time along with SPICE dataset refresh.
Profit Margin is the Profit divided by Sales and it is a non-additive measure. We will create two calculated fields for Profit Margin (Row and Aggregate), add them to the previous table and compare the results. This will help to get a clear understanding of how such calculations work.
Row Profit Margin (Incorrect): {Profit}/{Sales}
Aggregate Profit Margin: sum({Profit})/sum({Sales})
Row Profit Margin is a calculation at the data row level. Thereafter, it is getting summed up just like a regular aggregate measure and hence ends up being incorrect at the Segment field level and overall total levels. We can see that the aggregation mode against this calculation in the field well is SUM. The correct version gets the sum of profit and sum of sales for each Segment and then does the division. For the total row, it uses the total profit and total sales. Hence, we get the expected results. See that the aggregation mode is CUSTOM for this calculation. So, build calculations for non-additive KPIs using aggregated measures.
We will create now some Level-Aware Aggregate calculations.
Overall Sales [PRE_FILTER]: sumOver(Sales,[],PRE_FILTER)
Overall Sales [PRE_AGG]: sumOver(Sales,[],PRE_AGG)
Overall Sales [POST_AGG_FILTER]: sumOver(sum(Sales),[],POST_AGG_FILTER)And we will create a table (Level Aware Aggregates) to add them with the Industry and Sales fields.
At this point, all three calculated fields are showing the overall sales total across the full dataset (i.e. same value as total of Sales column in this view).
We will add a filter for the Industry field and deselect Misc value.
We can see that PRE_FILTER value stayed the same (as they get evaluated ahead of analysis filters) whereas PRE_AGG, POST_AGG_FILTER and table total for Sales column dropped down to a new value excluding Misc category.
We will now add a Top/Bottom filter for the Industry field to show only the top 5 Industries by Sales.
We can see that PRE_FILTER value and PRE_AGG values stayed unchanged from their prior values (as they get evaluated ahead of Top/Bottom filters) whereas POST_AGG_FILTER and table total for Sales column dropped down to new value including only Sales for Top 5 Industries.
When building calculations for additive measures use row-level measures in the dataset layer and for non-additive measures use aggregated measures.
It’s also important to understand the order of evaluation in AWS QuickSight, how the filters will interact with our data and what information we want to show in each case, so we will be able to build understandable dashboards with high-quality information.
Source: AWS
About the author:
David Hernandez is a Senior Data Engineer here at Version 1.
