# Calculating Differences based on the Value of a Column

Member
edited February 6

I'm trying to solve a problem where I want to compare the average of two columns based on the value of another column. A simplified version of my data table looks something like this:

Score A | Score B | Score C | Survey Type

10 | 20 |19 | pre

14 | 26 |23 | post

7 | 6 |8 | pre

10 | 10 |12 | post

I want to calculate the percentage change for each score column (A, B, C) for "pre" surveys vs. "post" surveys based on the Survey Type column.

I already have the averages of each score/survey type calculated using beast modes and filters based on the survey type, but I don't have a way to calculate the difference.

Would this be something I could do with ranking and windowing in the ETL?

Tagged:

• Coach

Here is what the beastmode would look like for column A % change between pre and post:

(AVG(CASE WHEN `Survey Type` = 'post' THEN `Score A` END) -
AVG(CASE WHEN `Survey Type` = 'pre' THEN `Score A` END)) /
AVG(CASE WHEN `Survey Type` = 'pre' THEN `Score A` END)

You can copy and paste this for Score B and C - just replace Score A with Score B and Score C respectively.

• Coach

Here is what the beastmode would look like for column A % change between pre and post:

(AVG(CASE WHEN `Survey Type` = 'post' THEN `Score A` END) -
AVG(CASE WHEN `Survey Type` = 'pre' THEN `Score A` END)) /
AVG(CASE WHEN `Survey Type` = 'pre' THEN `Score A` END)

You can copy and paste this for Score B and C - just replace Score A with Score B and Score C respectively.