How to create & use aggregated figures in a table

Question and answer
,
#1

This is a problem I’ve run into a couple of times at work lately when re-creating Excel ‘dashboards’ in QuickSight and I’m really hoping there’s a solution that doesn’t involve creating multiple datasets. My example data is below.

Budget Spend Dept
200 135 a
300 304 a
250 124 b
340 436 b
127 127 c
237 219 c

Basically, I have two figures, budget and spend, for different departments, and multiple rows of each. I want to create a table/pivot table that sums these per department and adds in columns for the difference between them, the percentage difference between them, and a RAG rating that depends on the value of the percentage difference. So an expected output similar to the below

Budget Spend Dept Diff Perc_diff RAG
500 439 a -61 -14% [green]
590 560 b -30 -5% [amber]
364 346 c -18 -5% [amber]

How do I create this in QuickSight?

The two solutions I have looked at so far are table calculations, and level-aware calculations. But for the former, the ‘Calculate as’ options don’t seem to allow me to do this, and seemingly wouldn’t allow me to use the Perc_diff column to calculate a RAG rating. And for the latter, I cannot find functions in that list that would allow me to calculate the percentage difference.

I understand I could do this by running a SQL query to create a new dataset which groups by Dept, but in my actual data there are other columns that I want to group by as well (e.g. project code), so I want to be able to do this without creating multiple different queries & datasets.

#2

You should be able to get what you want Using Level Aware Aggregations. For example the “Total Budget” would be

sumOver({Budget}, [{Dept}], PRE_AGG)

Similarly you can define a field for “Total Spend”

sumOver({Spend}, [{Dept}], PRE_AGG)

Then proceed to calculate the difference:

{Total Spend} - {Total Budget}

And similarly you can then proceed to calculate the percentage difference and rating accordingly

1 Like
#3

I don’t see a need here for table or level-aware calculations, you should be to do this using simple aggregation functions in calculated fields. For Budget and Spend, you do not need to do anything, you can just add the raw fields in the pivot table and make sure the aggregation is set to sum (which should be the default for numeric fields anyway). For Diff, this should work as a calculated field:

sum({Spend}) - sum({Budget})

For Perc_diff, try:

sum({Spend}) / sum({Budget}) - 1

You can then use Perc_diff for conditional formatting.

1 Like
#4

Hi @Istvan_Szucs The only reason I suggested level aware aggregations here is to be able to keep the exact column order requested (budget, spend, dept, diff, perc_diff, rag)… this would be achieved by putting all fields in the group by field well of a table visual.
If level aware aggregations are not use, then he would have to put the dept column in a group by and the rest in the values field well (hence not maintaining column order) - hence dept will be the left most column.
Nevertheless, if maintaining the column order is not critical, then I fully agree there is no need to use level aware aggregations :slight_smile: