How to display the mean value of a total instead of average?

MichaelClark

I have a dataset where I am counting Completed Surveys, Total Survey Score, and 5 Scale Conversion. I am trying to display the mean value of the 5 Scale Conversion, for example - 

Completed Surveys = 114

Survey Score = 427.30

5 Scale Conversion = 534.13

Result when I enter into a calculator 534.13 / 114 = 4.68535 or 4.7.  When the AVG is used in a graph of the scores or if MIN/MAX/AVG is displaying, it displays as 4.82.  My boss says in her Turrets style, you cannot average an average so I am at a loss as to how to display this information.

 

Here is the first attempt at a Mean Survey Score - 

SUM(`SurveyScore`) /(CASE when `CompletedDateTime` > '01/01/1901' then 1
else 0
end)*1.25

 

Thanks in advance!

Michael

jaeW_at_Onyx

Instead of doing your multiplication, *1.25 AFTER aggregation do it before aggregation.

 

avg_5_adj score =

sum(actual score * 1.25) // this returns adjusted score on a scale of 1 to 5.

/

sum( 5) // this returns the maximum number of points per survey taken.

 

Don't use a CASE statement at all for filtering your values ... just use Date Filters in the UI to show "avg score this month" or "avg score YTD"

Unknown

I should add my date filter is set for Year to Date in order to work with a smaller data set.  Once verified on January 2021 surveys, I can choose last year to review.

jaeW_at_Onyx

@GuitarMan2020So much going on here ... but the root of your problem / question is unclear because mathematically speaking, mean and average are the same thing...

 

 

https://www.mathsisfun.com/mean.html#:~:text=The%20mean%20is%20the%20average,sum%20divided%20by%20the%20count.

 

Your boss is correct.  generally you don't want to take the average of an average.

 

what is 5-scale conversion?

how are you calculating completed surveys?

can you show us a sample of your data.  you can alter the numbers, but until we understand the granularity of your data (what one row in the table represents) it's gonna be difficult to give concicse advice!

 

re:

SUM(`SurveyScore`)

/

(CASE when `CompletedDateTime` > '01/01/1901' then 1
else 0
end)*1.25

 

This beast mode is probably not doing what you think it's doing.  I'm assuming you only want to consider rows where the CompletedDateTime is not null.

 

Rewrite that as

 

SUM(

CASE

WHEN completedDateTime Is Not Null then SurveyScore 

// alternatively your test can be WHEN completedDateTime > 0 then SurveyScore

END

) * 1.25

 

If you have your CASE outside the aggregation (the SUM) the CASE test is applied AFTER the data has been aggregated.  So instead of testing each row, the final result (total survey score) gets tested.

 

NOTE: from our high school days, mathematically you don't want to ever do SUM(Amount) / 0 because that 'undefined' , which should error or give an unexpected result.  0 / 1 = 0 but 1 / 0 = undefined.  In other words, be careful of returning an ELSE 0 in your beast modes.

 

One more reason not to return ELSE 0 , in 'normal math' the AVG(5, 5, 0)  is 3.3333 NOT 10.  So you don't want your CASE statement to return 0 if it doesn't pass your CompletedDate test.  You'd want it to return NULL (most likely).

Unknown

Hey there Jae,

 

Appreciate the insight and guidance.  For clarification, our surveys are scored 0-4 however, somewhere in some meeting, management reported as a 5 scale.  So the manual process is to take the average of total survey score / surveys returned times 1.25.

 

I am going to modify my Beast Mode per your suggestions and reply back with the results however, here is a screenshot of the data which as stated, I am only using Completed Date Time and Score (then the 5 Score of course).

 

Thanks!

Michael

 

 

 

 

jaeW_at_Onyx

Instead of doing your multiplication, *1.25 AFTER aggregation do it before aggregation.

 

avg_5_adj score =

sum(actual score * 1.25) // this returns adjusted score on a scale of 1 to 5.

/

sum( 5) // this returns the maximum number of points per survey taken.

 

Don't use a CASE statement at all for filtering your values ... just use Date Filters in the UI to show "avg score this month" or "avg score YTD"

Unknown

Works now!  Thank you!