Calculating Differences based on the Value of a Column

pstrauss
pstrauss Member
edited February 2024 in Beast Mode

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:

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    edited February 2024 Answer ✓

    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.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited February 2024 Answer ✓

    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.

    If I solved your problem, please select "yes" above

  • pstrauss
    pstrauss Member

    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.

Categories