Calculating Differences based on the Value of a Column

pstrauss · 2024-02-06T00:53:23+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Beast Mode

Accepted answers

ColemenWilson

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.

All comments

ColemenWilson

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.

pstrauss

Thanks for your help with this, @ColemenWilson I was making the mistake of not averaging each case statement individually. I'll give this a shot and let you know how it goes.