Using levelaware calculations in Amazon QuickSight
Applies to: Enterprise Edition and Standard Edition 
With Levelaware calculations (LAC) you can specify the level of granularity that you want to compute window functions or aggregate functions. There are two types of LAC functions: levelaware calculation  aggregate (LACA) functions, and levelaware calculation  window (LACW) functions.
Levelaware calculation  aggregate (LACA) functions
With LACA functions, you can specify at what level to group the computation. By adding one argument into an existing aggregate function (e.g. sum()
, max()
, count()
) you can define any groupby level that you want for the aggregation. The level added can be any dimension independent of the dimensions added to the visual. For example:
sum(measure,[group_field_A])
To use LACA functions, type them directly in the calculation editor by adding the intended aggregation levels as the second argument between brackets. Following is an example of an aggregate function and a LACA function, for comparison.

Aggregate function:
sum({sales})

LACA function:
sum({sales}, [{Country},{Product}])
The results are computed with the specified level in the brackets
[]
.
LACA functions are supported for the following aggregate functions:
LACA examples
You can do the following with LACA functions:

Run calculations that are independent of the levels in the visual. For example, if you have the following calculation, the sales numbers are aggregated only at the country level, but not across other dimensions (Region or Product) in the visual.
sum({Sales},[{Country}])

Run calculations for the dimensions that are not in the visual. For example, if you have the following function, you can calculate the average total country sales by region.
sum({Sales},[{Country}])
Though Country is not included in the visual, the LACA function first aggregates the sales at the Country level and then the visual level calculation generates the average number for each region. If the LACA function isn't used to specify the level, the average sales are calculated at the lowest granular level (the base level of the dataset) for each region (showing in the sales column).

Use LACA combined with other aggregate functions and LACW functions. There are two ways you can nest LACA functions with other functions.

You can write a nested syntax when you create a calculation. For example, the LACA function can be nested with a LACW function to calculate the total sales by country of each product's average price:
sum(avgOver({Sales},[{Product}],PRE_AGG),[{Country}])

When adding a LACA function into a visual, the calculation can be further nested with visuallevel aggregate functions that you selected in the fields well. For more information about changing the aggregation of fields in the visual, see Changing or adding aggregation to a field by using a field well.

LACA limitations
The following limitations apply to LACA functions:

LACA functions are supported for all additive and nonadditive aggregate functions, such as
sum()
,count()
, andpercentile()
. LACA functions are not supported for conditional aggregate functions that end with "if", such assumif()
andcountif()
, nor for period aggregate functions that start with "periodToDate", such asperiodToDateSum()
andperiodToDateMax()
. 
Rowlevel and columnlevel totals are not currently supported for for LACA functions in tables and pivot tables. When you add rowlevel or columnlevel totals to the chart, the total number will show as blank. Other nonLAC dimensions are not impacted.

Nested LACA functions are not currently supported. A limited capability of LACA functions nested with regular aggregate functions and LACW functions are supported.
For example, the following functions are valid:

Aggregation(LACA())
. For example:max(sum({sales}, [{country}]))

LACA(LACW())
. For example:sum(sumOver({Sales},[{Product}],PRE_AGG), [{Country}])
The following functions are not valid:

LACA(Aggregation())
. For example:sum(max({sales}), [{country}])

LACA(LACA())
. For example:sum(max({sales}, [{country}]),[category])

LACW(LACA())
. For example:sumOver(sum({Sales},[{Product}]),[{Country}],PRE_AGG)

Levelaware calculation  window (LACW) functions
With LACW functions, you can specify the window or partition to compute the calculation. LACW functions are a group of window functions, such as sumover()
, (maxover)
, denseRank
, that you can run at the prefilter or preaggregate level. For example: sumOver(measure,[partition_field_A],pre_agg)
.
LACW functions used to be called level aware aggregations (LAA).
LACW functions help you to answer the following types of questions:

How many of my customers made only 1 purchase order? Or 10? Or 50? We want the visual to use the count as a dimension rather than a metric in the visual.

What are the total sales per market segment for customers whose lifetime spend is greater than $100,000? The visual should only show the market segment and the total sales for each.

How much is the contribution of each industry to the entire company's profit (percent of total)? We want to be able to filter the visual to show some of the industries, and how they contribute to the total sales for the displayed industries. However, we also want to see each industry's percent of total sales for the entire company (including industries that are filtered out).

What are the total sales in each category as compared to the industry average? The industry average should include all of the categories, even after filtering.

How are my customers grouped into cumulative spending ranges? We want to use the grouping as a dimension rather than a metric.
For more complex questions, you can inject a calculation or filter before QuickSight gets to a specific point in its evaluation of your settings. To directly influence your results, you add a calculation level keyword to a table calculation. For more information on how QuickSight evaluates queries, see Order of evaluation in Amazon QuickSight.
The following calculation levels are supported for LACW functions:

PRE_FILTER
– Before applying filters from the analysis, QuickSight evaluates prefilter calculations. Then it applies any filters that are configured on these prefilter calculations. 
PRE_AGG
– Before computing displaylevel aggregations, QuickSight performs preaggregate calculations. Then it applies any filters that are configured on these preaggregate calculations. This work happens before applying top and bottom N filters.
You can use the PRE_FILTER
or PRE_AGG
keyword as a parameter
in the following table calculation functions. When you specify a calculation level, you use an unaggregated measure in the function. For example, you can use countOver({ORDER ID}, [{Customer ID}], PRE_AGG)
. By using PRE_AGG
, you specify that the countOver
executes at the preaggregate level.
By default, the first parameter for each function must be an aggregated measure. If
you use either PRE_FILTER
or PRE_AGG
, you use a nonaggregated
measure for the first parameter.
For LACW functions, the visual aggregation defaults to MIN
to
eliminate duplicates. To change the aggregation, open the field's context (rightclick)
menu, and then choose a different aggregation.
For examples of when and how to use LACW functions in real life scenarios,
see the following post in the AWS Big Data Blog: Create advanced insights using Level Aware Aggregations in Amazon
QuickSight.