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.
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
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.