Use calculated field for fraction with different aggregation level in denominator than in numerator

I have a dataset which contains entries for outlets. Each entry has a field ‘outlet ID’, ‘outlet name’, ‘state’, and ‘product name’ (simplified). Each outlet can have 1 (if it offers none of the products) or several entries, one additional entry for each product (with a different ‘product name’) offered.

I want to create a bar chart which shows for each filtered product a fraction - the product coverage for each product, i.e., the number of outlets which offer a certain product (numerator) divided by total outlets (denominator). This data could also later be grouped, e.g., by states, or other filters could be applied to the outlets, e.g., only outlets are taken into consideration which offer at least one product out of a set of certain products.

How can I achieve this?

As I understand it, it is not possible to create a calculated field which computes a fraction where the aggregation level in the denominator is different from the numerator. That means, when in the field well the X axis contains the field ‘product name’ and the value contains the calculated field, that calculated field to be calculated as distinct_count({outlet ID}) / distinctCountOver({outlet ID}, [], PRE_AGG) is not possible.

I get this error message: “Mismatched aggregation. Custom aggregations can’t contain both aggregate “distinctCountOver” and non-aggregated fields “DISTINCTCOUNTOVER(“outlet ID”, PRE_AGG)”, in any combination.”

Any help is appreciated.

P.S.: I now added min() to the denominator. distinct_count({object ID}) / min(distinctCountOver({object ID}, [], PRE_FILTER)) doesn’t throw an error, not yet sure if it works correctly.

This is a full breakdown of LAC calculations and what is acceptable.

In regards to providing a fraction of the total, you can look into PRE_FILTERS as well, if you are filtering data but also want to show a denominator not filtered.

Don’t know if that’s what you’re looking for ^ but that’s how we do it.

1 Like

Actually, I think here I found the solution for a similar case:

Compute the fixed percent of total sales.

Calculated field name : fixedPercentOfTotal

Calculated field expression : sum(sales) / min(totalSalesBeforeFilters)

Note: totalSalesBeforeFilters is the same for every row of the unaggregated data. Since we want to use it post-aggregation, we are using the aggregation min on top of it. If all values are the same, max or avg aggregations can be used as well as it serves the same purpose.

Source: https://aws.amazon.com/jp/blogs/big-data/create-advanced-insights-using-level-aware-aggregations-in-amazon-quicksight/

1 Like