How can I add a column to my pivot table that is a simple calculation of current numbers in table?

Question & Answer
, ,
1

I have a simple pivot table that takes the average rating of each question from a survey - it is also broken out by “Wave”. I want to add a column that is simply subtracts Wave 1 from Wave 2 for each Question on the table. I’ve linked a sample dashboard below.

I’ve also tried making it a normal table rather than pivot table, but it doesn’t give me the option to keep the Question labels as the row names.

sample dash

Thanks

2

@c527 -
Not sure of the final layout you are looking for. This approach would require 3 calculated fields one for each question. But, you could do something like this -

ifelse(avg(Wave)=2,avg(Q1)-lag(avg(Q1),[Wave ASC],1),0)

This assumes you are looking for the avg of Wave 2 question 1 - avg of Wave 1 question 1.

3

Thanks for the response.

To clarify, final layout would be just as the dashboard linked, but with final column showing differences for each question. I did try adding that calculated field to the pivot table but it just shows zeroes and am not sure how to add it as a column

4

QuickSight doesn’t allow you to add a custom calculation to the Rows or Columns of the pivot table. You would have to add it as a value. You could change to a table. You would have 3 calculated columns, one for each question. If you want one column/value, are you combining the values for each question, that should be doable.