AWS QuickSight: Calculate Aggregate Percentage in Table

Question

In QuickSight I would like to calculate the aggregate percentage of one column against the aggregate total of another column.

Imagine the following data-set structure:

group|dim1|dim2|total|remaining

I would like to have a table Visualization with the following structure:

group-by-group|sum(total)|sum(remaining)|sum(remaining)/sum(total)

I would also like to dynamically filter via controls based on dim1 and dim2. This makes pre-calculating any percentages problematic. Is there a way to do this in QuickSight? I can visually represent it with a Stacked 100% Bar Chart, but I'd prefer to have this in-line with the rest of the data in a table.

Answer 1

The solution is to use aggregate functions in a calculated field. The resulting field well groups/values would be:

Group:

• group-by-group

Values:

• total aggregation=sum
• remaining aggregation=sum
• percent_remaining aggregation=custom

Where percent_remaining would be a calculated field with the following formula: sum({remaining})/sum({total})

The aggregate sum functions are calculated at visualization render time and take filters into account.