I have a use case to generate paginated reports on a monthly basis to report our monthly performance against monthly goals. I have attached a screenshot of randomized data below for some context on formatting. Our goals and actuals tend to be percentages and we measure the variance to goal with basis points, for ease of reading when comparing percentages. These varying data types make it a little difficult to use pivot tables as a solution. Our paginated reports include multiple visuals that we’ve built in QuickSight, but we’d like to be able to include these customized tables that may not have any aggregation or calculation. Is it possible to construct these customized tables in QuickSight? Sort of how you would with formulas in Excel? We considering using custom visuals and uploading screenshots of the data stored in S3, but all of the underlying data is available in the datasets, so we’d like to be able to generate these tables in QuickSight.
Hi @calvinkim,
You can achieve your desired outcome within QuickSight using a combination of calculated fields and pivot table.
In the example below, I used the following dataset (please adjust my suggestions to the structure of your own dataset):
Category,Year,Month,Actual,Target
Category A,2021,Jan,0.7113,0
Category A,2022,Jan,0.2074,0
Category A,2023,Jan,0.1347,0.2313
Category A,2021,Feb,0.7298,0
Category A,2022,Feb,0.9151,0
Category A,2023,Feb,0.0233,0.3322
Category B,2021,Jan,0.7113,0
Category B,2022,Jan,0.3654,0
Category B,2023,Jan,0.1223,0.5647
Category B,2021,Feb,0.7298,0
Category B,2022,Feb,0.3931,0
Category B,2023,Feb,0.1508,0.2719
To transform this dataset into the desired tabular view within QuickSight, you need to define calculated fields for the individual measures that you want to display for each category, i.e, 2023 Target, 2023 Actual, vs Target (basis points), 2022 Actual, 2021 Actual.
In my example, the expressions of the calculated fields would look as follows:
2023 Target:
ifelse(Year=2023,Target,0)
2023 Actual
ifelse(Year=2023,Actual,0)
vs Target (basis points)
ifelse(Year=2023,Actual-Target,0)*10000
2022 Actual
ifelse(Year=2022,Actual,0)
2021 Actual
ifelse(Year=2021,Actual,0)
Now, you can add a pivot table to your report and assign the Category to the rows section in the field wells, the Month to the columns section in the field wells, and all the calculated fields into the Values section in the field wells. Afterwards, you have to change the values from Column to Row (within the values section in the field wells) and adjust the formatting preference for each individual value (number vs percent, etc.). The result should then look as follows:
Did this answer your question? If so, please help the community out by marking this answer as “Solution!”. Thanks!
Hi Thomas. Thanks for the quick response! I tested and this is exactly what I’m looking for. Marking this answer as the solution.
A formatting nice-to-have would be to change the Categories from being presented as the its own “column” in the pivot table and being presented as row headings (see example below). I understand this doesn’t follow standard pivot table structure, but is this possible in QuickSight?
As of now, you can choose between two Layout options for pivot tables (“Hierarchy” and “Tabular”). The “Hierarchy” layout allows you to change the pivot table’s layout in the way you were asking for. However, with values shown as rows vs. columns, you would still get the value names next to each category item. If you would be open to switch the values to be columns instead of rows and move the Month into rows, you would get the following result:
