What Are Partitions in QuickSight Level-Aware Aggregations?

Whether you're brand new to AWS QuickSight or just need the refresher, here is a quick guide to using partitions in QuickSight.

What Is a Partition?

Partitions are not a new concept in the coding world, nor are they unique to QuickSight. Just as a physical curtain or partition might separate a room into individual spaces, partitions in coding allow you to segment data into groups, and then work with and apply calculations at the level of those groups rather than at the level of the dataset as a whole.

Partitioning in QuickSight

Here's a very simple example of a level-aware calculation for a calculated field in QuickSight utilizing partitions (here is some great additional AWS documentation on level-aware aggregations), a field which we'll call TotalBilledAmountPerCustomer:

sumOver({Billed Amount},[{Customer ID}],PRE_FILTER)

The partition in this case is the Customer ID field. In other words, this calculation groups together and adds all the billed amounts for each customer, and does so before any filters are applied to or executed in the visual. The result:

The individual billed amounts have not been affected, but we now have the total of all of those amounts at the customer level. Notice that those totals appear at each line for the customer (this is important when it comes to looking at averages later, but more on that another time).

We'll discuss the use cases of these level-aware aggregations in another blog, but for now let's focus on the partitions.

Multiple Partitions

To take things a step further, you can partition by more than one field. For example, a field such as TotalBilledAmountPerCustomerPerYear:

sumOver({Billed Amount},[{Customer ID},{Year}],PRE_FILTER)

We've partitioned our data by both Customer ID and the Year. Keep in mind that QuickSight addresses partitions in the order in which they are given. So it groups the data by Customer ID, and then breaks that group into smaller groups by Year. To continue to use our curtain example, it's like splitting a large conference room into sections and then adding more curtains or dividers to make meeting rooms within each conference room.

The result looks like this:

We still have our individual billed amounts, the total billed amount per customer all the way down, but now we also have a breakdown of those totals for that customer for each year.

Why go that extra step? While there are nuances to make a case like this more accurate, lets say you wanted to see the average billed amounts for a customer for a year. If we just attempted to put the Year in the Group By field well and the Billed Amount (Average) in the Values field well, we would end up with the average billed amount for all customers for the given year.

Likewise, substituting the Billed Amount (Average) for the TotalBilledAmountPerCustomer (Average) field yields the same value every year because it doesn't change as long as the Customer ID remains the same.

However, if we partition the data by Customer and then by Year, we can use the result to see the averages we desire.

The following image illustrates each of the three situations described above.

And that is a very brief overview to partitions in QuickSight.

Thanks for taking the time to read; we hope it was helpful in some way! As always, feel free to reach out to us with questions or requests for individual or group QuickSight live training sessions.